2

This question already has an answer here:

I'm getting significantly different performance from a query that uses parameters vs the same query where the same values are hardcoded in the where clause.

It comes down to 2 child tasks in the execution plan. The hardcoded query can perform an index seek but the parameterised query does an index scan. Since the table contains a million rows the difference is massive.

The query is pretty ugly...

DECLARE @MethodOfDelivery nvarchar(3) = 'AAA'
DECLARE @OutwardPostcode nvarchar(4) = 'BBB'
DECLARE @InwardPostcode nvarchar(3) = 'CCC'

SELECT TOP 1
    slr.*
FROM
    [dbo].[SortLevelRecord](NOLOCK) slr
    INNER JOIN dbo.RoutingDataVersion(NOLOCK) v ON v.Version = slr.Version
    INNER JOIN dbo.Routing(NOLOCK) psr ON
        (
            slr.SortLevelKey = psr.Day1SortLevelKey OR
            slr.SortLevelKey = psr.Day2SortLevelkey OR
            slr.SortLevelKey = psr.Day3SortLevelkey OR
            slr.SortLevelKey = psr.Day4SortLevelkey OR
            slr.SortLevelKey = psr.Day5SortLevelkey OR
            slr.SortLevelKey = psr.Day6SortLevelkey OR
            slr.SortLevelKey = psr.Day7SortLevelkey OR
            slr.SortLevelKey = psr.Day8SortLevelkey OR
            slr.SortLevelKey = psr.Day9SortLevelkey OR
            slr.SortLevelKey = psr.Day10SortLevelkey OR
            slr.SortLevelKey = psr.Day11SortLevelkey OR
            slr.SortLevelKey = psr.Day12SortLevelkey OR
            slr.SortLevelKey = psr.Day13SortLevelkey OR
            slr.SortLevelKey = psr.Day14SortLevelkey
        ) AND
        psr.Version = v.Version AND
        psr.MethodOfDelivery = slr.MethodOfDelivery AND
        psr.OutwardPostcode = @OutwardPostcode AND
        (
            psr.InwardPostcode = @InwardPostcode OR
            psr.InwardPostcode = SUBSTRING(@InwardPostcode, 1, 1) OR
            psr.InwardPostcode = ''
        )
WHERE
    slr.MethodOfDelivery = @MethodOfDelivery  
ORDER BY
    LEN(psr.InwardPostcode) DESC

The index scan/seek is on the Routing table.

Something else that seems a bit strange is that the parameterised query uses Parallelism to gather streams but there are no parallel operations, only 1.

This is on Azure SQL server. I'm not sure what version that correlates to.

marked as duplicate by Erik Darling sql-server May 2 at 13:28

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

  • 4
    I see only local variables rather than parameters in your code. Is the column type varchar or nvarchar? nvarchar parameters used against varchar columns will not use indexes efficiently with a legacy SQL collation. Upload your plans and add the link to your question for more specifics as to the cause. – Dan Guzman May 2 at 11:32
  • Sounds like you are seeing this problem: sommarskog.se/query-plan-mysteries.html#varparamreplace – Jacob H May 2 at 13:30
  • @DanGuzman yes, we are seeing the same performance issue with that in SSMS as we were with the parameters from code. – BenCr May 2 at 15:07
  • Okay, @DanGuzman you hit the nail on the head. I'd made an incorrect assumption about the variable types and the code that executes the query also makes an incorrect assumption about the variable types. 'AAA' and 'CCC' should be char(3) and 'BBB' should be varchar(4). – BenCr May 3 at 10:25
  • @BenCr, this is a common problem because strings in many programming languages are Unicode and default to nchar/nvarchar. It's a good practice to specify the intended SQL type and maximum length when creating parameters in code. Avoid AddWithValue in .NET apps. – Dan Guzman May 3 at 10:35
0

This is what is called parameter sniffing. What happens is when you execute a query, the underlying plan is stored and reused however that particular plan may not suit your variable. This is a normal and expected behavior in SQL Server. Because compiling queries is expensive and takes time, SQL server stores plan in the cache and as a DBA we want our SQL Server to re-use them as much as possible so that we can save our time and resources.

There is a very nice video from SQL Server SME Mr. Brent Ozar listed here which can give you full details on parameter sniffing, possible work-around and what should be done in order to avoid the same.

What you have asked in the question, almost same thing has been explained by Mr. Greg Larsen and you would get detailed info on variable and hard coding the values at this link.

You may also read details of parameter sniffing for azure on this link.

Few more interesting reads on the same topic:

  1. https://www.sqlshack.com/query-optimization-techniques-in-sql-server-parameter-sniffing/

  2. https://www.mssqltips.com/sqlservertip/3257/different-approaches-to-correct-sql-server-parameter-sniffing/

  3. https://hackernoon.com/why-parameter-sniffing-hurts-your-sql-query-performance-d73c0da71fbc

I hope above helps.

  • 3
    This case is not parameter sniffing. It's the same as adding OPTION(OPTIMIZE FOR UNKNOWN) to a query inside a stored procedure. The problem is the optimizer can't use statistics hystogram because it knows nothing about parameters' values so it has to use density vector. It could cause the wrong estimation. – Denis Rubashkin May 2 at 11:53
0

Disclaimer: I don't know SQL-server so this is more of a general idea why this may happen.

When a query is sent to the DBMS for the first time it is compiled and then stored in a cache. When the same query is sent again the DBMS can reuse the compiled statement. A query like select x from y where z=3 is in this regard distinct from select x from y where z=4, i.e. the query must be compiled again, and it will occupy memory in the cache. If the cache is full one or more compiled queries has to be sacrificed and thrown away. This is one major reason to use a parameter marker in this query: select x from y where z=?

The down-side is that if there is a skew distribution:

---
|z|
---
|3|
|4|
|4|
|4|
--- 

there's no way to tell how many rows that will be returned by:

`select x from y where z=?`

so some calculated value has to be used. A reasonable approach is to take the number of rows on the table, divided by the number of distinct values in z. I.e. 4/2=2.

If we, on the other hand, have distribution statistics at hand and use a query like select x from y where z=4 we can use the distribution statistics to better predict the number of rows that will be returned from the predicate. Assume a table with 1000 rows and 4 distinct values for a column x and distribution statistics like:

x | cnt
-------
1 | 500
2 | 400 

If we ask a query like WHERE x=1 we immediately know that 500 rows match this predicate. What about WHERE x=3? We know that there are two values for x that is unknown and that there are 1000-500-400=100 possible rows. On average we can assume the result to be 100/2=50 rows. If we, on the other hand, ask a query like WHERE x=?, we would have to assume 1000/4=250 rows returned.

So, using literals over parameter markers may change the execution plan at the cost of more compilations, and more memory used in cache. In general, one may use literals for low cardinality (distinct values) columns and skew distributions, and parameter markers for high cardinality columns and uniform distributions.

Not the answer you're looking for? Browse other questions tagged or ask your own question.