[SQL] SQL 쿼리 이전 행 최적화

SQL 쿼리 이전 행 최적화

여기 내 테이블 구조

MyTable
-----------

ObjectID int (Identity),           -- Primary Key
FileName varchar(10),
CreatedDate datetime
...........
...........
...........

나는 시간이 즉 파일 ...에 레코드를 생성 수행하는 데 필요한 ... 시간 같은 파일의 이전 기록과 같은 파일의 현재 레코드 사이에 경과

즉 ... 레코드가있는 경우

ObjectID    FileName    CreatedDate (just showing the time part here)
--------    --------    -----------
1           ABC         10:23
2           ABC         10:25
3           DEF         10:26
4           ABC         10:30
5           DEF         10:31
6           DEF         10:35

필요한 출력은 ...

ObjectID    FileName    CreatedDate     PrevRowCreatedDate
--------    --------    ----------- ---------------
1           ABC         10:23           NULL
2           ABC         10:25           10:23
3           DEF         10:26           NULL
4           ABC         10:30           10:25
5           DEF         10:31           10:26
6           DEF         10:35           10:31

지금까지 내가이 쿼리를 가지고 있지만, 예상보다 너무 많은 시간이 소요되고 ... 그것을 할 수있는 더 좋은 방법이 있나요 ...

    Select  A.ObjectID, 
        A.FileName
        A.CreatedDate as CreatedDate, 
        B.PrevRowCreatedDate,
        datediff("SS", '1900-01-01 00:00:00', Coalesce((A.CreatedDate - B.PrevRowCreatedDate),0)) as secondsTaken
    from MyTable as A 
        Cross Apply (       
        (Select PrevRowCreatedDate = Max(CreatedDate) from MyTable as BB 
                        where   BB.FileName = A.FileName and 
                                BB.CreatedDate < A.CreatedDate
        )
        ) as B  

나에게 더 많은 정보가 필요 넣다 알려주세요

감사

해결법

1. ==============================

   1.

   SELECT t1.FileName, t1.CreatedDate, t2.CreatedDate as PrevCreatedDate
   FROM 
      (SELECT FileName, CreateDate,
             ROW_NUMBER() OVER(PARTITION BY FileName ORDER BY CreatedDate) AS OrderNo
      FROM MyTable) t1
   LEFT JOIN
      (SELECT FileName, CreateDate,
        ROW_NUMBER() OVER(PARTITION BY FileName ORDER BY CreatedDate) AS OrderNo
        FROM MyTable) t2
   ON (t1.FileName = t2.FileName AND t1.OrderNo = t2.OrderNo - 1)
   

   쿼리가 동일하기 때문에 또는 'WITH'를보다 효율적으로 사용할 수 있습니다 :

   WITH t(ObjectID, FileName, CreatedDate, OrderNo) AS
      (SELECT ObjectID, FileName, CreatedDate,
             ROW_NUMBER() OVER(PARTITION BY FileName ORDER BY CreatedDate) AS OrderNo
      FROM MyTable) 
   SELECT t1.ObjectID, t1.FileName, t1.CreatedDate, t2.CreatedDate AS PrevCreatedDate,
           DATEDIFF("SS", '1900-01-01 00:00:00', 
              COALESCE((t1.CreatedDate - t2.CreatedDate),0)) AS secondsTaken
   FROM t t1 LEFT JOIN t t2 
   ON (t1.FileName = t2.FileName AND t1.OrderNo = t2.OrderNo + 1)
   

