Use Bind Variables (Con't) - Page 3

September 27, 2001

The above code uses dynamic SQL to query out a single row from the ALL_OBJECTS table. It generates 1000 unique queries with the values 1, 2, 3, ... and so on 'hard-coded' into the WHERE clause. On my 350MHz Pentium laptop, this took about 15 seconds (the speed may vary on different machines).

Next, we do it using bind variables:

tkyte@TKYTE816> declare
2      type rc is ref cursor;
3      l_rc rc;
4      l_dummy all_objects.object_name%type;
5      l_start number default dbms_utility.get_time;
6  begin
7      for i in 1 .. 1000
8      loop
9          open l_rc for
10          'select object_name
11             from all_objects
12            where object_id = :x'
13          using i;
14          fetch l_rc into l_dummy;
15          close l_rc;
16      end loop;
17      dbms_output.put_line
18      ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
19        ' seconds...' );
20  end;
21  /
1.27 seconds...
PL/SQL procedure successfully completed.

We use the same logic here — the only thing that has changed is the fact that we are not hard coding the values 1, 2, 3... and so on into the query — we are using bind variables instead. The results are pretty dramatic. The fact is that not only does this execute much faster (we spent more time parsing our queries then actually executing them!) it will let more users use your system simultaneously.

Executing SQL statements without bind variables is very much like compiling a subroutine before each and every method call. Imagine shipping Java source code to your customers where, before calling a method in a class, they had to invoke the Java compiler, compile the class, run the method, and then throw away the byte code. Next time they wanted to execute the exact same method, they would do the same thing; compile it, run it, and throw it away. You would never consider doing this in your application — you should never consider doing this in your database either.

In Chapter10, Tuning Strategies and Tools, we will look at ways to identify whether or not you are using bind variables, different ways to use them, an 'auto binder' feature in the database and so on. We will also discuss a specialized case where you don't want to use bind variables.

As it was, on this particular project, rewriting the existing code to use bind variables was the only possible course of action. The resulting code ran orders of magnitude faster and increased many times the number of simultaneous users that the system could support. However, it came at a high price in terms of time and effort. It is not that using bind variables is hard, or error prone, it's just that they did not do it initially and thus were forced to go back and revisit virtually all of the code and change it. They would not have paid this price if they had understood that it was vital to use bind variables in their application from day one.