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

And, one more thing. Putting your SQL in Service Programs is not going to
work if you use SQL Stored Procedures unless you create functions
for each Service Program. Huge amount of programming overhead.

On Mon, Aug 2, 2021 at 6:43 AM Alan Campin <alan0307d@xxxxxxxxx> wrote:

And I agree with you about static vs dynamic. The one thing that I would
add is that it is absolutely critical to error check all SQL statements and
throw an error if you find one when using dynamic or static. Failing to
error check each SQL statements is number one problem.

Static is so much easier but unfortunately you don't always have that
choice.

On Mon, Aug 2, 2021 at 6:37 AM Alan Campin <alan0307d@xxxxxxxxx> wrote:

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