Month: September 2008

To bind or not to bind… Using bind variables when working with Oracle

Posted on Updated on

I’ve been working with Oracle for a little while now, and wanted to understand the kind of difference that using bind variables made. I did some thinking around how I could perform an experiment, and came to the conclusion that the purest approach would be to write two simple stored procedures, one that makes use of bind variables and one that doesn’t, run them both and measure response time.

First I needed a table to use with my procedures, so I created one on my schema:

create table t ("y" number(10,0))

Procedure 1 – with bind

create or replace
procedure test_proc_bind
as
start_ts timestamp;
end_ts timestamp;
diff_mins number := 0;
diff_secs number := 0;
begin

-- Capture start time
select systimestamp
into start_ts from dual;

-- Loop and insert
for i in 1 .. 100000
loop
execute immediate
'insert into t values ( :x )' using i;
end loop;

-- Capture end timestamp
select systimestamp
into end_ts from dual;

-- Calculate the difference in minutes
select sum(
extract(minute from end_ts) - extract(minute from start_ts)
) into diff_mins from dual;

-- Calculate the difference in seconds
select abs(
sum(
(extract(second from end_ts) - extract(second from start_ts)))
) into diff_secs from dual;

-- Print stats
dbms_output.put_line('Started at ' || start_ts);
dbms_output.put_line('Finished at ' || end_ts);
dbms_output.put_line('Test took ' || diff_mins || ' minutes, '
|| diff_secs || ' seconds');

-- Clean up
delete from t;

end;

Procedure 2 – without bind


create or replace
procedure test_proc_nobind
as
start_ts timestamp;
end_ts timestamp;
diff_mins number := 0;
diff_secs number := 0;
begin 


-- Capture start time
select systimestamp
into start_ts from dual;


-- Loop and insert
for i in 1 .. 100000
loop
execute immediate
'insert into t values ('||i||')';
end loop;

-- Capture end timestamp
select systimestamp
into end_ts from dual;

-- Calculate the difference in minutes
select sum(
extract(minute from end_ts) - extract(minute from start_ts)
) into diff_mins from dual;

-- Calculate the difference in seconds
select abs(
sum(
(extract(second from end_ts) - extract(second from start_ts)))
) into diff_secs from dual;

-- Print stats
dbms_output.put_line('Started at ' || start_ts);
dbms_output.put_line('Finished at ' || end_ts);
dbms_output.put_line('Test took ' || diff_mins || ' minutes, '
|| diff_secs || ' seconds');

-- Clean up
delete from t;

end;

OK, so we have our test table and two procedures. You can see that the both procedures INSERT 100,000 values into the table T, then do some stats processing. The next step is to run the two procedures and see what happens to the response time.

matt@XE> exec test_proc_bind
Started at 19-SEP-08 08.44.23.046327 PM
Finished at 19-SEP-08 08.44.27.550171 PM
Test took 0 minutes, 4.503844 seconds

PL/SQL procedure successfully completed.

matt@XE> exec test_proc_nobind
Started at 19-SEP-08 08.44.35.772012 PM
Finished at 19-SEP-08 08.47.00.069557 PM
Test took 3 minutes, 35.702455 seconds

PL/SQL procedure successfully completed.

matt@XE>

The results were as I expected, the procedure that used bind variables took substantially less time than it’s non-bind variable using counterpart. Clearly there is a lesson here…

Advertisements