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 11:18:05 -0400, you wrote:

>Thanks for your quick response!
>
>We do use use host variables in every statement, though
>we do have a few like:
>
>exec sql insert into t ( col1, col2, col3, ... ) values ( :b1, SYSDATE, :b2,
>...)
>

sysdate is as good as a bind variable.

>Would I be better off preparing a statement like this?
>
>For a statement where there are the same default values:
>
> insert into t ( c1,c2,c3,c4,c5,c6 ) values ( 0, :b1, :b2, 0, :b3, :b4 )
>
>is it better to use a host variable and substitute it for the 0's ?
>b0 = 0; values ( :b0, :b1, :b2, :b3, :b4 )
>

only if you have other inserts into t that DO NOT use 0 in columns 1 and 4.

What we are trying to avoid is having >1 insert into T statement in the program. As long as they are all the same -- you avoid lots of 'hard parses'.

On the other hand, lets say you have code like:

void f1()
{

   exec sql insert into t ( c1,c2,c3,c4,c5,c6 ) values ( 0, :b1, :b2, 0, :b3, :b4 );

}
void f2()
{

   exec sql insert into t ( c1,c2,c3,c4,c5,c6 ) values ( 0, :b1, :b2, 0, :b3, :b4 );

}
void f3()
{

   exec sql insert into t ( c1,c2,c3,c4,c5,c6 ) values ( 0, :b1, :b2, 0, :b3, :b4 );

}

you might be better off coding that as:

void utility_routine()
{

   exec sql insert into t ( c1,c2,c3,c4,c5,c6 ) values ( 0, :b1, :b2, 0, :b3, :b4 );

}

void f1()
{

   utility_routine( a, b, c, d );
}
....

That is, have as few EXEC SQL calls as you can and use subroutines when you find yourself having the same exact statement in 2 different places. This will decrease the overall number of cursors you app will need at runtime and reduce the number of soft parses as well as hard parses.         

>All of our inserts/updates explicitly have the columns listed
>
> 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 - 12:14:13 CDT