Why the performance difference (parameter)?

• mcahren

  SSC Veteran

  Points: 234

  More actions

  December 17, 2017 at 11:00 am

  #402762

  Hello,

  How do I troubleshoot/resolve this performance difference when parameterizing vs not parameterizing this query?

  These two queries are essentially identical.  This one runs in 1 second, returning ~92k rows:

  
declare @loadyear    varchar(4)
declare @loadperiod  varchar(2)

  set @loadyear =    '2017'
set @loadperiod =  '12'

  select
   [year],[period],[account],[custom3],[custom4],[amount]
from
   V_PBCS_CV_JTD
where
   [year] = @loadyear
   and [period] = '12'

  This query is the same, except for the @loadperiod variable in the last line.  It returns the same rowcount/result, but it takes ~5 mins and 20 seconds.
declare @loadyear    varchar(4)
declare @loadperiod  varchar(2)

  set @loadyear =    '2017'
set @loadperiod =  '12'

  select
   [year],[period],[account],[custom3],[custom4],[amount]
from
   V_PBCS_CV_JTD
where
   [year] = @loadyear
   and [period] = @loadperiod

  • There is no contention on the tables/views.  
  • The @loadyear parameter doesn't affect performance, and it's basically the same thing in my mind(?)
  • The source (C_PBCS_CV_JTD) is a view that references other views, that references other views. It's ugly, but I still can't account for why the two queries above perform so differently.  

  Any ideas?  
  

• Evgeny Garaev

  SSCertifiable

  Points: 7428

  More actions

  December 17, 2017 at 1:02 pm

  #1972408

  mcahren - Sunday, December 17, 2017 11:00 AM

  Any ideas?  
  

  Parameter sniffing doesn't work for the second query. There are several ways to solve this problem you can use plan guides to force the efficient plan or you can use query hint OPTIMIZE FOR @loadperiod = '12'.
  How to use query hints you can read here - https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query

• Grant Fritchey

  SSC Guru

  Points: 398908

  More actions

  December 18, 2017 at 6:38 am

  #1972485

  Actually neither of those queries are parameterized. Those are local variables. They work differently than parameters. To test this as parameterized queries you'd need to either put them into a stored procedure, or use sp_execute_sql. You can get variable sniffing, which is the same as parameter sniffing, but only when a statement recompiles.

  To see what's going on with these queries, look to the row estimates within the execution plan. See what each one is doing. I'll bet you the top query has a very accurate row estimate for the period value since you're hard coding it. The second one, since it's a variable (not a parameter), will not be sniffed (except in a recompile) and therefore will use a generic average of the values for the period column. You're seeing the difference between a variable (no sniffing, average) and a hard coded value (not sniffed, it just uses the value to look at the statistics). To see paramters, change the code.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning