Query tuning guidance

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.


qryfiles.zip (491.5 kB)
avatar image By Maahi 1 asked Dec 27, 2017 at 09:59 AM
more ▼
(comments are locked)
10|10000 characters needed characters left

1 answer: sort voted first

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

avatar image By Hugo Kornelis 271 answered Dec 27, 2017 at 05:46 PM
more ▼
(comments are locked)
avatar image Maahi Dec 27, 2017 at 06:40 PM

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

avatar image Hugo Kornelis Dec 27, 2017 at 07:30 PM


   (SELECT a.SampleCondition
    SELECT 4 WHERE a.SampleCondition IS NULL) AS x
avatar image Maahi Dec 28, 2017 at 02:26 AM +

Thanks a ton Sir.

10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

We are Moving!


Follow this question



asked: Dec 27, 2017 at 09:59 AM

Seen: 97 times

Last Updated: Dec 28, 2017 at 03:58 AM