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:

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.