基于测量子集的最小/最大日期的 SQL 性能

Question

我编写了一个 SQL 查询，它返回所有“测量组”以及在其中看到测量的第一个和最后一个日期。这些是从子“测量”表派生的，但是在这个特定的查询中，我只想使用最后 100 个测量值(这将成为用户可配置的)用于第一个/最后一个日期计算。我附加的查询返回了正确的结果，但是随着数据集的增长(测量表中有数百万行)，性能变得很糟糕，需要大约 1 分钟才能返回。

希望您明白我正在做的事情的要点，我有什么想法可以提高这个查询的性能吗？

查询

SELECT  mg.MeasurementGroupId,
        mg.[Name],
(
    SELECT  MIN(m1.MeasurementDateTime)
    FROM    dbo.Measurement m1
    WHERE   m1.MeasurementId IN
    (
        SELECT TOP 100 m2.MeasurementId
        FROM    dbo.Measurement m2
        WHERE   m2.MeasurementGroupId = mg.MeasurementGroupId
        ORDER
            BY  m2.MeasurementDateTime DESC
    )
) AS FirstMeasurement,
(
    SELECT  MAX(m1.MeasurementDateTime)
    FROM    dbo.Measurement m1
    WHERE   m1.MeasurementId IN
    (
        SELECT TOP 100 m2.MeasurementId
        FROM    dbo.Measurement m2
        WHERE   m2.MeasurementGroupId = mg.MeasurementGroupId
        ORDER
            BY  m2.MeasurementDateTime DESC
    )
) AS LastMeasurement
FROM    dbo.MeasurementGroup mg

DDL

CREATE TABLE [dbo].[MeasurementGroup](
    [MeasurementGroupId] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_MeasurementGroup] PRIMARY KEY CLUSTERED 
(
    [MeasurementGroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Measurement](
    [MeasurementId] [int] IDENTITY(1,1) NOT NULL,
    [MeasurementGroupId] [int] NOT NULL,
    [MeasurementDateTime] [datetime] NOT NULL,
    [Value1] [decimal](18, 0) NULL,
    [Value2] [decimal](18, 0) NULL,
    [Value3] [decimal](18, 0) NULL,
 CONSTRAINT [PK_Measurement] PRIMARY KEY CLUSTERED 
(
    [MeasurementId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Measurement]  WITH CHECK ADD  CONSTRAINT [FK_Measurement_MeasurementGroup] FOREIGN KEY([MeasurementGroupId])
REFERENCES [dbo].[MeasurementGroup] ([MeasurementGroupId])
GO

ALTER TABLE [dbo].[Measurement] CHECK CONSTRAINT [FK_Measurement_MeasurementGroup]
GO

DML

WITH
  L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), -- 2^1
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),       -- 2^2
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),       -- 2^4
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS k FROM L2)

INSERT INTO [dbo].[MeasurementGroup] ([Name])
SELECT  'Measurement ' + CAST(k AS VARCHAR)
FROM    Nums
WHERE   k <= 10

WITH
  L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), -- 2^1
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),       -- 2^2
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),       -- 2^4
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),       -- 2^8
  L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),       -- 2^16
  L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),       -- 2^32
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS k FROM L5)

INSERT INTO [dbo].[Measurement] ([MeasurementGroupId], [MeasurementDateTime], [Value1], [Value2], [Value3])
SELECT  mg.MeasurementGroupId, DATEADD(MINUTE, k, GETDATE()), 13.3, NULL, NULL
FROM    Nums n
CROSS JOIN  dbo.MeasurementGroup mg
WHERE   k < 1000000

生成的执行计划:https://www.brentozar.com/pastetheplan/?id=r1zs_JIWH

Answer 1

尝试使用apply。至少，您只需要获取 100 个测量行一次，而不是两次:

select mg.*, m.*
from dbo.MeasurementGroup mg cross apply
     (select min(MeasurementDateTime) as firstMeasurement,
             max(MeasurementDateTime) as lastMeasurement
      from (select top (100) m.*
            from measurement m
            where m.MeasurementGroupId = mg.MeasurementGroupId
            order by m.MeasurementDateTime desc
           ) m
     ) m;

这应该能够利用 measurement(MeasurementGroupId, MeasurementDateTime desc) 上的索引。