Re: Proc C Dynamic SQL - Parameterized SQL statements

karthikn_at_cyberdude.com wrote:
>
> Hello,
>
> Is there any advantage in terms of speed, cache hits etc. if I use
> the parameterized version of dynamic SQLs, ie., construct and pass in
> values thru SQLDA instead of
> building the values directly into the SQL string. To illustrate :-
>
> Will the following statement be less efficient
>
> "SELECT A,B FROM SOMETABLE WHERE A = 10"
>
> as oppossed to
>
> "SELECT A,B FROM SOMETABLE WHERE A = : x "
>
> and I pass in the value of x thru SQLDA.
>
> The one benifit at my application level is that I can reuse the
> SQLDA again and again. But I am wondering about any performance benefits
> from the Oracle Server in
> terms of improved cache hits or any other. I do not know much about
> Oracle caching.
>
> Thank you for your replies
> Karthi
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

One immediate benefit of using the above structure is this:

SELECT A,B FROM SOMETABLE WHERE A = 10
and
SELECT A,B FROM SOMETABLE WHERE A = 11
Although the above two select statements are very similar, Oracle must parse the two statements separately since they are not identical.
However,

"SELECT A,B FROM SOMETABLE WHERE A = :X" and then setting x=10 first time, and x=11 the second time parses the statement only once since the select statement itself is identical even if the value passed to :x is different.

This can increase server side performance.

-- 

                    ____________________________________________ 
                   /    Kenichi Mizuta 
                  /     Oracle Corporation (Redwood Shores, CA) 
       ////      /      Applications Division 
      |0 0|     /       email: kmizuta_at_us.oracle.com 
 _ooO_ \U/_Ooo_/        url: http://members.tripod.com/~kmizuta/

 The comments and opinions expressed herein are mine and
 do not necessarily represent those of Oracle Corporation.