RE: SQL performance -- RPG400-L

... but when using dynamic SQL.
1. Work with parameter markers (instead of creating the SQL Statement with all values)
2. Save the previous SQL SELECT-Statement
3. If you compare the newly created SELECT statement wit the previous SELECT Statement, only execute PREPARE if there is a difference between both statements (i.e. prepare once and run multiple times)

Keep in mind with each PREPARE a FULL OPEN must be performed

Mit freundlichen Grüßen / Best regards

Birgitta Hauser
Modernization – Education – Consulting on IBM i
Database and Software Architect
IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson)
"Learning is experience … everything else is only information!" (Albert Einstein)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Charles Wilt
Sent: Friday, 23 August 2024 21:10
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL performance

If you switch to PREPARE and EXECUTE, let us know the effect.

Normally static is better, but I've seen and IBM has confirmed that there are some instances where dynamic performs better.

Charles

On Fri, Aug 23, 2024 at 10:46 AM Marco Facchinetti < marco.facchinetti@xxxxxxxxx> wrote:

Hi Charles, and thanks for your answer.

" Having said that, 20min still seems like way too much time."

If I test one single customer in ACS the cost is between 0,9 to 1,3
seconds. So * 1.260 is not so far away to 20 minutes. The I/O is
really high (I guess a full scan for each execution).

Sadly I have no time to ask IBM.

Thanks!
--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco

Il giorno mer 21 ago 2024 alle ore 16:01 Charles Wilt <
charles.wilt@xxxxxxxxx> ha scritto:

A few thoughts...

First off, to find the answer you're looking for, you're going to
need to capture a DB trace and probably hand it over to IBM.

I'd agree that what you're seeing doesn't make sense. I'd expect
the cursor to be slower.
I think Daniel hit the nail on the head talking about a difference
in optimization.

My understanding is that while static statements are optimized at
compile time, there is some level of re-optimization / planning that
happens at

run

time so that the DB can ensure the best performance in case things
have changed; for instance an index has been added.

This run-time optimization happens once for the cursor variant,
since the DECLARE is compile time and only optimized during the first open.
But 1260 times for the static SELECT INTO.

Having said that, 20min still seems like way too much time.

I suspect you'd see even better performance with a dynamic VALUES
INTO

that

is PREPAREd once and EXECUTEd 1260 times.

Of course, I agree with everybody else that suggested removing the
need

to

run the statement 1260 times.

You say that "customer's list (ABCDUTENTE) is too complex with
SQL.". I suspect it's not as difficult as you may think :)
Basically, what you need is a result set containing the 1260 values
used for ABCDUTENTE.
This could be an SQL UTDF or if really necessary an RPG UDTF.

Then at worst, you have
AND LcCDUTENTE in ( select abcdutente from table(myutdf(parms))

But I'd probably look at joining the UDTF results.

HTH,
Charles

On Wed, Aug 21, 2024 at 2:43 AM Marco Facchinetti <
marco.facchinetti@xxxxxxxxx> wrote:

Hi Daniel, thanks for you time.

I agree about data sets and, when possible, I design SQL acces in
that

way

but in this specific program obtaining the customer's list
(ABCDUTENTE)

is

too complex with SQL.

The program with the cursor works and is completely satisfactory
but I would like to know why with the Select the times are so
high. Is it a problem in the links between the tables? In the
conditions or in the sequence in which they are specified?
What puzzles me is why a cursor is so fast (2/3 SECONDS) and a
Select

takes

20 MINUTES. The code of the Select and of the Cursor is exactly
the

same

and the general logic too: will be executed 1.260 times.

TIA
--
Marco Facchinetti

Mr S.r.l.

Tel. 035 962885
Cel. 393 9620498

Skype: facchinettimarco

Il giorno mar 20 ago 2024 alle ore 17:17 Daniel Gross
<daniel@xxxxxxxx

ha

scritto:

Hi Marco,

Am 20.08.2024 um 16:50 schrieb Marco Facchinetti <

marco.facchinetti@xxxxxxxxx>:

Hi all, I'm talking about embedded SQL.

execute the following code 1.260 times:
<snip>

Takes 2/3 seconds.

Executing 1.260 times any variation of these 4 statements (the
last

one

is the cursor's code):

<snip>

You can try the statements in iACS and look, what Visual Explain
says about them. Sometimes the problem is a missing index -
sometimes

something

that makes it impossible to cache the access plan or result.

But first, you should think about the application logic -
repeating a

SQL

statement (or a whole SQL cursor loop) for over 1.000 times is a
big

no-no.

To utilize the full power of SQL you should think about "data sets"

and

how the a linked together - maybe you can join the data to
another

table

-

so that you don't have thousands of open/close operations or

select-into

statements.

So rethinking the application design might be a good idea - from
this

very

restricted point of view.

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

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

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

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

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