RE: I need faster SQL

The first thing I would do would be to create indexes over the fields that
are being used to join the tables.  I am not an SQL expert, but I believe a
table scan could be taking place to establish those joins (maybe just on the
first query, but none-the-less it would still speed it up).

The next thing I would do, which maybe should be the first, would be to
setup SQL journaling and then review the process with Visual Explain (this
can be done through iSeries Navigator).  Visual Explain can then recommend
indexes that should be created to speed up the process.  It has been awhile
since I have used Visual Explain so it may be called something else now.

HTH,
Aaron Bartell

 

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Kim Spears
Sent: Tuesday, February 20, 2007 10: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.