How to improve the performance of a non-deterministic column function in a where or join clause?

Question

I would like to improve the performance of a query that has a where clause with a non-deterministic function call.

Select Count(*) From table1 Where DateDiff(month, Cast(table1.Date As DateTime), GetDate()) = 0 

I think the question is equally true for Joins:

 Select table1.column1 From table1 Inner Join table2 On table1.MonthOfHappyness = DateDiff(month, Cast(table2.SomeDate As DateTime), GetDate()) 

As

 DateDiff(month, Cast(adrPkt.PktRevDato As DateTime), GetDate()) 

is not deterministic. I cannot create a view with a computed column and index it. see: SQL Server (2005) - "Deleted On." . DATETIME and indexing

What options do I have to improve performance?

Answer 1

Besides the non-deterministic function, the problem that I see is that you are doing calculations in a field. This (usually) makes any index in the field unusable in the query.

The second paragraph of this link ( Ten Common SQL Programming Errors (Indexed Column Functions in Predicates) ) provides more detailed information on when this happens, how to avoid it, and how sometimes optimizers can use indexes despite using functions.

In short, instead of relying on improved optimizers, you can often change the query by keeping the field intact (without any calculations on it), but instead making (inverse) calculations with different values. In your case, the current date provided by GetDate() . Then the query can use the index of the table1.Date field.

So you can use something like:

 SELECT COUNT(*) FROM table1 WHERE table1.Date BETWEEN /* First Day of Current Month */ AND /* Last Day of Current Month */ 

And you only need to find the features that you will receive on the first and last day of the current month.

This blog post can help you: sql-server-query-to-find-first-and-last-day-of-current-month /

Even better, this StackOverflow question / answer: the easiest way to create-a-date-that-is-the-first-day-of-the-month-Given-other-date

I will need to check, but I think this small change above will be:

 SELECT COUNT(*) FROM table1 WHERE table1.Date >= /* First Day of Current Month */ DATEADD(mm, DATEDIFF(mm, 0, GetDate() ), 0) AND table1.Date < /* First Day of Next Month */ DATEADD(mm, 1 + DATEDIFF(mm, 0, GetDate() ), 0) 

Answer 2

Have you tried to use the Local temporary table to insert all the required records first? Finally, do the calculations on the temporary table and return it.