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.