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

Oh, I agree with you. Hind site is twenty-twenty but when you have
programmers who don't know anything about SQL and built all the databases
for file I/O originally there is not much you can do about
multi-tenant short of re-writing the systems.

IBM has recommended that we have one table with security that keeps you
from seeing anything except the current client but again would require
virtually a re-write.


On Mon, Aug 2, 2021 at 6:17 AM D*B <dieter.bender@xxxxxxxxxxxx> wrote:

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

Simply move your database access to database access modules (SRVPGM), as
it
is best practice anyway and put these in the datalibs and use naming *sql,
accessing the data in the same lib, the SRVPGM is located in. All
switching
of libs is disappearing, all is running fine. BTW: using naming *sys and
libl is a very bad practice!!! Having a database with referential
constraints, reading parent and child table from diffrent libraries?????

The main diffrence between static sql and dynamic sql is not
performance!!!
The main diffrence is stability: all static sql is checked at compiletime,
dynamic sql is checked at runtime.

D*B

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