2. ==============================

   2.나는 마이클의 대답은 참으로보다 효율적으로 증명한다고 생각합니다. 난 그냥 Management Studio에서 표시되는 쿼리 비용 (배치에 비해)의 문제에 관심을 끌기 위해하고자하지만 효율성을 평가할 때.

   나는 마이클의 대답은 참으로보다 효율적으로 증명한다고 생각합니다. 난 그냥 Management Studio에서 표시되는 쿼리 비용 (배치에 비해)의 문제에 관심을 끌기 위해하고자하지만 효율성을 평가할 때.

   나는 23,174 행으로 테스트 테이블을 설정하고 질문 마이클의에서 쿼리를 실행했습니다. 그래서 원래 쿼리는 1 %였다 실제 실행 계획에서 "쿼리 비용 (배치에 상대적으로) '와 마이클의 99 % 비용으로보고하는 것은 대규모 비효율적 인 것으로 보인다.

   그러나 실제 통계는 완전히 다른 이야기를

   ROW_NUMBER 계획에서 병합 ROWNUMBER에 가입 = ROWNUMBER + 1 양쪽에가는 23,174 행이 있습니다. 뿐만 아니라 23174 체크 아웃이 값은 독특하고 실제 행을합니다. 그러나 SQL 서버는에서 생산 된 행이 34,812,000 될 것입니다 가입하여 삽입에 대한 예상 비용은 나중에 계획에 격렬하게 부정확 것으로 추정하고있다.

   BEGIN TRAN
   
   CREATE TABLE MyTable
     (
        [ObjectID]    [INT] IDENTITY(1, 1) NOT NULL PRIMARY KEY CLUSTERED,
        [FileName]    [VARCHAR](50) NULL,
        [CreatedDate] [DATETIME] NULL
     )
   
   GO
   
   INSERT INTO MyTable
   SELECT ISNULL(type, NEWID()),
          DATEADD(DAY, CAST(RAND(CAST(NEWID() AS VARBINARY)) * 10000 AS INT), GETDATE())
   FROM   master.dbo.spt_values,
          (SELECT TOP 10 1 AS X FROM  master.dbo.spt_values) V
   
   
   DELETE FROM MyTable
   WHERE  EXISTS(SELECT *
                 FROM   MyTable m2
                 WHERE  MyTable.CreatedDate = m2.CreatedDate
                        AND MyTable.FileName = m2.FileName
                        AND MyTable.ObjectID < m2.ObjectID)
   
   CREATE UNIQUE NONCLUSTERED INDEX [IX_MyTable]
     ON MyTable ([FileName] ASC, [CreatedDate] ASC)
   
   SET STATISTICS IO ON
   SET STATISTICS TIME ON
   
   SELECT A.ObjectID,
          A.FileName,
          A.CreatedDate                                                                                AS CreatedDate,
          B.PrevRowCreatedDate,
          DATEDIFF("SS", '1900-01-01 00:00:00', COALESCE(( A.CreatedDate - B.PrevRowCreatedDate ), 0)) AS secondsTaken
   INTO   #A
   FROM   MyTable AS A
          CROSS APPLY ((SELECT PrevRowCreatedDate = MAX(CreatedDate)
                        FROM   MyTable AS BB
                        WHERE  BB.FileName = A.FileName
                               AND BB.CreatedDate < A.CreatedDate)) AS B;
   
   WITH t(ObjectID, FileName, CreatedDate, OrderNo)
        AS (SELECT ObjectID,
                   FileName,
                   CreatedDate,
                   RANK() OVER(PARTITION BY FileName ORDER BY CreatedDate) AS OrderNo
            FROM   MyTable)
   SELECT t1.ObjectID,
          t1.FileName,
          t1.CreatedDate,
          t2.CreatedDate                                                                          AS PrevCreatedDate,
          DATEDIFF("SS", '1900-01-01 00:00:00', COALESCE(( t1.CreatedDate - t2.CreatedDate ), 0)) AS secondsTaken
   INTO   #B
   FROM   t t1
          LEFT JOIN t t2
            ON ( t1.FileName = t2.FileName
                 AND t1.OrderNo = t2.OrderNo + 1 )
   
   /*Test the 2 queries give the same result*/
   SELECT *
   FROM   #A
   EXCEPT
   SELECT *
   FROM   #B
   
   SELECT *
   FROM   #B
   EXCEPT
   SELECT *
   FROM   #A
   
   ROLLBACK 
   

from https://stackoverflow.com/questions/3424650/sql-query-pervious-row-optimisation by cc-by-sa and MIT license