硬编码日期字符串比SELECT中的DateTime快得多？ [英] Hard-Coding date string much faster than DateTime in SELECT?

问题描述

我有一个非常大的表（1500万行，这是一个审计表）。

I have a very large table (15 million rows, this is an audit table).

我需要运行一个查询来检查审计表中的事件在某个特定日期之后并且符合某些标准（我正在寻找仅在当天发生的审计记录）

I need to run a query that checks for occurrences in the audit table that are after a certain date and meet certain criteria (I am looking for audit records that took place on current day only)

当我运行时：

SELECT Field1, Field2 FROM AUDIT_TABLE WHERE AUDIT_DATE >= '8/9/12'

结果回来相当快（几秒钟，15M行也不错）

The results come back fairly quick (a few seconds, not bad for 15M rows)

当我跑步时：

SELECT Field1, Field2 FROM AUDIT_TABLE WHERE AUDIT_DATE >= @DateTime

需要11-15秒并进行全表扫描。

It takes 11-15 seconds and does a full table scan.

我实际的字段查询是DATETIME类型，索引也在该字段上。

The actual field I am querying against is a DATETIME type, and the index is also on that field.

推荐答案

听起来好像你被困在一个糟糕的计划中，可能是因为有人在某些点上使用了一个参数t选择了足够的表，表扫描是该参数值的最有效方式。尝试以这种方式运行查询：

Sounds like you are stuck with a bad plan, probably because someone used a parameter at some point that selected enough of the table that a table scan was the most efficient way for that parameter value. Try running the query once this way:

SELECT ... FROM AUDIT_TABLE WHERE AUDIT_DATE >= @DateTIme OPTION (RECOMPILE);

然后以这种方式更改您的代码：

And then change your code this way:

SELECT ... FROM dbo.AUDIT_TABLE WHERE AUDIT_DATE >= @DateTime;

使用 dbo。前缀将在至少可以防止具有不同模式的不同用户使用不同版本的计划污染计划缓存。它还会将未来的查询与存储的错误计划取消关联。

Using the dbo. prefix will at the very least prevent different users with different schemas from polluting the plan cache with different versions of the plan. It will also disassociate future queries from the bad plan that is stored.

如果要在选择最近的行（小％）和大量行之间有所不同，我可能只是将OPTION（RECOMPILE）留在那里。每次重新编译时支付次要的CPU罚款比在大多数查询中遇到糟糕的计划要便宜。

If you are going to vary between selecting recent rows (small %) and a lot of rows, I would probably just leave the OPTION (RECOMPILE) on there. Paying the minor CPU penalty in recompilation every time is going to be cheaper than getting stuck with a bad plan for most of your queries.

我见过的另一个技巧绕过参数嗅探：

Another trick I've seen used to bypass parameter sniffing:

ALTER PROCEDURE dbo.whatever
  @DateTime DATETIME
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @dt DATETIME;
  SET @dt = @DateTime;

  SELECT ... WHERE AUDIT_DATE >= @dt;
END
GO

这是一种肮脏且不直观的伎俩，但它使优化器更好地了解参数值，并更好地优化该值。

It's kind of a dirty and unintuitive trick, but it gives the optimizer a better glimpse at the parameter value and a better chance to optimize for that value.

这篇关于硬编码日期字符串比SELECT中的DateTime快得多？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！