SQL Server :时序数据性能

Question

我有一个表，其中包含超过10亿行的时间序列数据，插入性能非常出色，但(有时) select性能很糟糕。

表tblTrendDetails (主键顺序如图)：

PK  TrendTime    datetime
PK  CavityId     int
PK  TrendValueId int
    TrendValue   real

该表不断地拉入新数据并清除旧数据，因此插入和删除性能需要保持快速。

执行如下查询时，性能较差(30秒)：

SELECT * 
FROM tblTrendDetails
WHERE TrendTime BETWEEN @inMinTime AND @inMaxTime
  AND CavityId = @inCavityId
  AND TrendValueId = @inTrendId

如果我再次执行相同的查询(使用相似的时间，但使用任何@inCavityId或@inTrendId)，性能非常好(1秒)。性能计数器显示，首次运行查询时，磁盘访问是罪魁祸首。

关于如何在不(显著)影响插入或删除性能的情况下提高性能，有什么建议吗？欢迎任何建议(包括完全改变底层数据库)。

Answer 1

相同或相似数据的后续查询运行速度更快的事实可能是由于SQL Server caching your data。也就是说，有没有可能提高这个初始查询的速度？

验证查询计划：

我的猜测是，您的查询应该导致索引查找，而不是索引扫描(或者更糟，表扫描)。请使用SET SHOWPLAN_TEXT ON;或类似功能进行验证。使用between和=作为您的查询应该是真正的take advantage of the clustered index，尽管是that's debatable。

索引碎片：

在所有这些插入和删除操作之后，您的聚集索引(本例中的主键)可能非常零碎。我可能会和DBCC SHOWCONTIG (tblTrendDetails)确认一下。

您可以使用DBCC INDEXDEFRAG (MyDatabase, tblTrendDetails)对表的索引进行碎片整理。这可能需要一些时间，但将允许表保持可访问性，并且您可以停止操作，而不会有任何令人讨厌的副作用。

您可能需要更进一步，并使用DBCC DBREINDEX (tblTrendDetails)。不过，这是一个离线操作，因此您应该仅在不需要访问表时执行此操作。

这里描述了一些区别：Microsoft SQL Server 2000 Index Defragmentation Best Practices。

请注意，通过对大型表进行碎片整理，事务日志可能会增长很多，并且可能需要很长时间。

分区视图：

如果这些都不能解决这种情况(或者碎片不是问题)，您甚至可以考虑使用partitioned views，它可以为不同范围的记录创建一组底层基表，然后将它们合并到一个视图中(替换原始表)。

更好的东西：

如果这些selects的性能是真正的业务需求，那么您可能需要更好的硬件:更快的驱动器、更多的内存等。如果您的驱动器的速度是原来的两倍，那么这个查询的运行时间就会减半，对吧？此外，这对您来说可能行不通，但我只是发现SQL Server的更新版本确实更快，具有更多的选项和更好的维护。我很高兴将我公司的大部分数据转移到了2008R2。但我离题了..。