Sql-Server

使用游標改進查詢到基於 SET 的方法

  • January 23, 2016

我還是查詢優化的新手，我有一個儲存過程，它使用游標遍歷表中的每一行，並執行以下操作：

  1. 計算每行之間的時間差
  2. 計算每行之間的距離
  3. 如果距離 < 5 AND TimeDifference > 3 分鐘，則添加到臨時表

我嘗試將此游標轉換為 WHILE 循環，但性能下降。所以我需要幫助將其轉換為一種SET BASED方法而不是一種Procedural Based方法

所以游標執行這個邏輯：

-- READ Current Row into Cursor Variables
FETCH NEXT FROM crAssetIgnitionOnOff INTO
             @current_iVehicleMonitoringID
           , @current_iAssetID
           , @current_dtUTCDateTime
           , @current_sptGeoLocationPoint
           , @current_fLatitude
           , @current_fLongitude
           , @current_fAngle
           , @current_fSpeedKPH 
           , @current_sIgnitionStatus
           , @current_eEventCode
           , @current_sEventCode

IF(@current_iAssetID = @prev_iAssetID)
   BEGIN

       ---- Calculate Time Difference from previous Point
   DECLARE @diffInSeconds INT
       SET @diffInSeconds = DATEDIFF(SECOND, @prev_dtUTCDateTime, @current_dtUTCDateTime)

   DECLARE @diffInMinutes INT
       SET @diffInMinutes = @diffInSeconds / 60

       -- Calcualte the Distance from previous position
       DECLARE @tempDistance FLOAT;
           SELECT @tempDistance = @current_sptGeoLocaitonPoint.STDistance(@prev_sptGeoLocaitonPoint);

       -- Check if distance travelled less than 5, AND Time difference between points greater than user selected Idle Minutes (@iIdleMinutes) AND prev ignition status = On
       IF(@diffInSeconds &gt; @iIdleMinutes AND @tempDistance &lt; 5 AND @prev_sIgnitionStatus = 'On')   
           BEGIN

               DECLARE @sTime VARCHAR(30)
                   SELECT @sTime = dbo.xPT_ConvertTimeToDDHHMMSS(@diffInSeconds,'s')

               INSERT INTO @tblExcessiveIdleTime(
                                   AssetID,
                                   PreviousDate,
                                   CurrentDate,
                                   TimeString,
                                   TimeInSeconds
                           )
                           VALUES
                           (
                               @current_iAssetId,
                               @prev_dtUTCDateTime,
                               @current_dtUTCDateTime,
                               @sTime,
                               @diffInSeconds
                           )

           END 
   END


   -- Set Previous Values End of Loop
   SET  @prev_iVehicleMonitoringID = @current_iVehicleMonitoringID
   SET  @prev_iAssetID = @current_iAssetID
   SET  @prev_dtUTCDateTime = @current_dtUTCDateTime
   SET  @prev_sptGeoLocationPoint = @current_sptGeoLocationPoint
   SET  @prev_fLatitude = @current_fLatitude
   SET  @prev_fLongitude = @current_fLongitude
   SET  @prev_fAngle = @current_fAngle
   SET  @prev_fSpeedKPH = @current_fSpeedKPH
   SET  @prev_sIgnitionStatus = @current_sIgnitionStatus
   SET  @prev_eEventCode = @current_eEventCode
   SET  @prev_sEventCode = @current_sEventCode
END

