RE: SQL Opinion (still experimenting with SQL)

It's interesting, PRTSQLINF is telling me that no ACCESS PLAN is found for
both statements.

The key to FILEA is

1 RCCONO
2 RCUNKN
3 RCRNUM
4 RCANUM
5 RCUNNO
6 RCFYR
7 RCFPD
8 RCDIV
9 RCTYPE
10 RCSB1
11 RCSB2

I keyed the where clause in the same manner for the first 7 keyed fields.
Still getting sub second execution.

Michael Schutte
Admin Professional
Bob Evans Farms, Inc.
"The Secret's the Sauce! Enjoy our new Bob-B-Q Pulled Pork Knife & Fork
Sandwich!"


rpg400-l-bounces@xxxxxxxxxxxx wrote on 07/11/2007 01:13:25 PM:

Generally speaking, EXISTS should be faster. But assuming an index
exists that both forms would use,
especially if only the index needs to be read, instead of hitting
the table data space. They'd
probably run about the same.

Heck the query optimizer might in fact replace one with the other.
You might see a debug message to
that effect.

In any event if they are both sub-second, I wouldn't worry about it.

HTH,
Charles

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
Michael_Schutte@xxxxxxxxxxxx
Sent: Wednesday, July 11, 2007 11:50 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: SQL Opinion (still experimenting with SQL)


I have two SQL statements that produce the same results. They
both have about the same process time (1 second or less).

Which do you think should perform better or do you see where
I could improve? This is going into a web app and similar
statements will also be processed. Statement 1 uses a join to
bring in the records needed, while the other uses the EXISTS
statement.

Select Sum(temp1), Sum(temp2), Sum(temp3),sum(temp4) From
(SELECT RCUNNO Unit, max(RCTCONC) temp1, max(RCCALL) temp2,
max(RCPRAIS) temp3, max(RCCC) temp4 FROM FILEA b
JOIN FILEB c ON b.RCCONO = c.CONO
And c.CONO = '001'
And c.RNUM = 1
And c.ANum = 12
And b.RCUNNO = c.UNNO
WHERE RCUNKN = '0'
AND RCRNUM = 0
AND RCANUM = 0
AND RCFYR = 2008
AND RCFPD = 2
Group By b.RCUNNO ) a

-- OR

Select Sum(temp1), Sum(temp2), Sum(temp3),sum(temp4) From
(SELECT RCUNNO Unit, max(RCTCONC) temp1, max(RCCALL) temp2,
max(RCPRAIS) temp3, max(RCCC) temp4 FROM FILEA b
WHERE RCCONO = '001'
AND RCUNKN = '0'
AND RCRNUM = 0
AND RCANUM = 0
AND Exists(Select '1' From FILEB Where b.RCCono = CONO And
Rnum = 1 And Anum = 12 And b.RCUNNO = UNNO)
AND RCFYR = 2008
AND RCFPD = 2
Group By b.RCUNNO ) a

Thank you in advance.


Michael Schutte
Admin Professional
Bob Evans Farms, Inc.
"The Secret's the Sauce! Enjoy our new Bob-B-Q Pulled Pork
Knife & Fork Sandwich!"

--
This is the RPG programming on the AS400 / iSeries (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

This e-mail transmission contains information that is intended to be
confidential and privileged. If you receive this e-mail and you are
not a named addressee you are hereby notified that you are not
authorized to read, print, retain, copy or disseminate this
communication without the consent of the sender and that doing so is
prohibited and may be unlawful. Please reply to the message
immediately by informing the sender that the message was
misdirected. After replying, please delete and otherwise erase it
and any attachments from your computer system. Your assistance in
correcting this error is appreciated.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.