Re: Dynamic SQL using parameter markers vs. embedded SQL using host variables

Birgitta nailed it as always.

From my personal experience there is no measurable difference between executing a static SQL statement with host variables and executing an already prepared dynamic SQL statement with parameter markers (?).

The performance difference is in the PREPARE statement, which does the "heavy lifting" at runtime instead of compile time for a static statement. 

HTH
Daniel


> Am 12.09.2025 um 11:29 schrieb Birgitta Hauser <[email protected]>:
> 
> 
>>> 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 <[email protected]> On Behalf Of Dan Bale
> Sent: Thursday, 11 September 2025 23:14
> To: [email protected]
> 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: [email protected] To subscribe, unsubscribe, or
> change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: [email protected]
> Before posting, please take a moment to review the archives at
> https://archive.midrange.com/rpg400-l.
> 
> Please contact [email protected] for any subscription related
> questions.
> 
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: [email protected]
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: [email protected]
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
> 
> Please contact [email protected] for any subscription related questions.
-- 
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: [email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact [email protected] for any subscription related questions.