SQL效率 - 使用dateAdd函数查询两次;或者SubQuery和DateAdd函数一次;在BETWEEN的日期

The expense is using the DateAdd function not only in the SELECT, but also in the WHERE; Or using the Sub Query which initially returns more data than I need, but then can be filtered without using the DateAdd function again on the database.

费用不仅在SELECT中使用DateAdd函数,而且在WHERE中使用;或者使用Sub Query,它最初会返回比我需要的更多的数据,但是可以在数据库上再次使用DateAdd函数进行过滤。

Execution Plan seems to imply that they are identical as far as it is concerned. I'm wondering which would be more efficient?

执行计划似乎暗示它们是相同的。我想知道哪个更有效率?

    DECLARE @DateFrom DateTime
    SET @DateFrom = '2011-05-27'
    DECLARE @DateTo DateTime
    SET @DateTo = '2011-06-27'

    SELECT id, name, 
    dateAdd(hour, datediff(hour, getdate(), getutcdate()), --UTC offset
            dateadd(second, itsm_requiredbyx, '1/1/1970 12:00 AM')) as itsm_requiredbyx
    FROM tablename
    WHERE dateAdd(hour, datediff(hour, getdate(), getutcdate()), --UTC offset
            dateadd(second, itsm_requiredbyx, '1/1/1970 12:00 AM'))
            BETWEEN @DateFrom AND @DateTo

    ORDER BY itsm_requiredbyx desc

    ---------------------------------------------------------------------------------------------

    SELECT *
    FROM
        (
        select id, name, 
        dateAdd(hour, datediff(hour, getdate(), getutcdate()), --UTC offset
                dateadd(second, itsm_requiredbyx, '1/1/1970 12:00 AM')) as itsm_requiredbyx
        from tablename 
        ) RR
    WHERE itsm_requiredbyx BETWEEN @DateFrom AND @DateTo
    ORDER BY itsm_requiredbyx desc

2 个解决方案

#1

I don't think it matters which of the two you use. And the Execution Plans agree.

我不认为你使用哪两个都很重要。并执行计划同意。

But it seems you are doing calculations to column itsm_requiredbyx and then check if the result is between two external values, @DateFrom and @DateTo. This way, all datetimes from this field are processed by the functions before the WHERE conditions can be applied and no index can be used. The second link (Ten Common SQL Programming Mistakes) in @DOK's answer provide more detailed info on why and when this happens.

但似乎你正在对其列为itsm_requiredbyx进行计算,然后检查结果是否在两个外部值@DateFrom和@DateTo之间。这样,在应用WHERE条件之前,函数可以处理来自该字段的所有日期时间,并且不能使用索引。 @ DOK答案中的第二个链接(十个常见的SQL编程错误)提供了有关何时以及何时发生这种情况的更详细信息。

If you don't do any calculations to the column, but you do the (reversed) calculations to the external values instead and then check if itsm_requiredbyx is between these two calculated values, the query can use an index of itsm_requiredbyx (and the functions will only be called twice and not for every row in the table).

如果您不对列进行任何计算,但是您对外部值执行(反向)计算,然后检查其m_requiredbyx是否在这两个计算值之间,则查询可以使用其m_requiredbyx的索引(并且函数将只被调用两次而不是表中的每一行)。

#2

This article might help you choose. If your date columns are indexed, there might be a big difference between the methods used, particularly in the WHERE clause.

本文可能会帮助您选择。如果对日期列建立索引,则使用的方法之间可能存在很大差异,尤其是在WHERE子句中。

As it says,

正如它所说,

If you are searching large tables with lots of records, you will most likely index some of the date columns that are commonly used to constrain queries. When a date column is used in a WHERE clause, the query optimizer will not use an index if the date column is wrapped in a function.

如果要搜索包含大量记录的大型表,则很可能会索引一些常用于约束查询的日期列。在WHERE子句中使用日期列时,如果日期列包含在函数中,查询优化器将不使用索引。

This is also explained in Ten Common SQL Programming Mistakes, particularly under #2 Functions on indexed columns in predicates:

这在十个常见的SQL编程错误中也有解释,特别是在谓词中索引列的#2函数下:

The problem arises from the fact that the index columns are being passed to a function, which the query engine must then evaluate for every single row in the table. In cases such as these, the WHERE clause predicate is deemed "non-SARGable" and the best that the query optimizer can do is perform a full index or table scan.

问题产生于索引列被传递给函数,然后查询引擎必须为表中的每一行计算。在诸如此类的情况下,WHERE子句谓词被视为“非SARGable”,查询优化器可以执行的最佳操作是执行完整索引或表扫描。

To make sure the indexes get used, we need to avoid the use of functions on the indexed columns.

为了确保使用索引,我们需要避免在索引列上使用函数。

#1