Stored procedure takes longer run on SQL 2005 than SQL 2000
Here there is something interesting that I would like to share with you guys.
Even thou Microsoft SQL Server 2005 is out for quite some time, it is still common to see people working in projects using Microsoft SQL Server 2000 and often in mixed environments.
That's the case I want to talk about: The mixed environment, and I am working in a project where some applications have that hybrid configuration.
So someone told me that my report developed in .NET 2.0 was running slower than the similar one done in the old fashioned ASP. Of course I denied, just to see later the proof I was wrong.
Yes, the same stored procedure executed from the same page from, in the same machine was running faster in the old environment while it was slower in the new (and supposedly improved) environment. How's that possible? I traced the execution, used the SQL profiler but nothing gave me a good clue. Than I found this in the Microsoft website.
In SQL Server 2000, the execution plan for the query uses an Index Seek operator. In SQL Server 2005, the execution plan for the query uses an Index Scan operator. The optimizer produces an index spool for the Index Scan operation. When you use the FORWARD_ONLY cursor, SQL Server scans the index for every FETCH statement. Each fetch takes a long time. Therefore, the query takes a long time to execute.
See that example below:
50 declare @p1 int
51 set @p1=0
52 declare @p3 int
53 set @p3=16388
54 declare @p4 int
55 set @p4=8194
56 declare @p5 int
57 set @p5=0
58 exec sp_cursoropen @p1 output, <Transact-SQL statement> ,@p3 output,@p4 output,@p5 output
This code will run faster if you are NOT using the .NET 2005 SQL Connectors or running in a SQL Server 2000. Here we are using the sp_cursoropen to open a cursor, then specifying the forward-only option in the parameter list.
This is a bug you can only experience if you are using a lot of cursor-based stored procedures from a SQL 2000 to a SQL 2005 environment, and here we have a VERY HIGH cursor usage. (not that I like them neither I defend its usage, it is just a fact from the environment here)
How to fix this?
If you do not want to download and apply the patch and want to fix this in the code itself use "OPTION (FAST 1)" in the stored procedure call. That will make it run faster in the SQL 2005 machine.
See ya later