Re: Ad-hoc/Dynamic SQL Performance

"Steven Kilby" <spam_at_nowhere.com> wrote in message news:gf7tj.2066$497.1362_at_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
>
>

Make a slight change:
EXECUTE IMMEDIATE 'INSERT INTO test1 VALUES(:1)' using n;

If you do

INSERT INTO test1 VALUES(1);
INSERT INTO test1 VALUES(2);
INSERT INTO test1 VALUES(3);
INSERT INTO test1 VALUES(4);

From some application then you are purposely crippling yourself. By doing : EXECUTE IMMEDIATE 'INSERT INTO test1 VALUES(:1)' using n;

You are using bind variables. You should get signifigantly better results. SQL Server also has bind variables and in SQL Server you should use bind variables. (You will get a performance increase of some sort in SQL Server if you use bind variables.)

The effect of NOT using bind variables in your application to severly limit scalability. (regardless of RDBMS platform)

Years ago I worked with some COBOL Developers who used DB2 on a mainframe. They would write their COBOL and then compile it and bind it on the mainframe. My understanding was at compile time DB2 would determine the execution plan and bind that execution plan with the COBOL program. (assuming no one made any schema changes) So when they ran their COBOL programs DB2 would not have to generate an execution plan. It had already done that at compile time. So this was very effecient. Of course, if you made some massive schema change then you forced the dependent COBOL programs to rebind. You shouldn't be doing frequent massive schema changes in production anyway, so this method was very viable for DB2.

In Oracle a similar process happens when it sees an SQL statement. (although a stored outline is sort of similar to the DB2 mainframe execution plan binding, similar, not the same) I am fairly sure SQL Server reuses SQL statements with bind variables where it can. (so it does not have to compute the execution plan again and again for the same statement.)

So it is possible that Oracle takes a little longer than SQL Server to generate an execution plan. This may in fact be a benefit. It could be that Oracle is spending more time generating an execution plan so when it executes that plan it may be faster than SQL Server. Because Oracle (and SQL Server) highly recommend using bind variables the execution plan generation speed isn't as much of a concern as how fast the actual execution happens. They are thinking of it that you should be telling the RDBMS to generate an execution plan 1 time and then execute that plan millions of times. (which is what EXECUTE IMMEDIATE 'INSERT INTO test1 VALUES(:1)' using n; does.)

So you may be correct on some SQL statments SQL Server may in fact parse those statements faster than Oracle parses those statements. Big whoop.

The point is you shouldn't do that in either RDBMS. Jim Received on Thu Feb 14 2008 - 21:38:57 CST