Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic SQL Vs Static SQL using Pro*C..

Re: Dynamic SQL Vs Static SQL using Pro*C..

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/05/21
Message-ID: <356637de.7706100@192.86.155.100>#1/1

A copy of this was sent to kolli_s_at_hotmail.com (if that email address didn't require changing) On Thu, 21 May 1998 13:44:30 GMT, you wrote:

>
>
>Is there any performance impact between using Dynamic SQL and Static SQL in
>ORACLE. We are using Proc*C with flags "CONFIG=D:\orant\pro22\pmscfg.cfg".
>
>When I say Dynamic SQL I meant something like:
>sprintf(sql_stmt, "select col1, col2 from tab1 where col1 = %s", col1_val);
>EXEC SQL execute :sql_stmt; /* or something similar..*/
>

Typically no, there isn't much difference. However, based on your sprintf statement above I would say there would be big differences.

You are building constants (literals) into the sql statement. If col1_val had the string "5" in it, you would build the query 'select col1, col2 from tab1 where col1 = 5'. 'Static' sql on the other hand would have you use some thing like:

   exec sql declare c1 cursor for select col1, col2 from tabl1 where col1 = :x;

And then you would    

   exec sql open c1 using :col1_val;

So, the 'static' sql would have you use a bind variable -- this allows us to reuse query plans and avoid having to parse and compile the statement each and every time. You might find that if you put the values in the statement as you are doing in the sprintf(), that you spend more time parseing the queries then actually executing them.

It is interesting to note that there is no such thing as "static" sql in Oracle, all sql is dynamic under the covers. We benefit greatly from shared sql however. shared sql allows us to reuse parsed/optimized query plans as long as the sql statments are 100% identical (in case, spacing, newlines, everything). Using bind variables promotes the use of shared sql. putting constants in defeats it.

So dynamic sql does not decrease performance but not using bind variables will (in general). There are cases when you don't want to use bind variables (data warehouses that rely heavily on the CBO come to mind) but for most applications bind variables are the way to go...

>Static SQL meant:
>EXEC SQL select col1, col2 into :col1_val, :col2_val where col1 = :col1_val;
>
>My collegues say using Dynamic SQL decreases the performance. I am sure about
>it. My argument is whether we use Dynamic or Static eventually an unparsed
>string is sent to the Server (ORACLE) and it needs to do exactly the same
>thing in either case.
>
>I would appreciate your comments on this. Also, please Cc: your responses to
>"kolli_s_at_hotmail.com".
>
>Thanks..
>
>-Sudhir Kolli
>kolli_s_at_hotmail.com
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu May 21 1998 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US