I’m running into this little performance tuning pattern enough number of times that it is worth a special mention.
As it often happens, the app folks complain about a proc call being very slow, and I track it down to a specific line in the proc. The line appears to be harmlessly simple, as simple as the following:
SELECT MAX(BusinessDate) FROM BusinessTransactions;
But it takes a long time to complete. Upon further inspection, it turns out that BusinessTransactions is actually a very complex view with multiple joins and a few union all’s across archive tables. Oh, how I hate these views! But that’s a different story.
You can try to optimize the view, and that’s fine. But there is a much simpler way.
A little bit of checking around reveals that BusinessTransactions is holding business transaction data that is streaming in 24x7 as long as the company is open for business, and the BusinessDate column stamps each row with the date on which the transaction is entered. Armed with that knowledge, we can re-write the query with a small change:
SELECT MAX(BusinessDate) FROM BusinessTransactions
WHERE BusinessDate > @today_minus_20;
Variable @today_minus_20 is essentially DATEADD(DAY, –20, GETDATE()). The nature of the business dictates that there will always be data from the past 20 days in BusinessTransactions, and in the unlikely scenario where it does not have any data from the past 20 days, we have a much bigger problem to worry about than this query not returning the correct result.
In a recent case, after I made this little change, the query duration went from about 20 minutes to less than 15 seconds.