RE: Program performance incease ideas

> This would seem to be much better performance wise:
> 
> 
>       /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+ EXECUTE IMMEDIATE :SQLstmt into :IRGLINV
>      C/End-exec
>       /free


This is not a good idea, nor would it even work.  (You can't do EXECUTE 
IMMEDIATE <...> INTO :whatever)

EXECUTE IMMEDIATE still does the PREPARE behind the scenes.  

EXECUTE IMMEDIATE is useful for a one-time statement.  If the statement is run 
multiple times, you want to handle the PREPARE yourself and only do it once


However in this case since the statement is static you should be using static 
SQL not dynamic.

Here's the static SQL statement.

     c/exec SQL
     c+    select sum(ivpqty) into :IRGLINV
     c+    from sst37
     c+    where ivptno= :ihptno
     c+      and ivptcl= :ihptcl
     c/end-exec


Now if you actually need a dynamic statement, say for instance you don't know 
what file will be read till run-time.  Then the proper way to do this would be 
as follows:

      /free

         SQLstmt ='select sum(ivpqty)';
         SQLstmt = SQLstmt +' from ' + selectedFile;
         SQLstmt = SQLstmt +' where ivptno= ?';
         SQLstmt = SQLstmt +'   and 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+   for READ ONLY
     C/End-exec
         //***** NOTICE ***** here is start of DOW loop
         read sswihi;
         dow not %eof(sswihi);
      /end-free
     C*
     C/Exec SQL
     C+      OPEN IRGL using :ivptno, :ivptcl
     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

Note the performance of this is still going to be relatively poor as you 
open/close the cursor for every row.  I wouldn't recommend this method unless 
there's no other way. 

It would be nice if EXECUTE allowed the INTO clause but it doesn't.

HTH,

Charles