So you have stored procedure and it’s having issues. When you run it, it returns really fast. When .NET runs it, its like a dog.
The first thing you learn about SQL Server is that it has something called ARITHABORT and it is turned on in SQL Management Studio and OFF in ADO.NET. Yeah you heard that right. OFF. So when you find this out, your first thought is WTF, mate?! Why do we have to make this hard on ourselves?
Reference post: http://sqladvice.com/blogs/gstark/archive/2008/02/12/Arithabort-Option-Effects-Stored-Procedure-Performance.aspx
So you turn it off in SQL Server Management Studio under {Tools}->{Options} and all is good right?
Well, you might be coming across another problem and you want to optimize the sproc so you pull the text out and start running query analyzer. First things first, compare the text of the sproc versus executing the actual sproc. You might be surprised to find that the actual execution is not as fast as the text.
WTF? Why is the sproc running slower than the actual text? This is due to a bad query plan being cached by SQL. If you use the parameters that are passed in by the sproc, SQL Server tries to guess what those are and it puts in fake values. Then it caches a bad query plan.
So if you’re still with me at this point, you’re probably saying, that’s all fine and dandy Rob, but I just want to fix the freaking thing and you are giving me too much context.
The fix is so easy, it’s crazy. All you have to do is set those parameters to local variables and use the local variables in the sproc.
Create PROC Tim
@StartDate DateTime
AS
BEGIN
DECLARE @LocalStartDate DateTime
SET @LocalStartDate = @StartDate
/* your code here */
SELECT @LocalStartDate AS StartDate
/* instead of
SELECT @StartDate AS StartDate
*/
END
It’s a hack. And only useful when the sproc is being a non-performant nancy boy.
And here’s another good reason not to use Sprocs. I’m just saying. YMMV.
