THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Linchi Shea

Checking out SQL Server via empirical data points

Performance impact: a little business logic goes a long way

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.

Published Friday, December 23, 2011 1:13 AM by Linchi Shea



jchang said:

Good tip, along the same line, if our query were select max(col) from table when there is an index leading y on col, then it should be fast, simply go to the end of the index. however, if the table is partitioned, the query plan does a scan instead of going to the end of each partition. So the WHERE col > @val helps here too.

December 28, 2011 10:27 AM

RichB said:

Any comment on the performance difference between select max and select top 1 order desc with fast first?

January 3, 2012 10:28 AM
New Comments to this post are disabled

About Linchi Shea

Checking out SQL Server via empirical data points

This Blog


Privacy Statement