RE: Program performance incease ideas

Brian,

You don't need dynamic SQL for this and you should never use a cursor for a 
single row select (there's additional overhead with cursors). You can change it 
to the following prepared statement (this will cache the information used to 
determine the logical which will make a big difference in performance):

C/EXEC SQL
C+ select sum(sswihi) from sst37 where ivptno=:ihptno
C+ and trim(ivptcl)=:ihptcl
C/END-EXEC

Note that I specified the physical file name instead of the logical. You should 
either specify the physical file name or the name of a view in the from clause.

The group by is hurting performance in this case since you are only ever 
processing one record so I took it out. Also, when you use a group by, you need 
a corresponding order by to get the results you want.

As others have pointed out, you need to remove the trim from the where clause 
but once you build a logical with ivptno and ivptcl as the first two key 
fields, this should make a big difference (actually, just getting rid of the 
group by should make a difference).

Matt

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On 
Behalf Of Brian Piotrowski
Sent: Tuesday, July 19, 2005 10:52 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Program performance incease ideas

Hi Will,

Thanks for the ideas.

Here's the raw code I am using:

      /free
         read sswihi;
         dow not %eof(sswihi);
           SQLstmt ='select sum(ivpqty)';
           SQLstmt = SQLstmt +' from sst37t2';
           SQLstmt = SQLstmt +' where ivptno='+apos+%trim(ihptno)+apos;
           SQLstmt = SQLstmt +' and trim(ivptcl)='+apos+%trim(ihptcl)+apos;
           SQLstmt = SQLstmt +' group by ivplcd, ivptno, ivptcl';
      /end-free
     C*
     C/Exec SQL
     C+ PREPARE SQLIRGL FROM :SQLstmt
     C/End-exec
     C*
     C/Exec SQL
     C+ DECLARE IRGL CURSOR for SQLIRGL
     C/End-exec
     C*
     C/Exec SQL
     C+ OPEN IRGL
     C/End-exec
     C*
     C/Exec SQL
     C+ Fetch IRGL into :IRGLINV
     C/End-exec
     C*
     C/Exec SQL
     C+ Close IRGL
     C/End-exec
     C*
      /free
           // Check the in-house inventory - it should not be less than zero.
           // If it is, reset it to zero.
           IHQTY = IHQTY - IRGLINV;
           If IHQTY < 0;
             IHQTY = 0;
           Endif;
           update dihi_fmt;
           read sswihi;
         enddo;

I am almost sure that this is the bottleneck in the process, as I have run the 
same query from an SQL interpreter and received the response time of 2 -7 
seconds before it returns a record.

As for indexing, the PartNo and PartCL keys are part of an index on the PF 
(there are a few more keys it uses in the PF that I don't need or use).

For records being summarized, it ranges from 1500 to 3500, so it's nothing 
tremendous.

As for how it is run, I call the RPG program and send it to a batch process, so 
it's not running interactively (unless I debug it).

And yes, I do open and close the cursor for each row.  I'm guessing that is a 
bad thing? :)

Thanks again!

Brian.

-----Original Message-----
From: Wilt, Charles [mailto:CWilt@xxxxxxxxxxxx] 
Sent: Tuesday, July 19, 2005 10:27 AM
To: RPG programming on the AS400 / iSeries
Subject: RE: Program performance incease ideas

Brian,

Are you sure that the bottle neck is in the portion of the code you posted?

Are you sure the SQL is using indexes?

How many records are in the file being summarized?

Are you running interactive on a system without the enterprise package, thus 
subject to CFINT?

You're not trying to use a open/close a cursor for each row are you.  (Might 
want to post your actual source code instead of pseudo-code)

I'd double check the SQL indexes.  I've had SQL summary queries over 4.5 
million records in a few seconds on a little 270.


Charles Wilt
--
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx]On Behalf Of Brian Piotrowski
> Sent: Tuesday, July 19, 2005 10:10 AM
> To: RPG programming on the AS400 / iSeries
> Subject: Program performance incease ideas
> 
> 
> Hi All,
> 
>  
> 
> I have a portion of one of my programs that does this:
> 
>  
> 
> Read Myfile
> 
> Do while not eof(Myfile)
> 
>   Read Myfile
> 
>   SQLSum = Get the sum of a series of records where somepart 
> = current part# and somecolour = current part colour
> 
>   NewSum = CurrentSum - SQLSum
> 
>   Update Myfile
> 
>   Read Myfile
> 
> EndDo
> 
>  
> 
> I find this process to be very time-consuming, as each SQL 
> transaction takes about 2-7 seconds to complete.  I'm using a 
> logical file to get the data that is keyed on the part number 
> and colour as well as only selecting records that have a 
> status of 3 or 5.
> 
>  
> 
> Any ideas on how I could speed this up?  The physical file 
> which it is updating only has 1400 records in it, and it's 
> been running now for the past ½ hour and has less than ½ the 
> records processed.
> 
>  
> 
> Thanks,
> 
>  
> 
> Brian. 
> 
>  
> 
> -=-=-=-=-=-=-=-=-=-=-=-=-=-
> 
> Brian Piotrowski
> 
> Specialist - I.T.
> 
> Simcoe Parts Service, Inc.
> 
> Ph: 705-435-7814 x343
> 
> Fx: 705-435-6746
> 
> bpiotrowski@xxxxxxxxxxxxxxx
> 
> -=-=-=-=-=-=-=-=-=-=-=-=-=-
> 
>  
> 
> -- 
> 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.
> 
>