代码之家 › 专栏 › 技术社区 › RSlaughter

SQL按年、月、周、日、小时分组SQL与过程性能

performance sql-server sql

RSlaughter · 技术社区 · 17 年前

我需要编写一个查询,将大量记录按时间段从年到小时进行分组。

我最初的方法是在C#中按程序确定周期,迭代每个周期并运行SQL以获取该周期的数据,同时构建数据集。

SELECT Sum(someValues)
FROM table1
WHERE deliveryDate BETWEEN @fromDate AND @ toDate

我随后发现,我可以使用Year()、Month()、Day()、datepart(week,date)和datepart(hh,date)对记录进行分组。

SELECT Sum(someValues)
FROM table1
GROUP BY Year(deliveryDate), Month(deliveryDate), Day(deliveryDate)

我担心的是,在groupby中使用datepart会导致比在一段时间内多次运行查询性能更差,因为无法有效地使用datetime字段上的索引;你有没有想过这是不是真的?

谢谢。

7 回复 | 直到 17 年前

ShuggyCoUk 17 年前

与任何与性能相关的事情一样测量

检查第二种方法的查询计划会提前告诉你任何明显的问题(当你知道不需要全表扫描时,可以进行全表扫描),但测量是无可替代的。在SQL性能测试中,应该使用适当大小的测试数据进行测量。

由于这是一个复杂的情况,您不仅仅是比较两种不同的方法来执行单个查询,而是将单个查询方法与迭代方法进行比较,您的环境的各个方面可能会在实际性能中发挥重要作用。

明确地

与一个大查询方法相比,应用程序和数据库之间的“距离”,因为每次调用的延迟都会浪费时间
无论您是否使用预处理语句(在每个查询上都会给数据库引擎带来额外的解析工作)
范围查询本身的构建是否代价高昂(受2的影响很大)

Galwegian 17 年前

如果你把一个公式放在比较的字段部分, 你会得到一个表格扫描 .

索引在字段上,而不是在datepart(字段)上, 因此,必须计算所有字段 -所以我认为你的预感是对的。

Mladen Prajdic 17 年前

你可以做类似的事情:

SELECT Sum(someValues)
FROM 
(
    SELECT *, Year(deliveryDate) as Y, Month(deliveryDate) as M, Day(deliveryDate) as D
    FROM table1
    WHERE deliveryDate BETWEEN @fromDate AND @ toDate
) t
GROUP BY Y, M, D

Walter Mitty 17 年前

如果你能容忍再加入一张桌子对性能的影响,我有一个建议,看起来很奇怪,但效果很好。

创建一个我称之为ALMANAC的表,其中包含周、月、年等列。您甚至可以为日期的公司特定功能添加列,例如该日期是否为公司假期。您可能希望添加一个开始和结束时间戳,如下所述。

虽然你可能每天只坐一排,但当我这样做的时候,我发现每班坐一排很方便,因为一天有三班。即使按照这个速度,十年的时间也只有一万多行。

当您编写SQL来填充此表时,您可以使用所有面向日期的内置函数来简化工作。当你进行查询时,你可以使用日期列作为连接条件,或者你可能需要两个时间戳来提供一个范围,以便在该范围内捕获时间戳。其余部分与处理任何其他类型的数据一样简单。

jmmr alextansc 15 年前

我正在为报告目的寻找类似的解决方案,偶然发现了这篇名为 Group by Month (and other time periods) 。它显示了按日期时间字段分组的各种方法,有好有坏。绝对值得一看。

Frederik Gheysels 17 年前

我认为你应该对它进行基准测试以获得可靠的结果,但是,依我之见,我的第一个想法是让数据库来处理它(你的第二种方法)会比在客户端代码中处理它快得多。使用第一种方法,您需要多次往返DB,我认为这将要贵得多。 :)

Cade Roux 17 年前

您可能想考虑一种维度方法(这与Walter Mitty的建议类似),其中每一行都有一个日期和/或时间维度的外键。这允许通过与此表的连接进行非常灵活的求和,其中这些部分是预先计算的。在这些情况下,密钥通常是YYYYMMDD和HHMMSS形式的自然整数密钥,其性能相对较高,也是人类可读的。

另一种选择可能是索引视图,其中每个日期部分都有单独的表达式。

或计算列。

但必须测试性能并检查执行计划。..