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

A copy of this was sent to "Lisa Spielman" <lisa.spielman_at_compaq.com> (if that email address didn't require changing) On Mon, 13 Sep 1999 10:34:57 -0400, you 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.
>

if you have:

   exec sql insert into t values ( :bv1, :bv2, :bv3, ... );

then there will no benefit to parsing and executing as Pro*C will parse and execute the statement above for you.

OTOH, if you have:

   exec sql insert into t values ( 1, 2, 3 );
   exec sql insert into t values ( 4, 2, 3 );
   exec sql insert into t values ( 5, 2, 3 );
   exec sql insert into t values ( 6, 2, 3 );

then parsing once and executing with different values OR recoding as an insert with bind variables will be what you want to do.

the key is to use bind variables -- not the manner in which you use them.

>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?
>

A statement should use bind variables when called preatedly.

>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
>
>
>

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Sep 13 1999 - 09:43:33 CDT