|
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,no-no.
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.is the cursor's code):
Executing 1.260 times any variation of these 4 statements (the last
one
<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
--
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.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
