問題描述

在閱讀調整 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:調整動態查詢搜索的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！