AW: SQL optimization in bound procedures similar to LR OFF -- RPG400-L

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

Even if he works with parameter markers, he has to make sure that the statement in only prepared once and executed multiple times.
If he prepares always before execution it is as good as completely building the dynamic SQL statement and executing it after.

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 Charles Wilt
Gesendet: Thursday, 14.8 2014 15:00
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: 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.

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