在包含350万行且还在增长的SQL Server表上提高查询性能

本文关键字：高查询 性能 查询 SQL 350万 包含 Server | 更新日期: 2023-09-27 18:05:42

我用c#编写了一个应用程序，该应用程序连接到sql server数据库express edition，从前端我每隔几秒钟填充数据库中的特定表，并在该表中插入大约200~300行。

当前表包含大约350万行，并且还在不断增长，表定义如下

[DEVICE_ID] [decimal](19, 5) NULL,
[METER_ID] [decimal](19, 5)  NULL,
[DATE_TIME] [decimal](19, 5)  NULL,
[COL1] [decimal](19, 5)  NULL,
[COL2] [decimal](19, 5)  NULL,
.
.
.
.
[COL25] [decimal](19, 5) NULL

我已经在Date_Time列上创建了非聚集索引，并且要注意，如果需要的话，没有唯一的列存在，我可以为此创建标识列(自动增量)，但是我的报告生成逻辑完全基于Date_Time列。

我通常基于时间触发查询，例如，如果我需要计算在月份期间col1中发生的变化。我将需要Col1的值在第1天的第一个值和最后一个月的最后一天的值，同样，我需要为灵活的日期触发查询，我通常只需要基于Date_Time列的打开值和关闭值为任何选择的列。

获取第一天col1的第一个值，查询为

select top (1) COL1 from VALUEDATA where DeviceId=@DId and MeterId =@MId and Date_Time between @StartDateTime and @EndDateTime order by Date_Time

要获取最后一天col1的最后值，查询为

select top (1) COL1 from VALUEDATA where DeviceId=@DId and MeterId =@MId and Date_Time between @StartDateTime and @EndDateTime order by Date_Time desc

但是当我启动上面的查询时，它需要大约20~30秒，我相信这可以进一步优化，但不知道前面的路。

我给出的一个想法是创建另一个表，每天插入第一行和最后一行，并从中获取数据。但我将避免相同的，如果我可以做一些事情在现有的表和查询。

如果有人能提供相同的输入，那将是非常值得赞赏的。

在包含350万行且还在增长的SQL Server表上提高查询性能

要充分优化这些查询，您需要两个不同的多重索引:

CREATE INDEX ix_valuedata_asc ON VALUEDATA (DeviceId, MeterId, Date_Time);
CREATE INDEX ix_valuedata_des ON VALUEDATA (DeviceId, MeterId, Date_Time DESC);

我有另一个建议:如果您的目标是在进行索引查找后获得COL1, COL2等的值，那么在过滤列上仅使用非聚集索引的解决方案仍然必须连接回主表，即;做一个书签/RID查找

你的信息给我的印象是你的基表没有聚集(没有聚集索引);实际上是一个堆表

如果表上的大多数查询都遵循您描述的模式，那么我会将此表设置为集群。与大多数人的想法相反，您不必将聚集索引定义为(唯一的)主键。如果你在SQL server中为非唯一数据定义了一个聚集索引，SQL server将通过添加一个不可见的行标识符使它成为唯一的'under water'

如果该表上最常用的USED选择/过滤条件是日期时间，我会将表更改为以下集群结构:

    首先，删除所有非聚集的索引
  1. 然后添加以下集群索引:

CREATE CLUSTERED INDEX clix_valuedata ON VALUEDATA (Date_Time, DeviceId, MeterId);

当使用遵循模式的查询时，如果您查看查询解释计划，您(可能!)将获得非常高性能的聚集索引SEEK样式访问表。现在您将免费获得表中的所有其他列，因为不再需要进行书签查找。随着表的增长，这种方法可能会更好地扩展;

相关文章：