Query tuning guidance
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.
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