Each (executed) SQL Statement needs its own access path.
So a FULL OPEN must be performed, i.e. analyzing and perhaps rewriting the SQL Statement, estimating the available access path in conjunction with the statistics manager. Finally an access plan is generated, but an access plan is only a description of which indexes with which access methods in which sequence should be performed. The next step, Opening the ODP (Open data path) is the most time consuming part. And then the query is finally executed.
So if you split a Cursor into multiple independent SQL Statements you have already 1 Full Open versus multiple Full Opens.
Normally knows SQL about that and tries to keep the ODPs open, so with the next execution only the data in the ODP must be refreshed (PSEUDO OPEN).
If you compile your embedded SQL Program with CLOSQLCSR = *ENDMOD. The OPDs are closed as soon as your module ended.
If you compile your embedded SQL Program with CLOSQLCSR = *ENDACTGRP (which is by the way the default), the ODPs stay open as long as the activation group in your job is active (or if the maximum number of ODPs is reached, the oldest the longest not used ODPs are removed).
However if your programs run with Activation Group *NEW the ODPs get closed at the end of the program. Because the activation group will end and with it everything that runs in this activation group.
These kinds of different compiles can heavily affect the runtime.
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 Marco Facchinetti
Sent: Wednesday, 21 August 2024 10:44
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: SQL performance
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.
midrange.com
