× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Don't know if it shows you as much detail as Visual Explain, but if your 
GUIphobic you could try STRDBG PGM(*NONE) UPDPROD(*YES) before your next 
STRSQL session and see if your joblog shows you any suggestions.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com





"Wilt, Charles" <CWilt@xxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
01/17/2005 08:53 AM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
"Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
cc

Subject
RE: Question about SQL joins & performance






Peter,

Looks like the only difference is in the order of the join keys correct?

 left outer join pdic52pf on 
(pdmnbr52=pdmnbr19 and vernbr52=vernbr19)

vs.

 left outer join pdic52pf on 
(pdmnbr19=pdmnbr52 and vernbr19=vernbr52)


I don't believe it matters.  You could use Visual Explain in iSeries 
Navigator to see for sure.  One way to start VE is through the "Run SQL 
Scripts" function.

HTH,
 

Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of
> Peter.Colpaert@xxxxxxxxxx
> Sent: Monday, January 17, 2005 2:35 AM
> To: midrange-l@xxxxxxxxxxxx
> Subject: Question about SQL joins & performance
> 
> 
> Hi group,
> 
> I am trying to improve performance in an SQLRPGLE program, 
> and could use 
> some advice (as this is purely SQL related and not RPG, I 
> post to this 
> group and not to RPG400-l).
> 
> I have already tried to find out whether there is a 
> performance difference 
> between the following 2 statements, which are part of a 
> complex SQL string 
> I'm dynamically building in the program:
> 
> 'select distinct pdmnbr19, vernbr19, itmtyp19, ' +
> 'case when itmnbr19 <> ' + c_quote + c_quote +
> ' then itmnbr19 when prenbr19 <> ' + c_quote + c_quote +
> ' then prenbr19 when pdmnbr19 <> ' + c_quote + c_quote +
> ' then pdmnbr19 end case from pdit19pf ' +
> ' left outer join pdic52pf on ' +
> '(pdmnbr52=pdmnbr19 and vernbr52=vernbr19) ' +
> 'left outer join pdco51pf on colint51 = colint52 ';
> 
> versus
> 
> 'select distinct pdmnbr19, vernbr19, itmtyp19, ' +
> 'case when itmnbr19 <> ' + c_quote + c_quote +
> ' then itmnbr19 when prenbr19 <> ' + c_quote + c_quote +
> ' then prenbr19 when pdmnbr19 <> ' + c_quote + c_quote +
> ' then pdmnbr19 end case from pdit19pf ' +
> ' left outer join pdic52pf on ' +
> '(pdmnbr19=pdmnbr52 and vernbr19=vernbr52) ' +
> 'left outer join pdco51pf on colint51 = colint52 ';
> 
> When I run them interactively in debug, or through iSeries 
> Access V5R2, it 
> seems that whichever statement I run in the second run (be it 
> the first or 
> the second statement) is quicker, probably because of the access path 
> still being in memory or something like that.  So it's not 
> clear whether 
> the first or second statement is more performant.
> 
> The SQL reference doesn't mention anything about the order of 
> the join 
> fields, so if anybody can shed a light on this, I'd be very grateful.
> 
> Thanks in advance.
> 
> Peter Colpaert
> Application Developer
> Massive - Kontich, Belgium
> -----
> Yoda of Borg are we.  Futile is resistance, assimilated will you be.
> -----

-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.