Tutorial :Increase Execute Duration of Procedure When Using Variables in WhereClause



Question:

I Have a procedure executed in SQL Server 2008 R2, the script is:

DECLARE @LocalVar SMALLINT = GetLocalVarFunction();    SELECT     [TT].[ID],    [TT].[Title]  FROM [TargetTable] AS [TT]  LEFT JOIN [AcceccTable] AS [AT] ON [AT].[AccessID] = [TT].[ID]  WHERE   (    (@LocalVar = 1 AND ([AT].[Access] = 0 OR [AT].[Access] Is Null) AND    ([TT].[Level] > 7)  );  GO  

This Procedure executed in 16 seconds. But When I change the Where Clause to:

  WHERE   (    ((1=1) AND [AT].[Access] = 0 OR [AT].[Access] Is Null) AND    ([TT].[Level] > 7)  );  

The Procedure Executed in less than 1 second.

As You see I just remove the local variable.

So where is the problem? Is there any thing I missing to use local variable in where clause? any suggestion to improve execute time when I using local variable in where clause?

Update:

I also think to add an if statement before script and split the procedure to 2 procedures, but I have 4 or 5 variables like above and use if statement is so complex.

Update2:

I change the set of @LocalVar:

DECLARE @LocalVar SMALLINT = 1;  

There is no change in execute time.


Solution:1

When you use use local variables in WHERE filter then it causes FULL TABLE SCAN. The value of the local variable is not known to the SQL Server at compile time. hence SQL Server creates an execution plan for the largest scale that is avaliable for that column.

As you have seen that when you pass 1==1 then SQL server knows the value and hence the performance is not degraded. But the moment you pass a local variable the value is unknown.

One solution may be to use OPTION ( RECOMPILE ) at the end of your SQL query

You can check out the OPTIMIZE FOR UNKNOWN


Solution:2

When you use a local variable in WHERE optimizer doesn't know what to do with it.

You may check this link

What you could do in your case is run your query with displaying the actual plan in both cases and see how SQL is treating them.


Solution:3

It seems that you are using the @LocalVar as a branch condition, as follows:

  • If @LocalVar is 1 then apply a filter to the query
  • If @LocalVaris 0 then return an empty result set.

IMO you would be better off writing this condition explicitly, as then SQL will be in a position to optimize separate plans for the 2 branches, i.e.

DECLARE @LocalVar SMALLINT = GetLocalVarFunction();    IF (@LocalVar = 1)      SELECT         [TT].[ID],        [TT].[Title]      FROM [TargetTable] AS [TT]      LEFT JOIN [AcceccTable] AS [AT] ON [AT].[AccessID] = [TT].[ID]      WHERE       (        ([AT].[Access] = 0 OR [AT].[Access] Is Null) AND        ([TT].[Level] > 7)      )  ELSE      SELECT         [TT].[ID],        [TT].[Title]      FROM [TargetTable] AS [TT]      WHERE 1=2 -- Or any invalid filter, to retain the empty result  

And then, because there are now 2 branches through your stored procedure, you should add WITH RECOMPILE to the stored proc, because the 2 branches have radically different query plans.

Edit

Just to clarify the comments:

Note that placing OPTION(RECOMPILE) after a query means that the query plan is never cached - this might not be a good idea if your query is called frequently.

The WITH RECOMPILE at a PROC level prevents caching of branches through the proc. It is not the same as OPTION(RECOMPILE) at query level.

If there are a large number of permutations of filter in your query, then the 'branching' technique above doesn't scale very well - your code quickly becomes unmaintainable.

You might unfortunately then need to consider using parameterized dynamic SQL. SQL will then at least cache a separate plan for each permutation.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »