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

TL;DR

   - Generally, static is a better choice
   - If you use dynamic, only prepare a given statement once.
   - The plan cache doesn't store RPG variable names
      - In your examples, the statement stored in the plan cache is the
      exact same
      - Select a, b, c from custmast where c = ?


One last tidbit, don't overly complicate the static statement just to use
static.

What I mean is having something like so:
exec sql
  select <...>
    where col1 =
        case when :var1 = 0 then col1 else :var1 end
     and col2 =
        case when :var2 = '' then col2 else :var2 end
     <...>

To have "optional" filters or sorting applied.  This takes the "static is a
better choice" too far; greatly increasing the difficulty of the
optimiser's job.

In these cases, dynamic is a better choice.

HTH,
Charles


On Thu, Sep 11, 2025 at 3:14 PM Dan Bale <[email protected]>
wrote:

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