Re: Question about SQL joins & performance -- MIDRANGE-L

Hi Peter

Yes, the second run will always be faster because resources are still in memory, including the SQL programs themselves, and optimization has been done already. There are a couple things you can do to establish a more consistent testing environment. One is to use SETOBJACC *PURGE (see the help for details) and CLRPOOL, in order to get data resources out of memory. The other is to force the optimizer to create the access path each time. This is done with an entry in the QAQQINI file - you'll need to look this up in InfoCenter - look up QAQQINI and you should find something with the word "force" in it, IIRC.

And the Reference does not have the performance stuff you want. The manual for that is called "Database Performance and Query Optimization" and has some specific info on join criteria.

I don't think it will matter much what order you use around the equal signs. The key (heh heh) issue is that you have indexes that have the fields in the join criteria as the first fields in the key.

You can also look at www.iseries.ibm.com/db2, click on the "Support" tab, then the "Articles and White Papers" link, then the "Technical information" link. There are some performance items in this site.

For general performance there is a good site at www.iseries.ibm.com/perfmgmt - there is a link there to the "Resource library" that has some SQL (database) performance stuff.

HTH
Vern

At 01:35 AM 1/17/2005, you wrote:

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.