RE: I need faster SQL

I agree with Carl. Why add the additional code when a DS will work just
as well. 


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

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Carel Teijgeler
Sent: Tuesday, February 20, 2007 2:18 PM
To: rpg400-l@xxxxxxxxxxxx
Subject: 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.

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