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

stored procedures perf. question

P: n/a
Hi there,

I have a newbie stored procedures performance question that I'm hoping
someone can answer.

It seems like one of the benefits of using SQL stored procedures is
that they can reduce the network traffic between the client and
server, resulting in a performance gain. I'm wondering, though, if
there's a performance penalty because it could be harder to reuse
prepared statements with them.

For example, if I implement the logic within a C program on the client
side, I can prepare an SQL statement once and execute it any time that
statement is needed. In an SQL stored procedure, I can prepare a
statement and execute it multiple times within that procedure, but it
seems like every time I call that procedure, I'll need to prepare the
statement again. Because state cannot be saved across stored procedure
invocations, it seems like the benefit of using prepared statements is
lost. Is that true or am I misunderstanding something?

Thanks,

Bill

Aug 17 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
wi********************@gmail.com wrote:
For example, if I implement the logic within a C program on the client
side, I can prepare an SQL statement once and execute it any time that
statement is needed. In an SQL stored procedure, I can prepare a
statement and execute it multiple times within that procedure, but it
seems like every time I call that procedure, I'll need to prepare the
statement again. Because state cannot be saved across stored procedure
invocations, it seems like the benefit of using prepared statements is
lost. Is that true or am I misunderstanding something?
Bill,

In DB2 stored procedures (SQL or otherwise) are statically compiled.
that it when you CREATE the procedure all statements are being compiled
and stored in a so-called PACKAGE.
So, you can shut down DB2, restart, and when you CALL the procedure the
only extra work that needs to happen is to read the package into the
cache. No recompiles at all.

DB2 does not distinguish between a package for a procedure and a package
for an application.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Aug 17 '07 #2

P: n/a

Thanks much for your reply. Is there a benefit to using PREPARE
statements in an SQL stored procedure?

Thanks again,

Bill
>
In DB2 stored procedures (SQL or otherwise) are statically compiled.
that it when you CREATE the procedure all statements are being compiled
and stored in a so-called PACKAGE.
So, you can shut down DB2, restart, and when you CALL the procedure the
only extra work that needs to happen is to read the package into the
cache. No recompiles at all.

DB2 does not distinguish between a package for a procedure and a package
for an application.

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

Aug 17 '07 #3

P: n/a
wi********************@gmail.com wrote:
Thanks much for your reply. Is there a benefit to using PREPARE
statements in an SQL stored procedure?
There can be.
Dynamic SQL is beneficial when you compose SQL statements within the
stored procedure (beware of SQL Injection!) or you want to execute DDL
statements not directly supported by the syntax.
For DDL statements folks typically use EXECUTE IMMEDIATE though since
the statement is executed only once.
One example to use PREPARE is to perform recursive CALLs to the
procedure you are just creating. It is the only way how a procedure can
call itself.
Other examples are cursors for which the exact WHERE clause is not known
in advance.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Aug 17 '07 #4

P: n/a

Serge,

Thanks again.

Bill
There can be.
Dynamic SQL is beneficial when you compose SQL statements within the
stored procedure (beware of SQL Injection!) or you want to execute DDL
statements not directly supported by the syntax.
For DDL statements folks typically use EXECUTE IMMEDIATE though since
the statement is executed only once.
One example to use PREPARE is to perform recursive CALLs to the
procedure you are just creating. It is the only way how a procedure can
call itself.
Other examples are cursors for which the exact WHERE clause is not known
in advance.

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


Aug 17 '07 #5

P: n/a
Procedure(External or SQL) needs context exchange. So, you had better
to take into cinsideration both of performance improvement of
procedure itself and overhead of context swithing by using procedure,
at the time of discussing performance.
In DB2 SQL Reference Vol.2 ---Statements ---Compound SQL(Dynamic)
written as followings:
Notes:
- Dynamic compound statements are compiled by DB2 as one single
statement. This statement is effective for short scripts involving
little control flow logic but significant dataflow. For larger
constructs with requirements for nested complex control flow or
condition handling, a better choice is to use SQL procedures.
For more details on using SQL procedures, see "CREATE PROCEDURE".

Summarize this phrase:
1) Short scripts involving little control flow logic but significant
dataflow: Dynamic compound statements(and UDF) is effective.
2) Larger constructs with requirements for nested complex control flow
or condition handling: SQL procedures is a better choice.
Aug 19 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.