Re: SQL performance

Am 25.08.2024 um 15:45 schrieb Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>:

... but when using dynamic SQL.
1. Work with parameter markers (instead of creating the SQL Statement with all values)

Just create your statement and only use parameter markers for the parameters that are changing 1,247 times - all the rest, compile into the statement string.

Then PREPARE the whole statement only ONCE.

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

Yes - only do an EXECUTE statement USING :host-variables-for-?-parameter-markers, ...;

If you want to replace a SELECT INTO with that, that's also easy - use

'values (select .... ) into ?' as the dynamic statement, or
'set ? = (select .... )' as the dynamic statement

Then use also EXECUTE statement USING ... and use an host variable for the "result parameter marker".

This works quite good and could be more performant than the static statement.

HTH
Daniel