如何快速从日期/时间字段SQL Server中选择DISTINCT日期 [英] How to quickly select DISTINCT dates from a Date/Time field, SQL Server

问题描述

我想知道是否有一个性能卓越的查询，从SQL Server中的datetime字段的表中选择不同的日期（忽略时间）。

I am wondering if there is a good-performing query to select distinct dates (ignoring times) from a table with a datetime field in SQL Server.

我的问题没有让服务器真正做到这一点（我已经看到这个问题已经有了，我们已经有了类似的东西，使用DISTINCT）。问题是能否更快地完成任务。使用我们正在使用的数据，我们当前的查询返回〜80个不同的日期，其中有大约40,000行的数据（在另一个索引列上过滤后），日期列上有一个索引，查询总是设置为5+秒。这太慢了。

My problem isn't getting the server to actually do this (I've seen this question already, and we had something similar already in place using DISTINCT). The problem is whether there is any trick to get it done more quickly. With the data we are using, our current query is returning ~80 distinct days for which there are ~40,000 rows of data (after filtering on another indexed column), there is an index on the date column, and the query always manages to take 5+ seconds. Which is too slow.

更改数据库结构可能是一个选项，但不太可取。

Changing the database structure might be an option, but a less desirable one.

推荐答案

在datetime字段中涉及CAST或TRUNCATE或DATEPART操作的每个选项都有同样的问题：查询必须扫描整个结果集（40k）才能查找不同的日期。性能可能在各种实现之间略有不同。

Every option that involves CAST or TRUNCATE or DATEPART manipulation on the datetime field has the same problem: the query has to scan the entire resultset (the 40k) in order to find the distinct dates. Performance may vary marginally between various implementaitons.

您真正需要的是具有可以眨眼产生响应的索引。您可以拥有一个持久化的计算列，并具有索引（需要表结构更改）或索引视图（要求Enterprise Edition for QO考虑索引开箱即用）

What you really need is to have an index that can produce the response in a blink. You can either have a persisted computed column with and index that (requires table structure changes) or an indexed view (requires Enterprise Edition for QO to consider the index out-of-the-box).

持久计算列：

alter table foo add date_only as convert(char(8), [datetimecolumn], 112) persisted;
create index idx_foo_date_only on foo(date_only);

索引视图：

create view v_foo_with_date_only
with schemabinding as 
select id
    , convert(char(8), [datetimecolumn], 112) as date_only
from dbo.foo;   
create unique clustered index idx_v_foo on v_foo_with_date_only(date_only, id);

更新

要完全消除扫描，可以使用GROUP BY欺骗索引视图，如下所示：

To completely eliminate the scan one could use an GROUP BY tricked indexed view, like this:

create view v_foo_with_date_only
with schemabinding as 
select
    convert(char(8), [d], 112) as date_only
    , count_big(*) as [dummy]
from dbo.foo
group by convert(char(8), [d], 112)

create unique clustered index idx_v_foo on v_foo_with_date_only(date_only)

查询从foo中选择不同的date_only 将使用此索引视图。在技​​术上仍然是扫描，但是在已经不同的索引上，因此只扫描所需的记录。它是一个黑客，我估计，我不会推荐它用于现场制作代码。

The query select distinct date_only from foo will use this indexed view instead. Is still a scan technically, but on an already 'distinct' index, so only the needed records are scanned. Its a hack, I reckon, I would not recommend it for live production code.

AFAIK SQL Server无法扫描真正的索引，跳过重复，即。寻求顶部，然后寻求超过顶部，然后连续寻求大于最后找到。

AFAIK SQL Server does not have the capability of scanning a true index with skipping repeats, ie. seek top, then seek greater than top, then succesively seek greater than last found.

这篇关于如何快速从日期/时间字段SQL Server中选择DISTINCT日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！