Re: SQL performance

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