Forum OpenACS Q&A: Response to Open ACS handling heavy traffic

Collapse
Posted by Daryl Biberdorf on

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.