Re: disadvantages with bind variables

A copy of this was sent to david-deja_at_cheetahmail.com (if that email address didn't require changing) On Wed, 13 Oct 1999 21:59:17 GMT, you wrote:

>Hi-
>
>Is there any disadvantage in binding variables?
>
>I use Oracle8 and Perl using DBI/DBD.
>
>Because it is impracticabable to ask our programmers to use binding in
>their Perl scripts, I wrote a Perl library that parses the sql queries
>and substitutes ? for the variables (identified because they are
>enclosed in quotes) and then bind them.
>
>For example "select * from table where user = 'david'" becomes
>"select * from table where user = ?" ...
>
>The potential problem is that ALL sql queries get parsed and modified
>before they reach the database.
>
>For example ".... like 'david%'" becomes "... like ?".
>
>It doesn't seem to slow down anything, but I am worried it could cause
>some problems.
>
>Any insight? (Please cc me).

This in general is a very good thing. You will get maximum reuse out of the shared sql area this way. You will get in general better performance.

The only time I would not use a bind variable *might* be in a DSS or Data warehouse environment. Lets say the application submits the query:

select count(distinct y) from T where x = 'a';

-assume there is an index on x.

-assume the data is distributed such that 'a' represents 1% of the data.

-assume the data is distributed such that there is a value in x that represents
>20% or so of the data (eg: the valus in X are very skewed, sometimes X is very
discriminating, sometimes NOT)

If the optimizer is running in CBO and we've generated the appropriate stats so the optimizer understands how x is distributed and the optimizer gets the query above (wo bind variables) -- it'll pick the index since "x = 'a'" is very selective.

OTOH, if the optimizer recieved "select count(distinct y) from T where x = :b1", it would not know that you are going to compare x to 'a' -- it very well might pick an access plan that does not use that index (since its very skewed).

Given a choice --
1) use bind variables everywhere.
2) don't use them anywhere.

I would always opt for #1. You will find in many cases that 90% of your runtime is spent parsing queries without bind variables (your current state). Adding bind variables to the queries will release so many resources for other things that performance will go up. Then you can work on those individual queries that do not benefit from bind variables (eg: your perl api might let the programmer set a flag somewhere that says to you "do not autobind for me"). That way the exceptions to the binding rule can get around your logic when absolutely needed.

>
>David.
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

--
See http://osi.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 Thu Oct 14 1999 - 07:13:02 CDT