現在這在某些情況下需要 17 分鐘才能執行，所以我嘗試將其轉換為 WHILE 循環 - ( http://www.sqlbook.com/SQL/Avoiding-using-SQL-Cursors-20.aspx )

這不是一個好主意 - 因為邏輯讀取次數的性能是游標的 4 倍。而且處理時間更長：

WHILE @RowCount &lt;= @NumberRecords
   BEGIN

       -- Check for First Row
       IF @RowCount = 1
           BEGIN
               -- Set First Row as Previous
               SELECT @previous_iAssetID = iAssetID, @previous_sptGeoLocaitonPoint = sptGeoLocaitonPoint, @previous_dtUTCDateTime = dtUTCDateTime, @previous_sIgnitionStatus = sIgnitionStatus
               FROM #tblVehicleMonitoringLog WHERE RowID = @RowCount
           END
       ELSE
           BEGIN

               /* Select current Row */
               SELECT @current_iAssetID = iAssetID, @current_sptGeoLocaitonPoint = sptGeoLocaitonPoint, @current_dtUTCDateTime = dtUTCDateTime, @current_sIgnitionStatus = sIgnitionStatus
               FROM #tblVehicleMonitoringLog WHERE RowID = @RowCount


               /******** IMPLEMENT REPORT LOGIC **********/

               IF(@current_iAssetID = @previous_iAssetID)
                   BEGIN

                       ---- Calculate Time Difference from previous Point
                   DECLARE @diffInSeconds INT
                       SET @diffInSeconds = DATEDIFF(SECOND, @previous_dtUTCDateTime, @current_dtUTCDateTime)

                   DECLARE @diffInMinutes INT
                       SET @diffInMinutes = @diffInSeconds / 60

                       -- Calcualte the Distance from previous position
                       DECLARE @tempDistance FLOAT;
                           SELECT @tempDistance = @current_sptGeoLocaitonPoint.STDistance(@previous_sptGeoLocaitonPoint);

                       -- Check if distance travelled less than 5, AND Time difference between points greater than user selected Idle Minutes (@iIdleMinutes) AND prev ignition status = On
                       IF(@diffInSeconds &gt; @iIdleMinutes AND @tempDistance &lt; 5 AND @previous_sIgnitionStatus = 'On')   
                           BEGIN

                               DECLARE @sTime VARCHAR(30)
                                   SELECT @sTime = dbo.xPT_ConvertTimeToDDHHMMSS(@diffInSeconds,'s')

                               INSERT INTO @tblExcessiveIdleTime(
                                                   iAssetID,
                                                   dtIgnitionOn,
                                                   dtNextPeriodic,
                                                   sTime,
                                                   iTimeDurationInSeconds
                                           )
                                           VALUES
                                           (
                                               @current_iAssetId,
                                               DATEADD(hour, @fGmtOffSet, @previous_dtUTCDateTime),
                                               DATEADD(hour, @fGmtOffSet, @current_dtUTCDateTime),
                                               @sTime,
                                               @diffInSeconds
                                           )

                           END 
                   END


               -- Set Previous Values End of Loop
               SET @previous_iAssetID = @current_iAssetID;
               SET @previous_sptGeoLocaitonPoint = @current_sptGeoLocaitonPoint;
               SET @previous_dtUTCDateTime = @current_dtUTCDateTime;
               SET @previous_sIgnitionStatus = @current_sIgnitionStatus;

           END



       -- increment Row Number
       SET @RowCount = @RowCount + 1


   END -- END OF WHILE LOOP

因此，在再次線上查看後-我發現我們可以計算兩行之間的時間差。（https://stackoverflow.com/questions/2357515/calculate-time-difference-between-two-rows）

這就是原始數據的樣子。

這就是原始數據的樣子

我需要計算時差和每行之間的距離 WHERE

  1. 目前 iAssetID = 以前的 iAssetID
  2. 上一個 sDigitalInputValue = ‘10000000’

這是我提出的查詢：

WITH    rows AS
       (
           SELECT  *, ROW_NUMBER() OVER (ORDER BY dtUTCDateTime) AS rn
               FROM    VehicleMonitoringLog
               Where dtUTCDateTime &gt; GetDate() - 1
               --Order by iAssetId, dtUTCDateTime
       )


SELECT mc.iVehicleMonitoringId as CurrentID, mp.iVehicleMonitoringId as PreviousID, 
       mc.iAssetId as CurrentAsset, mp.iAssetId As PreviousAsset, mc.dtUTCDateTime as CurrentTime, mp.dtUTCDateTime as PreviousTime,
DATEDIFF(second, mc.dtUTCDateTime, mp.dtUTCDateTime) AS DateDiffSeconds
FROM    rows mc
JOIN    rows mp
ON      mc.rn = mp.rn - 1

編輯

我的查詢現在正在執行 - 如果您發現任何性能問題，請告訴我：

SELECT dt.CurrentAsset,
       dt.Distance,
       dt.DateDiffSeconds,
       dt.CurrentIgnition,
       dt.PreviousIgnition,
       ta.sReference,
       ta.sCategoryName,
       ta.sSiteName,
   dbo.xPT_ConvertTimeToDDHHMMSS(DateDiffSeconds,'s')
FROM (
   SELECT iVehicleMonitoringId AS CurrentID,
      LEAD(iVehicleMonitoringId, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS PreviousID, 
      iAssetId AS CurrentAsset,
      LEAD(iAssetId, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS PreviousAsset,
      sDigitalInputValue AS CurrentIgnition,
      LEAD(sDigitalInputValue, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS PreviousIgnition,
      dtUTCDateTime AS CurrentTime,
      LEAD(dtUTCDateTime, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime) AS PreviousTime,
      DATEDIFF(second, dtUTCDateTime, LEAD(dtUTCDateTime, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime)) AS DateDiffSeconds,
      sptGeoLocaitonPoint.STDistance(LEAD(sptGeoLocaitonPoint, 1) OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime)) AS Distance

       FROM VehicleMonitoringLog
       WHERE dtUTCDateTime &gt; @utcStartDate AND dtUTCDateTime &lt; @utcEndDate

) AS dt
Inner join #tblAssets ta on ta.iAssetID = dt.CurrentAsset
WHERE  CurrentIgnition = '10000000' AND Distance &lt; 5 AND DateDiffSeconds &gt; @iIdleMinutes

您使用視窗函式的基於 CTE 的方法是一個很好的開始。您可以使用另一個更合適的視窗函式：LAG()。

這是如何做：

SELECT iVehicleMonitoringId AS CurrentID,
      LAG(iVehicleMonitoringId, 1) OVER (ORDER BY dtUTCDateTime) AS PreviousID, 
      iAssetId AS CurrentAsset,
      LAG(iAssetId, 1) OVER (ORDER BY dtUTCDateTime) AS PreviousAsset,
      dtUTCDateTime AS CurrentTime,
      LAG(dtUTCDateTime, 1) OVER (ORDER BY dtUTCDateTime) AS PreviousTime,
      DATEDIFF(second,
          dtUTCDateTime,
          LAG(dtUTCDateTime, 1) OVER (ORDER BY dtUTCDateTime)
          ) AS DateDiffSeconds
FROM VehicleMonitoringLog
WHERE dtUTCDateTime &gt; DATEADD(day, -1, SYSDATETIME());

基本上，LAG(column, n) OVER (ORDER BY x)返回column, nrow(s) 的值（因此 n=1 返回前一行），按x.

您的 CTE 解決方案將掃描VehicleMonitoringLog兩次，然後加入兩個流。此查詢將僅執行一次掃描，這要高效得多。該LAG()函式（及其表親LEAD()）自 SQL Server 2012 起可用。

分區

看起來您忘記了某種類型的分區術語（iAssetID，也許？）。分區項用於分隔不同車輛之間的數據點，以防兩輛車同時出差。通過將 OVER() 子句從 更改為 ，將此分區項添加到查詢OVER (ORDER BY dtUTCDateTime)中OVER (PARTITION BY iAssetID ORDER BY dtUTCDateTime)。

索引

為了使該解決方案真正發揮作用，我將在以下位置創建以下索引VehicleMonitoringLog：

CREATE INDEX... (iAssetID, dtUTCDateTime);   --- if you're using PARTITION BY

.. 要麼

CREATE INDEX... (dtUTCDateTime);             --- without PARTITION BY

引用自：https://dba.stackexchange.com/questions/127083

相關問答

Sql-Server

“行目標”不同的行為 - 為什麼？

  • October 31, 2019
檢視問答
Sql-Server

尋求幫助優化自聯接

  • May 13, 2019
檢視問答
Sql-Server

謂詞的直接值產生不太好的計劃

  • April 9, 2019
檢視問答
Sql-Server

如何教優化器在數據記錄主/明細表上使用索引而不是 fts 連接？

  • August 9, 2018
檢視問答
Sql-Server

如何刪除兩個表連接以優化速度的鍵查找？

  • November 12, 2016
檢視問答
Sql-Server

有沒有辦法可以將以下查詢縮短為單個查詢？

  • September 9, 2015
檢視問答