Re: Prepared statement vs Exec sql insert/update/select

Anytime a statement will be opened repeatedly, as in a loop, the optimizer overhead can be minimized by PREPARING the statement. Since the cost of developing a query plan is spent only once rather than hundreds of times there is a definite savings at runtime. Statements that are only executed once gain nothing from being prepared, but, neither do they lose anything. If you do not prepare the statement manually the statement still needs to be parsed and optimized and an query plan developed for it at the time the statement is executed or the CURSOR declared.

The only downside to PREPARE is with replacable parameters. Some queries with replaceable parameters will develop a sub-optimimal query plan due to the need of the optimizer to guess or estimate the effect of any filters dependent on the parameters with no knowledge of the exact runtime values.

Art S. Kagel

Lisa Spielman wrote:
>
> Is there any benefit to preparing and executing sql statements
> rather than just having them in an exec sql insert/update/select....
>
> The code runs under Oracle and Informix which is why I am posting
> this in both newsgroups. Hopefully I will get responses for both
> databases.
>
> I have read that statements should be prepared when the
> statement is complex and called repeatedly. What is meant
> by complex? Many columns? many joins?
>
> It used to be that we needed to prepare/execute the sql because
> we didn't know some of the the table names at compile time.
> But now we have moved to using 1 table and partitioning it,
> so now the table names are known. (We have also had prepares
> for stmts where we have known the table name). A few years
> ago, an Informix consultant who turned out to be not very good,
> told us to use prepares everywhere. (This is when we only ran
> under Informix.) When we added Oracle to the code stream, we
> kept the prepares.
>
> Most of our sql statments have many columns and no joins.
> They are executed repeatedly. The system runs 7 x 24
> with high volumes of transactions to process.
>
> thanks for any help, Lisa
Received on Mon Sep 13 1999 - 15:23:40 CDT