So I have a lovely big SQL which is returning over 2000 rows of data (going back to 2005) which is a recursive Common Table Expression (CTE). To perform certain calculations we have to compare each row with the previous days data and update the data, it’s quite expensive but as a piece of SQL will run in around 1 minute on SQL 2K8 running with 8Gb RAM under VMware. The same query as a stored procedure takes 27 minutes to run!
Yep, the exact same SQL when running as a stored procedure is over 20 times slower to produce the same result set.
Time to hit Google and the second result on the query “” brings up this little beauty of a posting.
So the first check:
SET ANSI_NULLS ON
Yep, got that one covered, the second one however of using local variables in the stored procedure query made all the difference. In this stored procedures case I added in three local variables which took the values of the passed in parameters and that was it.
(Note: the computer didn’t actually blow up, that would be irritating.)
The stored procedure now executes as fast as the SQL query. Amazing, and now that I have a working and more timely query, hopefully I’ll have a happy client. Now to update all of the stored procedures in the application.