Sql-server – Why is query choosing horrible execution plan

performancequery-performancesql serversql-server-2016

I am trying to make a query that our application sends in more efficient. I slightly modified the query in SSMS and it will execute in about 1 sec.

Query A

SELECT  O.Code AS 'Code', O.[Action] AS 'Action',
    SUM(OpenResponseWithin) AS 'OpenResponseWithin',
    CONVERT(VARCHAR,convert(decimal(10,2),(((SUM(OpenResponseWithin))*100))/convert(decimal(10,2),(SUM(OpenResponseWithin)+SUM(OpenResponseAfter))))) + '%' AS 'OpenResponseWithinPercentage',
    SUM(OpenResponseAfter) AS 'OpenResponseAfter',
    CONVERT(VARCHAR,convert(decimal(10,2),(((SUM(OpenResponseAfter))*100))/convert(decimal(10,2),(SUM(OpenResponseWithin)+SUM(OpenResponseAfter))))) + '%' AS 'OpenResponseAfterPercentage',
    (SUM(OpenResponseWithin)+SUM(OpenResponseAfter)) AS 'OpenTotal',
    SUM(CloseResponseWithin) AS 'CloseResponseWithin',
    CONVERT(VARCHAR,convert(decimal(10,2),(((SUM(CloseResponseWithin))*100))/convert(decimal(10,2),(SUM(OpenResponseWithin)+SUM(OpenResponseAfter))))) + '%' AS 'CloseResponseWithinPercentage',
    SUM(CloseResponseAfter) AS 'CloseResponseAfter',
    CONVERT(VARCHAR,convert(decimal(10,2),(((SUM(CloseResponseAfter))*100))/convert(decimal(10,2),(SUM(OpenResponseWithin)+SUM(OpenResponseAfter))))) + '%' AS 'CloseResponseAfterPercentage',
    SUM(CloseNever) AS 'CloseNever',
    CONVERT(VARCHAR,convert(decimal(10,2),(((SUM(CloseNever))*100))/convert(decimal(10,2),(SUM(OpenResponseWithin)+SUM(OpenResponseAfter))))) + '%' AS 'CloseNeverPercentage'
FROM Custom_OpenCodeMRC O
WHERE O.ActionDate BETWEEN '10/5/2017' AND '12/4/2017'
      AND
      O.Code IN ('BERZ20','BERZ21','BERZ24','BERZ50','FTHZ63','YOR56','YOR57')
GROUP BY O.Code,O.[Action]
ORDER BY O.Code,O.[Action]

If I leave the query the way the application passes it in with parameters, it takes at least 20 seconds to execute.

Query B

DECLARE @parm1 NVARCHAR(10) = '10/05/2017';
DECLARE @parm2 NVARCHAR(10) = '12/04/2017';
DECLARE @parm9 NVARCHAR(6) = 'BERZ20';
DECLARE @parm8 NVARCHAR(6) = 'BERZ21';
DECLARE @parm7 NVARCHAR(6) = 'BERZ24';
DECLARE @parm6 NVARCHAR(6) = 'BERZ50';
DECLARE @parm5 NVARCHAR(6) = 'FTHZ63';
DECLARE @parm4 NVARCHAR(5) = 'YOR56';
DECLARE @parm3 NVARCHAR(5) = 'YOR57';

