It has been suggested to me that this can cause performance issues because
the plan cache may need to be "rebuilt"(?), so we should consider replacing
these type of queries with dynamic SQL using parameter markers, e.g.
Whoever recommended that either didn't listen properly and then twisted
everything around.
It is not a question about host variables versus parameter markers; it is a
question about static and dynamic SQL.
When using static SQL the SQL-Statement is already known at compile time.
So, syntax check and the first optimization are already performed at compile
time.
When running static SQL statements, they get directly optimized and
executed.
With static SQL you can include host variables (regular variables preceded
with a colon) directly in the SQL statement. When executing the SQL
statement, the data in the variables are directly included.
Each SQL Statement must run at least once through the FULL Optimization
(FULL OPEN) which is very time consuming.
With Static SQL the ODP (open data path) can stay open, so the next
execution, the ODP can be reused (only the variables are replaced and
executed) (PSEUDO OPEN)
PSEUDO OPENs can be 10-20 times faster than a FULL OPEN
When using dynamic SQL the SQL Statements are not known before they are
executed. That means the string containing the SQL Statement must be first
syntax checked and then converted into an executable SQL Statement.
After the FULL OPEN (with either the EXECUTE Statement or the OPEN
Statement) will be performed.
In dynamic SQL host variables are not allowed, instead you either can write
the dynamic Statement, so it includes all values in the String or you use
parameter markers (?) instead of the variables.
When executing the EXECUTE or OPEN statement the values in the parameter
markers are replaced with the values in the host variables (USING
:yourHostvar1, :yourHostvar2, ...). First parameter marker is replaced with
the value in the first host variable ...)
Performance:
1. compared with static SQL at runtime an additional step (syntax checking
and converting the string into an executable SQL Statement) must be
performed. (Advantage static SQL)
2. Since the statement is dynamic each time a PREPARE will be executed a New
Optimiztation (FULL OPEN) must be performed (which is time consuming).
(Advantage static SQL)
3. It is possible to reuse ODPs, if:
a. the previous SQL statement (string is saved) and
b. then compared with the new string
c. if both strings are identical skip the prepare statement.
When executing a FULL OPEN with each run the execution might be
different/faster, better optimized, but the FULL OPEN produces a big
overhead. (Advantage or disadvantage for any method?!)
... but if it is coded as in your example each time the SQL-Statement is
executed a FULL Optimization must occur.
Plan Cache: Contains the access plans from all executed queries (up to 3
different plans per statement).
It does not matter on whether the SQL-Statement was built and executed
statically or dynamically.
The Access Plan is never the problem because it only describes how a SQL
Statement is executed ("Access Plan delivers the receipt but does not
cooking" IBM).
What is time consuming is Opening the ODP (Open Data Path)! For performance
issues the number of FULL OPENs should be reduced to a minimum.
Static SQL may keep the ODP open, per default (at least as long as the
activation group in which the program runs is not reclaimed/closed).
Dynamic SQL throws the ODP away and reoptimizes (the only exception is, what
I said before, working with parameter markers comparing the old and new
statement and if they are identical skip the PREPARE.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
Modernization ? Education ? Consulting on IBM i
Database and Software Architect
IBM Champion since 2020
"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!"
"Train people well enough so they can leave, treat them well enough so they
don't want to. " (Richard Branson)
"Learning is experience ? everything else is only information!" (Albert
Einstein)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Dan Bale
Sent: Thursday, 11 September 2025 23:14
To: RPG400-L@xxxxxxxxxxxxxxxxxx
Subject: Dynamic SQL using parameter markers vs. embedded SQL using host
variables
We have a lot of embedded SQL that uses host variables, e.g.:
Select a, b, c from custmast where c = :hostc;
It has been suggested to me that this can cause performance issues because
the plan cache may need to be "rebuilt"(?), so we should consider replacing
these type of queries with dynamic SQL using parameter markers, e.g.
SQLStatement = 'Select a, b, c from custmast where c = ?'; Exec SQL Prepare
P1 from :SQLStatement; Exec SQL Declare C1 cursor for P1; Exec SQL Open C1
USING :hostc;
Is anyone aware of a resource that describes this in detail? Maybe also
provide test cases that prove performance gains? (Or not?)
- Dan Bale
*** CONFIDENTIALITY NOTICE: The information contained in this communication
may be confidential, and is intended only for the use of the recipients
named above. If the reader of this message is not the intended recipient,
you are hereby notified that any dissemination, distribution, or copying of
this communication, or any of its contents, is strictly prohibited. If you
have received this communication in error, please return it to the sender
immediately and delete the original message and any copy of it from your
computer system. If you have any questions concerning this message, please
contact the sender. ***
--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.
midrange.com
