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