TSQL:调整动态查询搜索 [英] TSQL: Tune Dynamic Query Search

问题描述

在阅读调整 TSQL 查询时，我看到了关于避免(或小心)WHERE 子句中的函数的建议.但是，在某些情况下——比如需要从今天开始的动态日期的搜索——我很好奇查询是否可以进一步调整?例如，下面的查询使用当前日期的 DATEADD 函数，它允许用户随时获取过去三十天的正确信息:

In reading on tuning TSQL queries, I've seen advice on avoiding (or being careful) about functions in the WHERE clause. However, in some cases - like searches that require dynamic dates from today's date - I'm curious if a query can be tuned further? For instance, the query below this uses the DATEADD function for the current date, which allows the user at anytime to get the correct information for the past thirty days:

SELECT *
FROM Zoo..Transportation
WHERE ArrivalDate BETWEEN DATEADD(DD,-30,GETDATE()) AND GETDATE()

如果我尝试消除函数DATEADD，我可以声明一个变量来提取那个时间，然后使用存储在变量中的设置值查询数据，例如:

If I try to eliminate the function, DATEADD, I could declare a variable that will pull that time and then query the data with that set value stored in the variable, such as:

DECLARE @begin DATE
SET @begin = DATEADD(DD,-30,GETDATE())

SELECT *
FROM Zoo..Transportation
WHERE ArrivalDate BETWEEN @begin AND GETDATE()

但是，执行计划和统计数据显示的读取、扫描和批处理成本完全相同.

However, the Execution Plan and Statistics show the exact same number of reads, scans and batch costs.

在这些动态数据实例中(例如，使用今天的日期作为起点)，我们如何减少或消除WHERE 子句中函数的使用?

In these instances of dynamic data (for instance, using today's date as a starting point), how do we reduce or eliminate the use of functions in the WHERE clause?

推荐答案

where 子句中的函数意味着做一些愚蠢的事情，例如:

Functions in the where clause mean doing silly things like:

WHERE DATEPART(WEEK, ArrivalDate) = 1

或

WHERE CONVERT(CHAR(10), ArrivalDate, 101) = '01/01/2012'

例如对 where 子句中的列起作用，这在大多数情况下会破坏可调整性(换句话说，使索引查找无用并强制进行索引或表扫描).

E.g. functions against columns in the where clause, which in most case destroy sargability (in other words, render an index seek useless and force an index or table scan).

我知道有一个例外:

WHERE CONVERT(DATE, ArrivalDate) = CONVERT(DATE, GETDATE())

但我不会将其用于任何其他场景.

But I would not rely on this for any other scenario.

这篇关于TSQL:调整动态查询搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！