RE: I need faster SQL

First thing to do is run the job in debug so you can see what SQL is
doing. Pay special attention to whether or not SQL if SQL is having to
build an access path. If so you'll significantly improve performance if
you create one so that SQL doesn't have to do it every time your job
runs. 
 


Thanks,
S. Ellsberry
(678) 893-6673
steve.ellsberry@xxxxxxxxxxxx
 

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Kim Spears
Sent: Tuesday, February 20, 2007 11:55 AM
To: RPG400-L@xxxxxxxxxxxx
Subject: 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

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.