SEO

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

william.david.anderson@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

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

william.david.anderson@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

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

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.