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

[Alan Campin <alan0307d@xxxxxxxxx>]

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