T-SQL: Stored Procedure vs Query Performance

The query ran fast. The stored procedure of that same query is slow.

Let’s do this. For those looking for a solution and no explanation, we’ll start with the answer and finish with an explanation.

Solution:

You started with a query that looked like this:

 DECLARE @ PersonID int

SELECT * FROM Persons WHERE PersonID = @PersonID 

It worked great. Fast as can be. Then you created the stored procedure:

CREATE PROCEDURE [dbo].[dbsp_Get_Person_From_PersonID]
@PersonID int
AS

BEGIN
SELECT * FROM Persons WHERE PersonID = @PersonID
END

And now it’s slow. You can fix this by adding an internal variable and assigning the new internal variable to the value of the external variable and using the new internal variable in your query:

CREATE PROCEDURE [dbo].[dbsp_Get_Person_From_PersonID]
 @PersonID int
AS

BEGIN
 DECLARE @PersonIDInternal int

 SET @PersonIDInternal = @PersonID

 SELECT * FROM Persons WHERE PersonID = @PersonIDInternal
END

That’s it. Hopefully that solves your problem. If you’re curious why such an irritatingly redundant step could be necessary, feel free to read on.

Explanation:

Before I figured out the solution to this issue, I think I lost a few hairs to the gray side. I’ll describe the scenario first:

You’ve worked very hard to build a SQL query (maybe you didn’t work that hard but we’ll pretend you did anyway). Being the good developer you are and keeping your anti-SQLi (SQL Injection) wits about you and desiring that sweet SQL optimization, you convert your reusable code into a stored procedure. There’s only one problem:

The query ran fast. The stored procedure is slow.

Now the degree to which this takes place depends on the query. I’ve had queries that ran instantaneously that turned into 30 second queries as a stored procedure. You’ll pore over that code, trying to find what you could have done. What has changed? A little feature of SQL Server Optimization called Parameter Sniffing.

SQL knows you want to reuse that stored procedure and for queries more complex than the ones we demonstrated above, SQL doesn’t want to figure out how to execute that code every single time it runs it. So SQL Server creates an “execution plan” on exactly how to get your data and then caches it, theoretically to make it execute faster.

However, during that process of creating an execution plan, SQL automatically assumes that every time you run this query, it’s going to run the same way. It assumes that because you’re using parameters. You see, depending on what parameters you pass in to your query, you could get very different result, especially if you have a complex query designed to return a variable number of records. Imagine if, in the query I listed above, sometimes it returned 1 record, other times it returned 10,000 records. If you were just running that in a query window, SQL would probably build different execution plans for optimization. But because it’s in a stored procedure and SQL has cached the execution plan, it runs the same way, every time.

That’s not a bad thing unless that’s precisely what you DON’T want to happen.

By providing an internal variable as in the example above, you can force SQL to build an execution plan that is optimal for the query you’re running.

Another way of doing this is using the “WITH RECOMPILE” statement.

CREATE PROCEDURE [dbo].[dbsp_Get_Person_From_PersonID]
 @PersonID int
WITH RECOMPILE
AS

BEGIN
 SELECT * FROM Persons WHERE PersonID = @PersonID
END

This forces SQL Server to recompile the query every time the stored procedure is called.

Here’s Microsoft’s blurb on it:

Another reason to force a procedure to recompile is to counteract the “parameter sniffing” behavior of procedure compilation. When SQL Server executes procedures, any parameter values that are used by the procedure when it compiles are included as part of generating the query plan. If these values represent the typical ones with which the procedure is subsequently called, then the procedure benefits from the query plan every time that it compiles and executes. If parameter values on the procedure are frequently atypical, forcing a recompile of the procedure and a new plan based on different parameter values can improve performance. – (https://msdn.microsoft.com/en-us/library/ms190439.aspx)

 

Advertisements
  1. No trackbacks yet.

You must be logged in to post a comment.
Advertisements
%d bloggers like this: