There is NOT value in using BOUND variables!!! ???

we are using Oracle 8.01 on NT4/SP5, 512MB RAM, Dual Pentium Pro 200, RAID array level 3.

I was intrigued by the suggestions made in Oracle Performance Tuning about using bound variables where possible. I always had the hunch that bound variable queries actually run slower but I decided to test it out in a more disciplined fashion.

I ran following query 100 times, each time criteria coming from a random number generator so it was guanrenteed to be unique in my case. Select x, y, z from ABC where field in (set of 6 values).

And then I ran following query
select x, y , z from ABC where field in (:1, :2, :3, :4, :5, :6) and then did the binding.

The first query (without bound variables) took 10 seconds to execute 100 versions. The second one (with bound variables) took 13 seconds!

Then I changed the bound variable query to use all 255 bound variables in the *IN* clause (to have a more generic query, i.e. :1, :2, ..., :255) and then assigned random values to first 6 variables and '0' to the rest of the 249. The resultant query just slowed to a crawl. I did not have the patience to see it run 100 times.

So what does it tell me? I remember that the perception about bound variables forced developers in our shop to change queries NOT to use bound variables (a few months ago) and todays test just confirms it.

Yours thoughts?

Regards,
Mike.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Nov 10 1999 - 16:09:52 CST