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

<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