RE: SQL Opinion (still experimenting with SQL)

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.