Re: Ad-hoc/Dynamic SQL Performance

Steven Kilby wrote:
> Hi,
>
> I've "googled" for this, but I can't find any specific information so I
> thought I'd ask. I've run some simple tests using ad-hoc SQL and found
> that, on the same hardware, Oracle 10 is about 20% slower than SQL Server.
> I'm not trying to open a debate about ad-hoc vs stored procedures or
> whatever.
>
> I just want to know if my results are valid or is there something I'm
> missing. Both SQL Server and Oracle were installed with more or less the
> defaults. The test was nothing more than:
>
> CREATE TABLE test1(field1 NUMBER(10) PRIMARY KEY);
>
> CREATE PROCEDURE TestRoutine(iterations BINARY_INTEGER) AS
> BEGIN
> DECLARE
> n BINARY_INTEGER;
> y CHAR(1);
> BEGIN
> FOR n IN 1..iterations LOOP
> EXECUTE IMMEDIATE 'INSERT INTO test1 VALUES(n)';
> END LOOP;
> END;
> END;
> /
> BEGIN
> TestRoutine(100000);
> END;
>
> Similar tests show more or less the same results for UPDATE and delete as
> well. Additional analysis suggests that most of the extra time Oracle takes
> is for compiling the SQL. Is SQL Server faster at compiling SQL? Is there
> some configuration trick I'm missing with Oracle?
>
> Oracle appears to be much faster executing store procedures and I guess I
> just assumed it would be faster at most everything. Is this an exception?
>
> Thanks
> Steven Kilby

Much of the reason you are finding Oracle slower is possibly that your database is tuned as poorly as your code is written. Lets take your first statement for example:

 > CREATE TABLE test1(field1 NUMBER(10) PRIMARY KEY);

You have created a heap table using the default values for PCTFREE and PCTUSED. You have created a system generated primary key constraint and B*Tree index, again using system defaults. I would expect mediocre performance. Your stored procedure too is written to take advantage of version 7 technology that is totally obsolete. Further the use of NDS totally unnecessary.

Here's what your code does in 11.1.0.6

SQL> CREATE TABLE test1(field1 NUMBER(10) PRIMARY KEY);

Table created.

SQL> CREATE PROCEDURE TestRoutine(iterations BINARY_INTEGER) AS

   2 BEGIN

   3     DECLARE
   4        n BINARY_INTEGER;
   5        y CHAR(1);
   6     BEGIN
   7        FOR n IN 1..iterations LOOP
   8           EXECUTE IMMEDIATE 'INSERT INTO test1 VALUES(n)';
   9        END LOOP;
  10     END;

Procedure created.

SQL> set timing on
SQL> BEGIN
   2 TestRoutine(100000);
   3 END;
   4 /
BEGIN
*
ERROR at line 1:

ORA-00984: column not allowed here
ORA-06512: at "UWCLASS.TESTROUTINE", line 8
ORA-06512: at line 2

Among the obvious problems is that you declared, as a variable, the FOR loop counter "n". You can not treat Oracle like it is a Microsoft product and expect to be successful. Both of your variable declarations do nothing and your code, as you can see from the ORA-00984 invalid.

So lets recode this and at least get it to run:

SQL> CREATE OR REPLACE PROCEDURE TestRoutine(iterations BINARY_INTEGER) AS

   2 BEGIN
   3 FOR n IN 1..iterations LOOP
   4 INSERT INTO test1 (field1) VALUES (n);    5 END LOOP;
   6 COMMIT;
   7 END;
   8 /

Procedure created.

SQL> set timing on
SQL> exec TestRoutine(100000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:06.39
SQL> set timing off

6.39 seconds to insert 100,000 records is pathetic.

So lets do it correctly using SQL and then PL/SQL. First SQL:

SQL> CREATE TABLE test2(field1 NUMBER(10))

   2 PCTUSED 99
   3 PCTFREE 0; Table created.

SQL> ALTER TABLE test2

   2 ADD CONSTRAINT pk_test2
   3 PRIMARY KEY (field1)
   4 USING INDEX
   5 PCTFREE 0; Table altered.

SQL> set timing on
SQL> INSERT INTO test2

   2 SELECT rownum
   3 FROM dual
   4 CONNECT BY LEVEL <= 100000;

100000 rows created.

Elapsed: 00:00:00.18
SQL> set timing off

A modification of your routine takes 6.39 seconds, my version 0.18 seconds with SQL. Now lets do it with PL/SQL.

CREATE OR REPLACE PROCEDURE testroutine(iterations BINARY_INTEGER) IS   TYPE parent_rec IS RECORD (fld1_num dbms_sql.number_table);   p_rec parent_rec;
BEGIN
   FOR i IN 1 .. iterations LOOP
     p_rec.fld1_num(i) := i;
   END LOOP;    FORALL i IN 1 .. p_rec.fld1_num.COUNT    INSERT INTO test2
   (field1)
   VALUES
   (p_rec.fld1_num(i));

   COMMIT;
END testroutine;
/

SQL> set timing on
SQL> exec testroutine(100000);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.17
SQL> set timing off
SQL> SELECT COUNT(*) FROM test2;

   COUNT(*)

     100000

SQL> My point here is not to flame SQL Server. It has enough issues, Windows-only for one, that such is not required.

What I am a bit exercised about is people that come to Oracle from SQL Server, don't read the concept and architecture docs, don't read Tom Kyte's books, don't learn how to properly use the tool, and then complain about it being mediocre. A Ferrari doesn't work well hauling bricks either.

Oracle will run circles around SQL Server when used properly. And lest you think the above timings due to the use of some supercomputer ... they were done on the exact same IBM T43 ThinkPad I just carried to Denver for my presentation at RMOUG: A great conference put on by a great user group.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org