Geeks With Blogs

News
Rob Reynolds

Subscribe to Fervent Coder RSS
Subscribe to Fervent Coder by Email

About Me

I manage several open source projects. Need...
   ...package management for Windows?
   ...automated builds?
   ...database change management (migrations)?
   ...your application to check email?
   ...a monitoring utility?

I also write for



Like what you are reading? Want to buy me a cup of coffee?
PayPal - The safer, easier way to pay online!

Fervent Coder Coding Towards Utopia...by Rob Reynolds

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?

image

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.

 image

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.

 

 

kick it on DotNetKicks.com

 

Posted on Friday, November 6, 2009 12:02 PM | Back to top


Comments on this post: Sproc Executing Slow? It Might be This

# re: Sproc Executing Slow? It Might be This
Requesting Gravatar...
"not to use Sprocs"++
Left by Dewayne Christensen on Nov 06, 2009 4:45 PM

# re: Sproc Executing Slow? It Might be This
Requesting Gravatar...
hello,
you can try "Optimize for unknown" in sql server 2008
...
OPTION(OPTIMIZE FOR (@pid UNKNOWN))
Left by anon on Nov 07, 2009 9:51 AM

# re: Sproc Executing Slow? It Might be This
Requesting Gravatar...
Do you really have to use that offensive term about your non-performant stored procedures?
Left by David Kemp on Nov 08, 2009 10:03 PM

# re: Sproc Executing Slow? It Might be This
Requesting Gravatar...
I apologize for any stored procedure I offended by calling it a nancy boy. :D
Left by Robz on Nov 09, 2009 3:11 AM

# re: Sproc Executing Slow? It Might be This
Requesting Gravatar...
I think you got that backwards... Nancy boys should be offended for being compared to sprocs.
Left by anon on Nov 09, 2009 5:09 AM

# re: Sproc Executing Slow? It Might be This
Requesting Gravatar...
FYI; this is known as "parameter sniffing"
Left by SanjayU on Nov 10, 2009 3:39 AM

# re: Sproc Executing Slow? It Might be This
Requesting Gravatar...
@Sanjay: Thanks! I couldn't remember what it was called when I wrote this...
Left by Robz on Nov 10, 2009 3:20 PM

Comments have been closed on this topic.
Copyright © Robz / Fervent Coder | Powered by: GeeksWithBlogs.net | Join free