sql - 具有时间间隔的 TSQL 领先

问题描述

我正在尝试提高此查询的性能：

    SELECT Ts.[TripID]
      ,Ts.[RDate]
      ,Ts.[RTime]
      ,Ts.[Passengers]
      ,Ts.[Other]
      ,Ts.[RecordStatusID]
      ,IIF(Ts.RecordStatusID = 1, '', CONVERT(VARCHAR(10),
                             (SELECT        SUM((IIF(Passengers + Other < 8, 1, CEILING((Passengers + Other) / 7.0))))
                               FROM            dbo.tblTrips T
                               WHERE        ISNULL(T.ActualDateTime, CAST(T.RDate AS SMALLDATETIME) + CAST(T.RTime AS SMALLDATETIME)) BETWEEN ISNULL(Ts.ActualDateTime, CAST(Ts.RDate AS SMALLDATETIME) + CAST(Ts.RTime AS SMALLDATETIME)) AND DATEADD(MINUTE, 35, 
                                                         ISNULL(Ts.ActualDateTime, CAST(Ts.RDate AS SMALLDATETIME) + CAST(Ts.RTime AS SMALLDATETIME))) AND T.RecordStatusID > 1)) + ' Trips') AS Trips
  FROM tblTrips Ts
  ORDER BY Ts.RDate, Ts.RTime

我真的不知道如何以任何其他方式做到这一点，任何帮助将不胜感激。

这里的目标是计算每行的 35 分钟间隔，显示从当前行程时间 (rdate + rtime) + 35 分钟有多少行程，我尝试进行分组，但结果不是我想要的。我想知道是否有时间间隔的线索我可以指定一个条件并使用一个计数？

标签： sqlsql-servertsqlsubquery

您可以尝试拆分操作。例如：

DROP TABLE IF EXISTS #tblTrips;
DROP TABLE IF EXISTS #tblTripsAgg;


CREATE TABLE #tblTrips
(
     [TripID] [int] NOT NULL 
    ,[Record] INT 
    ,[ActualDateTime] [datetime] 
    ,PRIMARY KEY ([TripID])
);

CREATE TABLE #tblTripsAgg
(
     [TripID] [int] NOT NULL PRIMARY KEY
    ,Trips INT
);

INSERT INTO #tblTrips ([TripID], [Record], [ActualDateTime])
SELECT TripID
      ,IIF(RecordStatusID = 1, 0, IIF(Passengers + Other < 8, 1, CEILING((Passengers + Other) / 7.0)))
      ,CAST(RDate AS SMALLDATETIME) + CAST(RTime AS SMALLDATETIME)
FROM tblTrips

INSERT INTO #tblTripsAgg
SELECT TS.[TripID]
        ,SUM(T.[Record])
FROM #tblTrips TS
LEFT JOIN #tblTrips T
    ON T.[ActualDateTime] >= TS.[ActualDateTime]
    AND T.[ActualDateTime] <= DATEADD(MINUTE, 35, TS.[ActualDateTime])
GROUP BY TS.[TripID]

SELECT A.[TripID]
      ,A.[RDate]
      ,A.[RTime]
      ,A.[Passengers]
      ,A.[Other]
      ,A.[RecordStatusID]
      ,IIF(A.RecordStatusID = 1, '', CONCAT(Trips, ' Trips')) AS Trips
FROM tblTrips A
INNER JOIN #tblTripsAgg DS
    ON A.TripID = ds.TripID;

为什么？首先是因为有时复杂的查询没有被 SQL 引擎正确优化，其次 - 你可以很容易地看到过程的哪一部分是最慢的。

例如，在上面的示例中，第一部分只是将我们的日期和时间放在一个日期中。此外，我们正在计算一些额外的东西。因此，如果查询的这一部分花费大量时间，只需预先计算它 - 使用触发器、使用计算预先计算的列或当应用程序接触表时。

然后在第二个查询中，我们计算每个的总和trip id。如果这部分很慢，也许我们可以在表上创建一个不同的索引，ActualDateTime或者如果当前行程 ID 的日期总是大于前一个，我们可以添加TS.TripID > T.TripID以进一步限制行？

正如您在评论中所说，处理了 300 000 行，但这确实是少量数据，所以它真的不应该花费这么多时间来执行。如果您可以进一步优化查询，请共享数据转储。

您可以创建一个索引视图来自动预先计算第一个查询的结果，因此不需要额外的工作。

DROP VIEW IF EXISTS  dbo.vw_tblTrips;
GO

CREATE VIEW dbo.vw_tblTrips WITH SCHEMABINDING
AS 
SELECT TripID AS [TripID]
      ,IIF(RecordStatusID = 1, 0, IIF(Passengers + Other < 8, 1, CEILING((Passengers + Other) / 7.0))) AS [Record]
      ,CAST(RDate AS SMALLDATETIME) + CAST(RTime AS SMALLDATETIME) AS [ActualDateTime]
FROM dbo.tblTrips;

GO

CREATE UNIQUE CLUSTERED INDEX INX_vw_tblTrips   
    ON dbo.vw_tblTrips ([ActualDateTime], [TripID]);  
GO

sql - 具有时间间隔的 TSQL 领先

问题描述

解决方案

推荐阅读