当日期范围较小时，SQL 查询需要更长的时间? [英] SQL query takes longer time when date range is smaller?

问题描述

我有一个简单的 select 语句，它从 SQL Server 2000(这么旧)的表中选择数据，该表大约有 10-2000 万行，就像这样 -

I have a simple select statement which selects data from a SQL Server 2000 (so old) table with about 10-20 million rows like this -

@startDate = '2014-01-25' -- yyyy-mm-dd
@endDate = '2014-02-20'

SELECT 
    Id, 6-7 other columns
FROM 
    Table1 as t1
LEFT OUTER JOIN 
    Table2 as t2 ON t1.Code = t2.Code
WHERE 
    t1.Id = 'G59' -- yes, its a varchar
    AND (t1.Entry_Date >= @startDate AND t1.Entry_Date < @endDate) 

这在大约 10 秒内给了我大约 40 K 行.但是，如果我设置@startDate = '2014-01-30'，始终保持@endDate 相同，那么查询大约需要 2 分 30 秒

This gives me about 40 K rows in about 10 seconds. But, if I set @startDate = '2014-01-30', keeping @endDate same ALWAYS, then the query takes about 2 min 30 sec

为了生成相同数量的行，我再次尝试使用 01-30，结果花了 2 分 48 秒.

To produce the same number of rows, I tried it with 01-30 again and it took 2 min 48 seconds.

我很惊讶地看到了差异.没想到差别这么大.相反，我希望它在较小的日期范围内花费相同的时间或更短的时间.

I am surprised to see the difference. I was not expecting the difference to be so big. Rather, I was expecting it to take the same time or lesser for a smaller date range.

这可能是什么原因，我该如何解决?

What could be the reason for this and how do I fix it ?

推荐答案

您最近是否插入和/或删除了大量行?可能是表索引的统计信息已过时，因此查询优化器将在较小的日期范围内执行索引查找 + 键查找"方案 - 但结果证明这比仅执行表要慢/聚集索引扫描.

Have you recently inserted and/or deleted a large number of rows? It could be that the statistics on the table's indices are out of date, and thus the query optimizer will go for a "index seek + key lookup" scenario on the smaller date range - but that turns out to be slower than just doing a table/clustered index scan.

我建议更新统计信息(请参阅此有关如何更新统计数据的 TechNEt 文章) 并重试 - 有任何改进吗?

I would recommend to update the statistics (see this TechNEt article on how to update the statistics) and try again - any improvement?

查询优化器使用统计信息来确定是仅进行表扫描(只需读取表的所有数据页并选择匹配的行)更快，还是在索引中搜索搜索值更快；该索引通常不包含所有数据 - 因此一旦找到匹配项，就需要在表上执行键查找以获取数据 - 这是一项昂贵的操作，因此它仅适用于小型数据集.如果过时的统计信息误导"了查询优化器，它可能会选择一个次优的执行计划

The query optimizer uses statistics to determine whether it's faster to just do a table scan (just read all the table's data pages and select the rows that match), or whether it's faster to search for the search value in an index; that index typically doesn't contain all the data - so once a match is found, a key lookup needs to be performed on the table to get at the data - which is an expensive operation, so it's only viable for small sets of data. If out-of-date statistics "mislead" the query optimizer, it might choose a suboptimal execution plan

这篇关于当日期范围较小时，SQL 查询需要更长的时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！