Re: SQL optimization in bound procedures similar to LR OFF

[Charles Wilt <charles.wilt@xxxxxxxxx>]

In addition to this, if you're using dynamic SQL
 - make sure you are using parameter markers (always should do this)
 - only prepare the statement once


Dynamic SQL with parameter markers looks like
wSqlStmt = 'select fld1 from myfile where key = ?';

vs.
wSqlStmt = 'select fld1 from myfile where key = ' + someValue;

Which is bad not only for performance but security.

Lastly note that
 - the system by default won't leave access paths open till the 3rd
invocation of a statement
 - the system is smart enough to sometimes figure out the parameter markers
itself and only prepare the statement once.

So if you seeing slow performance on calls 1-3 and better performance on
the rest, you may not see much improvement.

Charles




On Thu, Aug 14, 2014 at 1:04 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

> Are you setting the CLSSQLCSR option within the CRTSQLRPGI to *ENDMOD?
> Or do you include a SET OPTION statement in your source code that includes
> the CLSSQLCSR option to be set to *ENDMOD?
>
> If so the cursor is always closed and the ODP removed if your module ends,
> i.e. a FULL OPEN must be performed over and over again.
> In this way do not change the default for CLSSQLCSR from *ENDACTGRP to
> *ENDMOD and do not compile your program with activation group *NEW, because
> the ODPs are always closed at the end of the activation group, and with
> option ACTGRP = *NEW the activation group is ended as soon as the program
> ends.
>
> Mit freundlichen Grüßen / Best regards
>
> Birgitta Hauser
>
> "Shoot for the moon, even if you miss, you'll land among the stars." (Les
> Brown)
> "If you think education is expensive, try ignorance." (Derek Bok)
> "What is worse than training your staff and losing them? Not training them
> and keeping them!"
>
> -----Ursprüngliche Nachricht-----
> Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Brad
> Sommerfeld
> Gesendet: Thursday, 14.8 2014 00:27
> An: rpg400-l@xxxxxxxxxxxx
> Betreff: SQL optimization in bound procedures similar to LR OFF
>
>
> In a program, I can return without setting on LR and the file opens,
> variables etc. do not close or get cleared.  This saves me the time of
> opening them on the next call for programs that are called frequently with
> significant initialization cost.
>
> My question is how to achieve the same effect when I have a procedure in a
> module that uses SQL?   I would like a 2nd and following call to be able to
> use the new values passed in with the existing SQL statement and cursor.  I
> call the procedure 12 times in a row from another program and it is taking
> up to 1 second per call.
>
> If there is a keyword that will help me find this on the web, please let me
> know because all of my searching has not yielded the answer.
>
>
> --
> This is the RPG programming on the IBM i (AS/400 and 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 IBM i (AS/400 and 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.