performance implications of calling PREPARE more than once

[william.david.anderson]

Hi there,

I'm thinking of using a PREPARE statement inside a stored
procedure, but
am wondering about the performance penalty associated with calling
PREPARE
multiple times. Below is an example of the kind of statements I
was thinking
of using.

...
SET S1 = 'UPDATE MFDP SET reliab=?';
PREPARE PS1 FROM S1;
EXECUTE S1 USING newreliab;
...

Is it true that if PREPARE is called multiple times with the same
statement,
the statement is only compiled once? Does that mean that the
performance
penalty for calling it multiple times would be low (compared with
compiling
it multiple times)?

Thanks for any help,

Bill

[Serge Rielau]

wi********************@gmail.com wrote:

Hi there,

I'm thinking of using a PREPARE statement inside a stored
procedure, but
am wondering about the performance penalty associated with calling
PREPARE
multiple times. Below is an example of the kind of statements I
was thinking
of using.

...
SET S1 = 'UPDATE MFDP SET reliab=?';
PREPARE PS1 FROM S1;
EXECUTE S1 USING newreliab;
...

Is it true that if PREPARE is called multiple times with the same
statement,
the statement is only compiled once? Does that mean that the
performance
penalty for calling it multiple times would be low (compared with
compiling
it multiple times)?

Depends on your definition of low.
The subsequent PREPAREs will discover that a statement with the same
text and environment (CURRENT SCHEMA, etc..) already exists and reuse
it's compiled plan.
However that discovery, while a fraction of the cost of compilation,
isn't free.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[william.david.anderson]

Serge,

Thanks for the info.

Bill

>
Depends on your definition of low.
The subsequent PREPAREs will discover that a statement with the same
text and environment (CURRENT SCHEMA, etc..) already exists and reuse
it's compiled plan.
However that discovery, while a fraction of the cost of compilation,
isn't free.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab