SQL 按年、月、周、日、小时分组 SQL 与程序性能 [英] SQL Group By Year, Month, Week, Day, Hour SQL vs Procedural Performance

问题描述

我需要编写一个查询，该查询将按从年到小时的时间段对大量记录进行分组.

I need to write a query that will group a large number of records by periods of time from Year to Hour.

我最初的方法是在 C# 中以程序方式确定周期，遍历每个周期并运行 SQL 以获取该周期的数据，同时构建数据集.

My initial approach has been to decide the periods procedurally in C#, iterate through each and run the SQL to get the data for that period, building up the dataset as I go.

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

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

I've subsequently discovered I can group the records using Year(), Month() Day(), and datepart(week, date) and datepart(hh, date).

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

我担心的是，在 group by 中使用 datepart 会导致性能比在设定的时间段内多次运行查询更差，因为无法有效地使用 datetime 字段上的索引；关于这是否属实有什么想法吗?

My concern is that using datepart in a group by will lead to worse performance than running the query multiple times for a set period of time due to not being able to use the index on the datetime field as efficiently; any thoughts as to whether this is true?

谢谢.

推荐答案

与任何与性能相关的衡量

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

Checking the query plan up for the second approach will tell you any obvious problems in advance (a full table scan when you know one is not needed) but there is no substitute for measuring. In SQL performance testing that measurement should be done with appropriate sizes of test data.

由于这是一个复杂的案例，您不是简单地比较执行单个查询的两种不同方法，而是将单个查询方法与迭代方法进行比较，您的环境的各个方面可能在实际性能中发挥重要作用.

Since this is a complex case, you are not simply comparing two different ways to do a single query but comparing a single query approach against a iterative one, aspects of your environment may play a major role in the actual performance.

特别

1. 与大型查询方法相比，您的应用程序和数据库之间的距离"，因为每次调用的延迟都会浪费时间
2. 您是否使用准备好的语句(导致每次查询时数据库引擎的额外解析工作)
3. 范围查询本身的构建是否成本高(受 2 的影响很大)

这篇关于SQL 按年、月、周、日、小时分组 SQL 与程序性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！