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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

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


















Thanks for your registration, follow us on our social networks to keep up-to-date