代码之家 › 专栏 › 技术社区 › Andrew Rimmer

获取存档表中当前记录的最佳查询是什么(SQL Server 2005/2008)

sql-server-2008 tsql performance sql-server

Andrew Rimmer · 技术社区 · 16 年前

有一个应用程序可以测量世界上每个城镇的温度。每5分钟进行一次测量并写入测量表。

CREATE TABLE [dbo].[Measurement](
    [MeasurementID] [int] IDENTITY(1,1) NOT NULL,
    [Town] [varchar](50) NOT NULL,
    [Date] [datetime] NOT NULL,
    [Temp] [int] NOT NULL,
CONSTRAINT [PK_Measurement] PRIMARY KEY CLUSTERED 
(
    [MeasurementID] ASC
)) ON [PRIMARY]

问题

假设有10万个城镇和1000万个记录

注:我已经添加了一些可能的答案,但可能还有其他选择。

6 回复 | 直到 16 年前

dkretz 16 年前

以下是一些应该有效的方法:

选择
m1.城镇,m1.温度
从
测量为m1
左连接
以m2计
打开

和m1.日期<m2.日期
哪里
m2.MeasurementID为空

你需要一份关于城镇和日期的索引。

这种技术对于早期版本的MySQL尤其有用,因为它无法处理更明显的

选择城镇,临时
以m1计

从测量中选择1
其中Town=m1.城镇
和日期>m1.日期

按城镇排序

Andrew Rimmer 16 年前

select *
from
(
    select distinct *, --Keyword,Total,CreatedOn,EngineInstanceID,
    Rank() over (PARTITION by Town order by Date DESC) as DateOrder
    from Measurement
    where Town is not null
) CurrentMeasurement
where DateOrder = 1

Cade Roux 16 年前

很高兴看到这么多方法来剥这只猫的皮。下面是一个使用CTE的例子(您也可以嵌套查询以获得更多的ANSI ism,但我发现CTE很好地避免了很多缩进,而且前面声明的东西使它在下面上下都非常可读):

WITH LastMeasurements AS (
    SELECT [Town], MAX([Date]) AS LastMeasurementDate
    FROM [Measurement]
    GROUP BY [Town]
)
SELECT [Measurement].Town, [Measurement].[Date], [Measurement].Temp
FROM [Measurement]
INNER JOIN LastMeasurements
    ON [Measurement].[Town] = LastMeasurements.[Town]
    AND [Measurement].[Date] = LastMeasurements.LastMeasurementDate

我喜欢显式回溯技术的一点是,它可以让您轻松地访问为组选择的最上面一行中的所有信息,并且在更改组时非常灵活,而且不需要重复自己的操作。

优化器倾向于在SQL Server上非常快速地执行这些操作—就像大多数解决方案一样,如果您有一个关于城镇、日期、临时的索引,那么它将覆盖并运行得非常快。即使只是在镇上约会 GROUP BY 无论如何都可以做得很快。

Andrew Rimmer 16 年前

select s.*
from Measurement s
where exists ( 
   select 1
   from Measurement s1
   where s.Town = s1.Town
   group by s1.Town
   having max( s1.Date )= s.Date)
   order by s.Town

Gordon Bell 16 年前

select m.town, m.temperature, m.date
from Measurement m
where m.date = (select max(m2.date) from Measurement m2 where m2.town = m.town)
order by 1

momo 13 年前

你可能有一张列有不同城镇的桌子吗?假设每个城镇有大约1000个测量值,窗口函数解决方案(如row_number()、rank()等)的性能可能不如普通聚合或此应用版本:

SELECT
   M.*
FROM
   Towns T
   OUTER APPLY (
      SELECT TOP 1 * -- add 'WITH TIES' to the 'TOP 1' if you have/want ties on date.
      FROM Measurement M
      WHERE T.Town = M.Town
      ORDER BY M.Date DESC
   ) M

如果没有城镇列表,您可以试试这个,尽管我不知道它与普通的聚合+查找相比会有什么不同:

SELECT
   M.*
FROM
   (SELECT DISTINCT Town FROM Towns) T
   OUTER APPLY (
      SELECT TOP 1 *
      FROM Measurement M
      WHERE T.Town = M.Town
      ORDER BY M.Date DESC
   ) M

这些查询的性能完全取决于索引。你需要一个在[城镇]的最低限度和[城镇,日期]而不是最好的。如果其他表使用MeasurementID,但您很少使用MeasurementID访问MeasurementID表,则删除聚集索引,将MeasurementID设为非聚集PK,并在Town、Date上添加(非均匀)聚集索引。如果没有使用MeasurementID的其他表,那么完全删除该列——在这种情况下,它是一个无用的合成/人工键,无缘无故地使表膨胀。

索引中的这些建议更改将有助于使用聚合或应用此处答案中的所有查询。不确定它们对窗口函数的影响,这取决于优化器如何制定执行计划(如果它足够聪明,能够意识到它只需要访问最大日期,而不接触所有其他行,那么同一个索引将极大地提高它的性能,尽管我怀疑优化器能否做到这一点)。

另外,为了提高性能,我建议一定要有一个镇上的桌子,而不是把整个镇的地方。如果镇名变了怎么办?将每个名称的平均字节数从15个左右切换到一个int TownID的平均字节数只有4个,这将有助于提高速度。(尽管测试是为了证明这一点)。