RE: I need faster SQL

Why need a user space, apperently you are procesing the occurences of the
DS. Using the user space in this regard is overkill.

Regards,
Carel Teijgeler

*********** REPLY SEPARATOR  ***********

On 20-2-2007 at 9:47 Alan Campin wrote:

If you issue the declare once and do an open and close each time, it will

reuse the Open Data Path(ODP). Actually, if you look at the

compiled code you will see that the declare is just a comment. There is no

executable code. 

Based on your SQL, you should be reusing the ODP 

Last thing. If you are reading in a lot of records, the best way to

increase the performance is to read in many records into an array. 

The best way to do this is to create a user space and load the records

directly to the user space. Then read the user space in a loop. 

I don't know your field sizes so I am guessing. Partial code. Just the

flavor. 

D dsRecord...
D                   s               Qualified
D                                   Based(ptrRecord)
D                                   Occurs(32766)
D  IORD#                     7a
D  IITEM                    15a
D  IQORD                     7p 2
D  IQSHP                     7P 2
D  SLDATE                    8s 0
/Free

 ptrRecord = MakeUserSpace();

/Exec Sql
 Fetch From C1 For 32766 Rows Into :dsRecord  
/End-Exec

 w_RowCount = SQLERRD(3);

 For x = 1 to w_RowCount;
   %Occur(dsRecord) = x;
   // process record
 EndFor; 

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