Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Sep 27, 2001

Use Bind Variables (Con't) - Page 3

By DatabaseJournal.com Staff

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.

Oracle Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.



Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM