Re: Dynamic Sql for better performance?

In article <SJgI7.1535$RL6.49449_at_news.cpqcorp.net>, "Lisa says...
>
>In my application, many of our sql statements are prepared once and executed
>repeatedly.
>This was done this way for performance reasons when the application was
>originally written to use an Informix database. We ported to Oracle a few
>years ago and have continued using dynamic sql in many places.
>
>In the oracle documentation in the Pro C/C++ Programmers guide under 'When
>to use Dynamic SQL', it says to use it when there are 'unknowns', and it
>says nothing about performance. We have no unknowns and are purely using
>dynamic sql for performance reasons. Is this giving us better performance?
>
>When does it start to make sense to use prepares and executes?
>When the number of columns being selected and/or updated becomes high?
>How high is high? 20 columns? 40 columns? 80 columns? ??
>I know that "prepares" save on parsing repeatedly, but since the binding of
>the host variables
>are done in the OPEN or EXECUTE statement, does the 'parsing once' save all
>that much?

saves BIG TIME, hugely -- large.

Pro*C is pretty good about caching the cursors for you however -- you don't *need* to use dynamic sql to get a parse once/execute many -- you can use STATIC sql (which is really dynamic sql under the covers) and Pro*c will cache the cursors for you.

So, you can stay with dynamic sql and do the work yourself OR you can use static sql and let pro*c do it for you. Upto you.

>
>Thanks for any insights.
>
> Lisa
>
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp