Re: SQL optimization in bound procedures similar to LR OFF

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.