SELECT O.Code AS 'Code',O.[Action] AS 'Action',
    SUM(OpenResponseWithin) AS 'OpenResponseWithin',
    CONVERT(VARCHAR,convert(decimal(10,2),(((SUM(OpenResponseWithin))*100))/convert(decimal(10,2),(SUM(OpenResponseWithin)+SUM(OpenResponseAfter))))) + '%' AS 'OpenResponseWithinPercentage',
    SUM(OpenResponseAfter) AS 'OpenResponseAfter',
    CONVERT(VARCHAR,convert(decimal(10,2),(((SUM(OpenResponseAfter))*100))/convert(decimal(10,2),(SUM(OpenResponseWithin)+SUM(OpenResponseAfter))))) + '%' AS 'OpenResponseAfterPercentage',
    (SUM(OpenResponseWithin)+SUM(OpenResponseAfter)) AS 'OpenTotal',
    SUM(CloseResponseWithin) AS 'CloseResponseWithin',
    CONVERT(VARCHAR,convert(decimal(10,2),(((SUM(CloseResponseWithin))*100))/convert(decimal(10,2),(SUM(OpenResponseWithin)+SUM(OpenResponseAfter))))) + '%' AS 'CloseResponseWithinPercentage',
    SUM(CloseResponseAfter) AS 'CloseResponseAfter',
    CONVERT(VARCHAR,convert(decimal(10,2),(((SUM(CloseResponseAfter))*100))/convert(decimal(10,2),(SUM(OpenResponseWithin)+SUM(OpenResponseAfter))))) + '%' AS 'CloseResponseAfterPercentage',
    SUM(CloseNever) AS 'CloseNever',
    CONVERT(VARCHAR,convert(decimal(10,2),(((SUM(CloseNever))*100))/convert(decimal(10,2),(SUM(OpenResponseWithin)+SUM(OpenResponseAfter))))) + '%' AS 'CloseNeverPercentage'
FROM Custom_OpenCodeMRC O
WHERE O.ActionDate BETWEEN @parm1 AND @parm2
      AND
      O.Code IN (@parm3,@parm4,@parm5,@parm6,@parm7,@parm8,@parm9)
GROUP BY O.Code,O.[Action]
ORDER BY O.Code Asc,O.[Action] Asc

I have the execution plans saved, but they are very long. If someone would like to see them, I can post the plans in XML. Here are some query stats.

 +-----------+---------------------+
 | Query A   | CPU time = 1439 ms  |
 | Query B   | CPU time = 23282 ms |
 +-----------+---------------------+

AND

+----------------+--------------+--------------+---------------+---------------+
|   Table        |  Query A     |   Query B    |  Query A      |  Query B      |
|                |  Scan Count  |  Scan Count  | Logical Reads | Logical Reads |
+----------------+--------------+--------------+---------------+---------------+
| ResponseAction |      1       |      1       |      2        |      2        |
|    Code        |      7       |      5       |      14       |      13       |
|   Workfile     |      0       |      0       |      0        |      0        |
|   Worktable    |      57      |    1,305     |    30,712     |    1,735,281  |
|   Response     |      7       |      7       |   12,1136     |    12,1137    |
| ResponseHistory|      24      |      23      |     3,507     |     3,507     |
|    Ticket      |      5       |      5       |     907       |      907      |
|  Organization  |      0       |      5       |    3,479      |     1,463     |
+----------------+--------------+--------------+---------------+---------------+

They both have the exact same results, but much different execution times. Can someone please explain to me why Query A uses a much more efficient execution plan than Query B? Thanks!

Here are links to the execution plans:

Query A

Query B

Best Answer

Imagine that you have to plan a trip to some mystery location. It could be anywhere in the world. Your options for traveling are by foot, by car, or by plane. You need to choose before knowing the destination. What would you pick? I would pick traveling by plane. It's the best option if you take the average travel time for all possible options in the world. Of course, you could get unlucky if your destination is say, down the street. Taking a plane will be relatively inefficient for that destination. On the other hand, that's certainly a better option than needing to walk thousands of miles.

Using local variables in queries often puts the query optimizer in the same type of situation. The query optimizer aims to create a cached plan that performs well for all possible input variables. It will often use the density of the statistics object, which is one way to get an "average" cardinality estimate. By default, it will not embed the value of your specific parameters into the query and use those values to create an efficient plan.

For another way to look at it, your data and table structures might mean that one query plan works well for processing a relatively small amount of data but a different query plan works well for processing a relatively large amount of data. For example, you might want the query plan to change if the value of @parm1 shifts from '10/05/2017' to '10/05/2000'. With local variables you get just a single cached plan. That cached plan will result in less than optimal performance for one or both of those date values.

The simplest fix to improve the performance of query B is to add a RECOMPILE hint. This enables the parameter embedding optimization which gives you a custom plan based on the variable values at runtime. The downside of RECOMPILE is that the query optimizer needs to compile a new query plan each time. If your good query takes over a second to run you probably don't have to worry too much about that.

Related Question