How can I improve query performance that blocks other users?

Question

I have the complex sub-select statement that slows down my query and also blocks other users.

select 

                (Case when (Select COUNT(*) from tblQuoteDetails QD where QD.QuoteGUID = a.QuoteGUID) > 1 then
                              (SELECT Round(Sum(dbo.tblQuoteOptions.Premium),2)
                              FROM dbo.tblQuotes AS Q 
                                    INNER JOIN dbo.lstQuoteStatus ON Q.QuoteStatusID = dbo.lstQuoteStatus.QuoteStatusID 
                                    INNER JOIN dbo.tblQuoteOptions ON Q.QuoteGUID = dbo.tblQuoteOptions.QuoteGUID 
                                    --INNER JOIN dbo.tblQuoteOptionPremiums ON dbo.tblQuoteOptionPremiums.QuoteOptionGuid = dbo.tblQuoteOptions.QuoteOptionGUID
                              WHERE     (Q.ControlNo = a.ControlNo) 
                                    AND (Q.OriginalQuoteGUID IS NULL) 
                                    AND (dbo.tblQuoteOptions.Premium <> 0)
                                    AND (DATEDIFF(d,ISNULL(null, dbo.GetEffectiveDate(Q.QuoteGUID)), dbo.GetEffectiveDate(Q.QuoteGUID)) <= 0))
                        Else
                              (SELECT     Round(Avg(dbo.tblQuoteOptions.Premium),2)
                              FROM dbo.tblQuotes AS Q 
                                    INNER JOIN dbo.lstQuoteStatus ON Q.QuoteStatusID = dbo.lstQuoteStatus.QuoteStatusID 
                                    INNER JOIN dbo.tblQuoteOptions ON Q.QuoteGUID = dbo.tblQuoteOptions.QuoteGUID 
                                    --INNER JOIN dbo.tblQuoteOptionPremiums ON dbo.tblQuoteOptionPremiums.QuoteOptionGuid = dbo.tblQuoteOptions.QuoteOptionGUID
                              WHERE     (Q.ControlNo = a.ControlNo) 
                                    AND (Q.OriginalQuoteGUID IS NULL) 
                                    AND (dbo.tblQuoteOptions.Premium <> 0)
                                    AND (DATEDIFF(d,ISNULL(null, dbo.GetEffectiveDate(Q.QuoteGUID)), dbo.GetEffectiveDate(Q.QuoteGUID)) <= 0))
                              --GROUP BY dbo.tblQuoteOptions.QuoteOptionID
                       End) As QuotedPremium
    FROM tblQuotes a

Not sure if I'm reading execution plan correctly but that's what I see:

Any idea what approach should I take here?

Thanks

do you think you can use [temporary tables](http://stackoverflow.com/questions/16767645/why-is-there-a-huge-performance-difference-between-temp-table-and-subselect) ? — RegularNormalDayGuy, May 19 '17 at 16:56
There are many things "left of center" in that query. The biggest issue is using scalar functions. They are horribly inefficient and you have them splattered all over the place. You have totally pointless isnull functions where the first value is null. You also might want to take a peek at this article. https://sqlblog.org/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations — Sean Lange, May 19 '17 at 16:57
Note : Using GUIDs instead of compound indexes is a terrible strategy. Especially if you apply clustered indexes to them. DO NOT DO THIS! It does not scale and will destroy performance on systems that experience high frequency of insert, update and delete operations. — dbbri, May 19 '17 at 18:36
if you aren't concerned with perfectly live data then running some of the data into temp tables then doing the final query on the temp tables can be an effective way to speed up a query. Just remember that if the underlying data changes between generating the temp tables and building the final output your output won't reflect it. — David, May 19 '17 at 19:32
What is the transaction isolation level for your database and the queries/transactions you're running? The default for SQL Server is `Read Committed` and readers can block writers with this setting. As long as you have adequate `tempdb` space/performance, switching to `Snapshot` or `Read Committed Snapshot` should alleviate the "others are getting blocked" problem. See http://stackoverflow.com/q/2741016/1324345 — alroc, May 20 '17 at 12:10

score 1 · Answer 1 · answered May 19 '17 at 17:10

Looking into the query completely without having access to your environment won't be terribly efficient, but I can safely say that Key Lookups are expensive, and can often be eliminated by ensuring the columns you're getting from a joined table are INCLUDEd in the index being used. Considering the two key lookups we can amount to almost 80% of the query cost, I'd start there.

score 0 · Answer 2 · answered May 19 '17 at 20:55

Also, part of the issue is the use of DATEDIFF inside a WHERE clause.

AND (DATEDIFF(d,ISNULL(null, dbo.GetEffectiveDate(Q.QuoteGUID))dbo.GetEffectiveDate(Q.QuoteGUID)) <= 0))

This will severely hamper the optimizer from doing it's job. Simplifying this particular comparison could make a big difference.

How can I improve query performance that blocks other users?

2 Answers2