Contact  |  Site Map  |  Privacy      Call for more info
 
Latest articles

SQL 2005 vs. SQL 2000: SQL 2005 is not always faster


We continued on with our experiments that call almost empty procedures thousands of times over. The results are once again revealing.

Experiments are again done on a computer with Pentium 4 2.4GHz processor and 1Gb of RAM.

We created three procedures that do nothing. You read right, they do absolutely nothing.

CREATE PROCEDURE Proc_Return1
AS 
    RETURN
GO

CREATE PROCEDURE Proc_Return2
AS 
    RETURN
    DECLARE @i INT
    SET @i = 0
GO

CREATE PROCEDURE Proc_Return3
AS 
    RETURN 1
GO

In the second procedure, there were unreachable statements after the RETURN. They were not an error. We too thought that there is no difference, but we then called all three procedures 100K times:

CREATE PROCEDURE TestProc_Return1
AS
BEGIN
    DECLARE @t datetime
    SET @t = getdate()
    DECLARE @i INT
    SET @i = 0
    WHILE (@i <100000)
    BEGIN
        EXEC Proc_Return(1/2/3)
        SET @i = @i + 1
    END
    SELECT datediff(ms, @t, getdate())
END
GO

And we got the following results:

SQL 2000 SQL 2005
EXEC TestProc_Return1 2120ms 1850
EXEC TestProc_Return2 1200ms 1850
EXEC TestProc_Return3 1300ms 1850

What do these results mean?

  • Mysteriously, unreachable code after the RETURN helps it running faster. Only God and Microsoft know why, I donít have any logical explanation. It is believed that SET statements are more useful then the others. You can continue my experiments with the different statements and share the result.
  • RETURN 1 also helps, and is twice faster then RETURN
  • Finally, this mysterious behavior is fixed on SQL 2005Ö it is always running slow...

Now that is more important. Note: this is actually a case where SQL 2005 is running almost 2 times slower then SQL 2000 on the same code. And that can hurt on the production! Is it the only case? No, we will continue the investigation.

Disclaimer:

In many articles you will find the execution times of different queries. Please do not interpret these results scientifically or as official benchmarks. You will not even find the configuration of the computer used for these experiments because it is rather unimportant.

What is important is that query A is running 2 times slower then query B. On your computer, it could be different: 1.5 or even 3 times slower. Read the full disclaimer