AW: I need faster SQL

Hi,

1. Would a named activation group positively affect performance in this

case?

  As long as your program isn't called from an other program that runs

with activation group *New, 
   using a named activation group will not have any affect on the
performance.

2. Is there a way to reset the cursor without executing the declare

repeatedly
   It's not the declare statement, but the open statement that needs the
most time.
   With the open statement the open data path will be built, i.e. temporary
objects are built and filled with data.
   The access path will be built the first two executions. From the third
execution the ODP can be left open, i.e. only data will be actualized.
   More interesting is: How is the Compile Option CLOSQLCSR set? 
   If it is set to *ENDMOD, the complete optimization process will be
executed each time the program runs, i.e. the ODP will be always hard
closed. 

To speed up performance you can do the following:
1. STRDBG before running your program an check the joblog created. May be
some access path (indexes) are adviced.
2. You may also run your select statement with iSeries Navigator's Visual
Explain. You'll even get more information as with STRDBG
If access paths are adviced create them and rerun your program.

3. Make sure that there are access path (DDS described logical files or SQL
indexes) over the join fields of the 2 tables.
   There should also be an access path over IPROD# (may be combined with the
joined fields)
4. If the field ICMPLT can only contain 2 values (Y or N), replace the <>
with =
5. Instead of using a single row retch into single fields, you'd use a
multiple row fetch into either an Multi Occurence Datastructure or an array
data structure with as much rows as possible.


Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them?  Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im
Auftrag von Kim Spears
Gesendet: Tuesday, February 20, 2007 17:55
An: RPG400-L@xxxxxxxxxxxx
Betreff: I need faster SQL


 Hello All, 

I've created a SQLRPGLE service program that contains a function to allocate
inventory to open orders. My SQL takes a product number, finds all open
orders and allocates the inventory from various sources based on the
expected ship date for the order. It works well, however performance is an
issue.

I use the function in a batch program...

C CALLP $Allocate(Array:count:IPROD#)

...to produce a report that the scheduling people use to plan their week.
The report can take from 20 to 120 minutes to generate. I've read back on
the list and don't seem to see anything that answers my questions, so I
would like to know...

The report program is compiled with DFTACTGRP(*NO) ACTGRP(*CALLER).

1.      Would a named activation group positively affect performance in
this case?

2.      Is there a way to reset the cursor without executing the declare
repeatedly

3.      What other options do I have?

Here's the relevant code snip from ($Allocate)...

C/EXEC SQL 

C+ DECLARE C1 CURSOR FOR

C+ SELECT IORD#, IITEM, IQORD, IQSHP, SLDATE FROM DCIDATA/MAPOITM

C+ INNER JOIN DCIDATA/MAPOSCH ON IORD# = SORD# AND IITEM = SITEM

C+ WHERE IPROD# = :Product# AND ICMPLT <> 'Y'

C+ ORDER BY SLDATE

C/END-EXEC 

C/EXEC SQL 

C+ OPEN C1

C/END-EXEC 

C SQLCOD DOUNE 0 

C/EXEC SQL 

C+ fetch C1 into :Order,:Item,:OQty,:SQty,:SSDate

C/END-EXEC

        

I will happily respond to any replies on Monday. Thank you in advance.




Kim