4

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

18

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.

  • Thanks Joe, I looked at the link you provided and it seems like that is for stored procedures or functions. Custom _OpenCodeMRC in my query is a view. I tried adding the RECOMPILE option to the view and the SSMS editor didn't like it. Can RECOMPLIE be added to a view? That would be ideal for me since I don't have total control on what the application passes in. – Jason Dec 5 '17 at 1:12
  • 1
    @Jason how are people passing variables to the view? – Erik Darling Dec 5 '17 at 1:40
  • @sp_BlitzErik, it is a web application. The end user only selects the date range. Each user has certain codes assigned to them. The application determines the codes and builds the query based on the date range selected. – Jason Dec 5 '17 at 1:48
  • 1
    @Jason the recompile hint would need to be appended to the query assembled by the web application. If you can't change that, you may need to look into plan guides. If you have full control, you may want to consider using a stored procedure or sp_executesql instead. – Erik Darling Dec 5 '17 at 1:57
  • @Jason, your data type look inappropriate too. – KumarHarsh Dec 5 '17 at 6:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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