如何提高where子句或join中列的非确定性函数的性能？

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

我想提高查询的性能，它有一个带有非确定性函数调用的where子句。

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

I think the question is equally valid for Joins:

我认为这个问题对于Joins同样有效：

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

Since

以来

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

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

是非确定性的我不能用计算列创建一个视图并将其编入索引。请参阅：SQL Server（2005） - “已删除”DATETIME和索引

What options do I have to improve performance?

我有什么选择来提高性能？

2 个解决方案

#1

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

除了非确定性函数，我看到的问题是你在一个字段上进行计算。这（通常）使得该字段上的任何索引都不能被查询使用。

The second paragraph of this link (Ten Common SQL Programming Mistakes (Functions on indexed columns in predicates)) provides more detailed info on when this happens, how to avoid it and how sometimes optimizers can use indexes despite the use of functions.

此链接的第二段（十个常见SQL编程错误（谓词中的索引列上的函数））提供了有关何时发生这种情况的更详细信息，如何避免它以及有时优化器如何使用索引尽管使用了函数。

In short, instead of relying on improved optimizers, it's often possible to change the query by keeping the field intact (without doing any calculations on it), but instead doing the (reversed) calculations to the other values. In your case to the current date that is provided by GetDate(). Then the query can use the index of the field table1.Date.

简而言之，通常可以通过保持字段完整（不对其进行任何计算）来改变查询，而是依赖于改进的优化器，而是对其他值进行（反向）计算。在您的情况下，GetDate（）提供的当前日期。然后查询可以使用字段table1.Date的索引。

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 functions that get you the first and last day of current month.

而且您只需找到能够让您获得当月第一天和最后一天的功能。

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

这篇博文可以帮助你：sql-server-query-to-find-first-last-last-of-current-month /

Even better, this * question/answer: simplest-way-to-create-a-date-that-is-the-first-day-of-the-month-given-another-date

更好的是，这个*问题/答案：最简单的创建日期 - 这是第一天给出的另一个日期

I'll have to test but I think this slight variation of the above will do:

我将不得不测试，但我认为上述的这种轻微变化将会：

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)

#2

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

您是否尝试使用本地临时表首先插入所有必需的记录？最后，在临时表上进行计算并返回它。

#1