Ad-hoc/Dynamic SQL Performance

From: Steven Kilby <spam_at_nowhere.com>
Date: Thu, 14 Feb 2008 19:00:29 -0800
Message-ID: <gf7tj.2066$497.1362@newsfe14.phx>

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 Received on Thu Feb 14 2008 - 21:00:29 CST