Re: Performance Gains using Bind Variables (Parameterized Queries)in ADO

Mark G wrote:
>
> Does anyone out there have any opinions/experience with ADO and
> Parameterized queries and comparing it's performance to straight brute force
> SQL? What I mean is I can do this in ADO:
>
> Do Until RSCustomers.EOF
> SQL = "Select * from MyTable Where Cust_ID = " &
> RSCustomers("Cust_ID")
> adoConn.Open SQL, .....
> ...processing stuff....
> RSCustomers.MoveNext
> Loop
>
> When I look into the sql_text in v$sql, I'd see something like this:
>
> Select * from MyTable Where Cust_ID = 1234
> Select * from MyTable Where Cust_ID = 1235
> Select * from MyTable Where Cust_ID = 1236
> Select * from MyTable Where Cust_ID = 1237
>
> Now if I do a parameterized query in ADO, the same SQL text looks like this:
>
> Select * from MyTable Where Cust_ID = :V001
>
> and only appears no more than twice (why??).
>
> I KNOW that param'd queries are supposed to be quicker, but how much?
>
> TIA
Not using bind variables is one of the biggest causes of performance problems in Oracle...For every different set of (unbound) values, the SQL must be parsed which is very resource intensive...

Some drivers (in particular the older versions of ODBC) ran more slowly with bind parameters, and thus people used the static values...but this is a mistake. If your driver does binding poorly, change the driver...

HTH

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk

We are born naked, wet and hungry...then things get worse