Query tuning guidance

Maahi 2017-12-27 09:59:44

Hi All,

Need some suggestions on tuning below attached query. Its a reporting query which takes more than 5 mins to execute. The problem with this query doesn't have any proper filters. checking what all things can be done in order to improve the response time of this particular query. link text

Any suggestions or thoughts? Uploaded the src query, plan explorer actual plan, ssms actual plan.

Thank you.

/storage/attachments/2176-qryfiles.zip

Hugo Kornelis 2017-12-27 17:46:37

At first sight, the query plan does not look like it does anything weird. No bad estimates, no strange operators where I would not expect them. You are simply doing a lot of work on a lot of rows, and that will take some time.

That being said, there might still be some things you can do.

First, the execution plan tells you that it is running into issues with data types; various columns have to be implicitly converted to other data types. You might want to look into where those are coming from. I think they are related to the CONCAT expressions in the final SELECT and in that case they won't affect performance, but it never hurst to check.

Second, you repeat a very complex query twice, with UNION ALL. The first time it selects all rows; the second time it only selects rows with SampleCondition NOT NULL, then does the exact same joins with those rows, and returns the same data except that the SampleCondition is replaced with the fixed value 4. This might not be the best way to do this. Instead of repeating the entire subquery, you can:
a) Do a UNION ALL between only the Sample table and the non-NULL rows from the Sample table; then join all other columns to the result of that; or
b) Replace the entire UNION ALL logic with a CROSS APPLY to a subquery that reads SELECT a.SampleCondition UNION ALL SELECT 4 WHERE a.SampleCondition IS NOT NULL

Maahi 2017-12-27 18:40:12

Thanks Hugo. However, I didnt get it completely. Can you provide sample pseudo code so that I can do the re-write.

Hugo Kornelis 2017-12-27 19:30:59

Sure.

CROSS APPLY
  (SELECT a.SampleCondition
   UNION ALL
   SELECT 4 WHERE a.SampleCondition IS NULL) AS x

Maahi 2017-12-28 02:26:26

Thanks a ton Sir.

Maahi 2017-12-28 03:58:34

One more thing, can this code be converted to an indexed view ? if so how can we do it? and is there any side-effects/limitations of indexed views?

December 27, 2017

No Comments