Jonathan, I respectfully disagree that the parse time is
negligible, if you're really concerned about scalability.
Sure, 1/10 of a second is nothing when doing interactive queries.
But if hundreds of users are descending on your site, you need
all the help you can get. I think Tom Kyte's example (given
here) does a nice job of showing this. I took
his example and ran it on our own development database server:
SQL> -- the wrong way (no bind variables)
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.20
SQL> set serveroutput on size 1000000
SQL> 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 = ' || i;
13 fetch l_rc into l_dummy;
14 close l_rc;
15 end loop;
16 dbms_output.put_line
17 ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
18 ' seconds...' );
19 end;
20 /
11 seconds...
PL/SQL procedure successfully completed.
Elapsed: 00:00:11.27
SQL> -- the right way (bind variables)
SQL> alter system flush shared_pool;
System altered.
Elapsed: 00:00:00.50
SQL> set serveroutput on size 1000000
SQL> 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.06 seconds...
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.32
With PostgreSQL, you'll have to use techniques other than
taking advantage of a library cache to get your speed. But if you're
using Oracle, getting rid of those parse operations WILL
give you better, measurable performance.
