我所处的情境如下。
我有一个相当大的表格,需要查询其中最新的记录。以下是针对查询必要列的创建语句:
CREATE TABLE [dbo].[ChannelValue](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[UpdateRecord] [bit] NOT NULL,
[VehicleID] [int] NOT NULL,
[UnitID] [int] NOT NULL,
[RecordInsert] [datetime] NOT NULL,
[TimeStamp] [datetime] NOT NULL
) ON [PRIMARY]
GO
ID列是一个主键,而VehicleID和TimeStamp上有一个非聚集索引。
CREATE NONCLUSTERED INDEX [IX_ChannelValue_TimeStamp_VehicleID] ON [dbo].[ChannelValue]
(
[TimeStamp] ASC,
[VehicleID] ASC
)ON [PRIMARY]
GO
我正在优化的表格有超过2300万行,并且只有查询需要操作的大小的十分之一。
我需要返回每个VehicleID的最新行。
我已经在StackOverflow上查看了此问题的回答,并进行了大量的谷歌搜索。似乎在SQL Server 2005及以上版本中有3或4种常见方法可以实现这一点。
到目前为止,我找到的最快方法是以下查询:
SELECT cv.*
FROM ChannelValue cv
WHERE cv.TimeStamp = (
SELECT
MAX(TimeStamp)
FROM ChannelValue
WHERE ChannelValue.VehicleID = cv.VehicleID
)
目前表中的数据量大约需要6秒执行,这在合理范围内,但是在生产环境中,表将包含更多数据,查询开始变得太慢。
通过查看执行计划,我担心的是SQL Server为返回行所做的工作。
我不能发布执行计划图像,因为我的声望还不够高,但索引扫描正在解析表中的每一行,从而使查询变得非常缓慢。
我尝试过使用多种不同的方法重写查询,包括使用SQL 2005分区方法,如下所示:
WITH cte
AS (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY VehicleID ORDER BY TimeStamp DESC) AS seq
FROM ChannelValue
)
SELECT
VehicleID,
TimeStamp,
Col1
FROM cte
WHERE seq = 1
但是那个查询的性能甚至比较糟糕。
我已经尝试了像这样重新构造查询,但结果速度和查询执行计划几乎相同:
SELECT cv.*
FROM (
SELECT VehicleID
,MAX(TimeStamp) AS [TimeStamp]
FROM ChannelValue
GROUP BY VehicleID
) AS [q]
INNER JOIN ChannelValue cv
ON cv.VehicleID = q.VehicleID
AND cv.TimeStamp = q.TimeStamp
在表结构方面我有一些灵活性可用(虽然受到限制),因此我可以添加索引、索引视图等,甚至可以向数据库中添加其他表。
非常感谢任何帮助。
编辑:已添加执行计划图像链接。
create index
命令吗? - Aaron Bertrand