By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,592 Members | 2,158 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,592 IT Pros & Developers. It's quick & easy.

performance implications of calling PREPARE more than once

P: n/a

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

Sep 21 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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
Sep 21 '07 #2

P: n/a


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

Sep 22 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.