Re: Embedded Exec SQL versus SQL-CLI Performance (Link?)

Why would you use SQL-CLI? Wouldn't you just use dynamic SQL?

I doubt you would see much of a difference. Any difference would be in
building the Access Plan. With embedded SQL, the access plan is stored in
the program. With the Dynamic, it is stored in the System Cache.

One area that you will see a significant difference is if you have a
multi-tenant situation. That is where there are multiple clients or
multiple customers or any situation where you are running the same SQL
statement against different libraries.

Each time you switch a library, the SQL runtime has to re-do the access
plan and store the access plan back into the program assuming embedded SQL.
If you are using Dynamic SQL this is not true. This redoing of the access
plan and storing it back in the program is very expensive. I have done
extensive timing tests over the years on this but I don't have my numbers
anymore.

My current company and my previous company are both multi-tenant shops so I
have had a lot of experience with this.

If you are in a multi-tenant situation, which is unusual, it is important
to write your SQL using dynamic SQL.

On Fri, Jul 30, 2021 at 1:03 PM Alan Cassidy <cfuture@xxxxxxxxxxx> wrote:

Is there a link on-line comparing the relative general merits comparing
benefits of embedded SQL with SQL-CLI?

--Alan

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

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com