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.
> -----