在SQL Server中为图表处理数百万行的最快方法

Question

我们每秒都会将实时数据记录到SQL Server数据库中，我们希望从1000万行或更多行中生成图表。目前，我们使用类似以下代码的东西。目标是获得至少1000-2000个值以传递到图表中。

在下面的查询中，我们根据从LargeTable中挑选的数据数量，对接下来的第n行取一个平均值。最多可以选择200000行，但速度太慢。

SELECT 
AVG(X),
AVG(Y)
FROM 
(SELECT 
X, Y,
(Id / @AvgCount) AS [Group]
FROM 
[LargeTable]
WHERE 
Timestmp > @From
AND Timestmp < @Till) j
GROUP BY
[Group]
ORDER BY 
X;

现在，我们试图从LargeTable中只选择第n行，然后对这些数据进行平均以获得更高的性能，但这几乎需要相同的时间。

SELECT 
X, Y
FROM 
(SELECT 
X, Y,
ROW_NUMBER() OVER (ORDER BY Id) AS rownr
FROM 
LargeTable
WHERE 
Timestmp >= @From
AND Timestmp <= @Till) a
WHERE 
a.rownr % (@count / 10000) = 0;

这只是伪代码！我们在所有相关列上都有索引。

有没有更好更快的方法来获取图表数据？

Answer 1

我认为有两种方法可以提高图表的性能：

1. 试图提高查询的性能
2. 减少需要读取的数据量

如果没有完整的DDL和执行计划，我几乎不可能提高查询的性能。所以我建议你减少要读取的数据量。

关键是在数据到来时以给定的粒度级别汇总组，并将其存储在一个单独的表中，如下所示：

CREATE TABLE SummarizedData
(
int GroupId PRIMARY KEY,
FromDate datetime,
ToDate datetime,
SumX float,
SumY float,
GroupCount 
)

IdGroup应等于Id/100或Id/1000，具体取决于您希望在组中使用多少粒度。对于较大的组，您可以获得更粗的粒度，但更有效的图表。

我假设LargeTableId列单调增加，所以您可以将已处理的最后一个Id存储在另一个名为SummaryProcessExecutions的表中

您将需要一个存储过程ExecuteSummaryProcess，它是：

1. 从SummaryProcessExecutions读取LastProcessedId
2. 读取大表上的最后一个Id并将其存储到@NewLastProcessedId变量中
3. 用Id > @LastProcessedId and Id <= @NewLastProcessedId汇总LargeTable中的所有行，并将结果存储到SummarizedData表中
4. 将@NewLastProcessedId变量存储到SummaryProcessExecutions表中

您可以在SQL Server代理作业中频繁执行ExecuteSummaryProcess存储过程。

我相信按日期分组会比按Id分组更好。这会简化事情。SummarizedDataGroupId列将与LargeTableId无关，并且不需要更新SummarizedData行，只需要插入行即可。

Answer 2

由于扫描表的时间随着表中行数的增加而增加，因此我认为Timetmp列上没有索引。像下面这样的索引可能会加快您的查询速度：

CREATE NONCLUSTERED INDEX [IDX_Timestmp] ON [LargeTable](Timestmp) INCLUDE(X, Y, Id)

请注意，创建这样的索引可能需要相当长的时间，它也会影响您的插入。