sql - 连接海量 CTE 表(13,000,000 行以上)性能问题

Question

我们有一个生产数据库，可以提前数年管理 100 多个分支机构的人员预订，精度达到分钟级。

该系统的一部分是突出显示差距的报告，即比较分行营业时间和员工预订情况，以查看是否有任何分行营业但没有人预订。

它还会同时检查重叠、重复预订等，基本上需要分钟级的精度。

我们这样做的方法是使用整数计数表将开放时间和预订的开始和结束时间扩展为分钟:

--===== Create and populate the Tally table on the fly
 SELECT TOP 16777216
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2,
        Master.dbo.SysColumns sc3

--===== Add a Primary Key to maximize performance
  ALTER TABLE dbo.Tally
    ADD CONSTRAINT PK_Tally_N 
        PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

我们利用这个静态索引理货表来扩展开放时间和预订，如下所示:

SELECT   [BranchID] ,
        [DayOfWeek] ,
        DATEADD(MINUTE, N - 1, StartTime)
FROM     OpeningHours
        LEFT OUTER JOIN tally ON tally.N BETWEEN 0
                                         AND     DATEDIFF(MINUTE, OpeningHours.StartTime, OpeningHours.EndTime) + 1

问题是，一旦我们有了 13,000,000 分钟的“开放分钟数”和“预订分钟数”，我们就需要加入结果以查看涵盖的内容:

SELECT   OpenDatesAndMinutes.[Date] ,
                                OpenDatesAndMinutes.[Time] ,
                                OpenDatesAndMinutes.[BranchID] ,
                                ISNULL(BookedMinutes.BookingCount, 0) AS BookingCount
                       FROM     OpenDatesAndMinutes
                                LEFT OUTER JOIN BookedMinutes ON OpenDatesAndMinutes.BranchID = BookedMinutes.BranchID
                                                                 AND OpenDatesAndMinutes.[Date] = BookedMinutes.[Date]
                                                                 AND OpenDatesAndMinutes.[Time] = BookedMinutes.[Time]

正如您所想象的那样，加入存储在 CTE 表中的 13,000,000 行的分支、日期和时间需要 AGES - 运行一周并不算太糟糕，大约 10 秒，但如果我们运行 6 个月(13,000,000 分钟) ) 膨胀到 25 分钟+

一旦我们将开放分钟数加入预订分钟数，我们就会将数据分组并呈现给用户:

CrossTabPrep ( [Date], [Time], [BranchID], [BookingCount], [Grp] )
  AS ( SELECT   [Date] ,
                [Time] ,
                [BranchID] ,
                [BookingCount] ,
                DATEPART(HOUR, Time) * 60 + DATEPART(MINUTE, Time) - ROW_NUMBER() OVER ( PARTITION BY [BranchID], Date, [BookingCount] ORDER BY Time ) AS [Grp]
       FROM     PreRender
     ),
FinalRender ( [BranchID], [Date], [Start Time], [End Time], [Duration], [EntryCount], [EntryColour] )
  AS ( SELECT   [BranchID] ,
                [Date] ,
                MIN([Time]) AS [Start Time] ,
                MAX([Time]) AS [End Time] ,
                ISNULL(DATEDIFF(MINUTE, MIN([Time]), MAX([Time])), 0) AS Duration ,
                [BookingCount] AS EntryCount ,
                CASE WHEN [BookingCount] = 0 THEN 'Red'
                     WHEN [BookingCount] = 1 THEN 'Green'
                     ELSE 'Yellow'
                END AS EntryColour
       FROM     CrossTabPrep
       GROUP BY [BranchID] ,
                [Date] ,
                [BookingCount] ,
                [Grp]
     )

很简单，我的方法是否有效？有什么办法可以改进这种方法，同时保持分钟级的准确性？在处理像这样的大量 CTE 表时，将这些数据转储到索引临时表并加入它们会有什么好处吗？

我正在考虑的另一件事是替换大连接使用的 DATE & TIME(0) 数据类型，如果我将它们转换为整数会更有效吗？

以下是完整的 CTE，以防有帮助:

WITH    OpeningHours ( [BranchID], [DayOfWeek], [StartTime], [EndTime] )
          AS ( SELECT   BranchID ,
                        DayOfWeek ,
                        CONVERT(TIME(0), AM_open) ,
                        CONVERT(TIME(0), AM_close)
               FROM     db_BranchDetails.dbo.tbl_ShopOpeningTimes (NOLOCK)
                        INNER JOIN @tbl_Days Filter_Days ON db_BranchDetails.dbo.tbl_ShopOpeningTimes.DayOfWeek = Filter_Days.DayNumber
               WHERE    CONVERT(TIME(0), AM_open) <> CONVERT(TIME(0), '00:00:00')
               UNION ALL
               SELECT   BranchID ,
                        DayOfWeek ,
                        CONVERT(TIME(0), PM_open) ,
                        CONVERT(TIME(0), PM_close)
               FROM     db_BranchDetails.dbo.tbl_ShopOpeningTimes (NOLOCK)
                        INNER JOIN @tbl_Days Filter_Days ON db_BranchDetails.dbo.tbl_ShopOpeningTimes.DayOfWeek = Filter_Days.DayNumber
               WHERE    CONVERT(TIME(0), PM_open) <> CONVERT(TIME(0), '00:00:00')
               UNION ALL
               SELECT   BranchID ,
                        DayOfWeek ,
                        CONVERT(TIME(0), EVE_open) ,
                        CONVERT(TIME(0), EVE_close)
               FROM     db_BranchDetails.dbo.tbl_ShopOpeningTimes (NOLOCK)
                        INNER JOIN @tbl_Days Filter_Days ON db_BranchDetails.dbo.tbl_ShopOpeningTimes.DayOfWeek = Filter_Days.DayNumber
               WHERE    CONVERT(TIME(0), EVE_open) <> CONVERT(TIME(0), '00:00:00')
             ),
        DateRange ( [Date], [DayOfWeek] )
          AS ( SELECT   CONVERT(DATE, DATEADD(DAY, N - 1, @StartDate)) ,
                        DATEPART(WEEKDAY, DATEADD(DAY, N - 1, @StartDate))
               FROM     tally (NOLOCK)
               WHERE    N <= DATEDIFF(DAY, @StartDate, @EndDate) + 1
             ),
        OpenMinutes ( [BranchID], [DayOfWeek], [Time] )
          AS ( SELECT   [BranchID] ,
                        [DayOfWeek] ,
                        DATEADD(MINUTE, N - 1, StartTime)
               FROM     OpeningHours
                        LEFT OUTER JOIN tally ON tally.N BETWEEN 0
                                                         AND     DATEDIFF(MINUTE, OpeningHours.StartTime, OpeningHours.EndTime) + 1
             ),
        OpenDatesAndMinutes ( [Date], [Time], [BranchID] )
          AS ( SELECT   DateRange.[Date] ,
                        OpenMinutes.[Time] ,
                        OpenMinutes.BranchID
               FROM     DateRange
                        LEFT OUTER JOIN OpenMinutes ON DateRange.DayOfWeek = OpenMinutes.DayOfWeek
               WHERE    OpenMinutes.BranchID IS NOT NULL
             ),
        WhiteListEmployees ( [DET_NUMBERA] )
          AS ( SELECT   DET_NUMBERA
               FROM     [dbo].[tbl_ChrisCache_WhiteList]
               WHERE    [TimeSheetV2_SecurityContext] = @TimeSheetV2_SecurityContext
             ),
        BookedMinutesByRole ( [Date], [Time], [BranchID], BookingCount )
          AS ( SELECT   [BookingDate] ,
                        DATEADD(MINUTE, N - 1, StartTime) ,
                        BranchID ,
                        COUNT(BookingID) AS Bookings
               FROM     tbl_Booking (NOLOCK)
                        INNER JOIN tbl_BookingReason  (NOLOCK) ON dbo.tbl_BookingReason.ReasonID = dbo.tbl_Booking.ReasonID
                        INNER JOIN tbl_ChrisCache  (NOLOCK) ON dbo.tbl_Booking.DET_NUMBERA = dbo.tbl_ChrisCache.DET_NUMBERA
                        INNER JOIN @ValidPosCodes AS Filter_PostCodes ON dbo.tbl_ChrisCache.POS_NUMBERA = Filter_PostCodes.POSCODE
                        LEFT OUTER JOIN tally (NOLOCK) ON tally.N BETWEEN 0
                                                                  AND     DATEDIFF(MINUTE, tbl_Booking.StartTime, tbl_Booking.EndTime) + 1
               WHERE    ( Void = 0 )
                        AND tbl_BookingReason.CoverRequired = 0 --#### Only use bookings that dont require cover
                        AND tbl_booking.BranchID <> '023'   --#### Branch 23 will always have messy data
                        AND ( dbo.tbl_Booking.BookingDate BETWEEN @StartDate
                                                          AND     @EndDate )
               GROUP BY [BookingDate] ,
                        BranchID ,
                        DATEADD(MINUTE, N - 1, StartTime)
             ),
        BookedMinutesByWhiteList ( [Date], [Time], [BranchID], BookingCount )
          AS ( SELECT   [BookingDate] ,
                        DATEADD(MINUTE, N - 1, StartTime) ,
                        BranchID ,
                        COUNT(BookingID) AS Bookings
               FROM     tbl_Booking(NOLOCK)
                        INNER JOIN tbl_BookingReason (NOLOCK) ON dbo.tbl_BookingReason.ReasonID = dbo.tbl_Booking.ReasonID
                        INNER JOIN tbl_ChrisCache (NOLOCK) ON dbo.tbl_Booking.DET_NUMBERA = dbo.tbl_ChrisCache.DET_NUMBERA
                        INNER JOIN WhiteListEmployees Filter_WhiteList ON dbo.tbl_Booking.DET_NUMBERA = Filter_WhiteList.DET_NUMBERA
                        LEFT OUTER JOIN tally (NOLOCK) ON tally.N BETWEEN 0
                                                                  AND     DATEDIFF(MINUTE, tbl_Booking.StartTime, tbl_Booking.EndTime) + 1
               WHERE    ( Void = 0 )
                        AND tbl_BookingReason.CoverRequired = 0 --#### Only use bookings that dont require cover
                        AND tbl_booking.BranchID <> '023'   --#### Branch 23 will always have messy data
                        AND ( dbo.tbl_Booking.BookingDate BETWEEN @StartDate
                                                          AND     @EndDate )
               GROUP BY [BookingDate] ,
                        BranchID ,
                        DATEADD(MINUTE, N - 1, StartTime)
             ),
        BookedMinutes ( [Date], [Time], [BranchID], BookingCount )
          AS ( SELECT   [Date] ,
                        [Time] ,
                        [BranchID] ,
                        BookingCount
               FROM     BookedMinutesByRole
               UNION
               SELECT   [Date] ,
                        [Time] ,
                        [BranchID] ,
                        BookingCount
               FROM     BookedMinutesByWhiteList
             ),
        PreRender ( [Date], [Time], [BranchID], [BookingCount] )
          AS ( SELECT   OpenDatesAndMinutes.[Date] ,
                        OpenDatesAndMinutes.[Time] ,
                        OpenDatesAndMinutes.[BranchID] ,
                        ISNULL(BookedMinutes.BookingCount, 0) AS BookingCount
               FROM     OpenDatesAndMinutes
                        LEFT OUTER JOIN BookedMinutes ON OpenDatesAndMinutes.BranchID = BookedMinutes.BranchID
                                                         AND OpenDatesAndMinutes.[Date] = BookedMinutes.[Date]
                                                         AND OpenDatesAndMinutes.[Time] = BookedMinutes.[Time]
             ),
        CrossTabPrep ( [Date], [Time], [BranchID], [BookingCount], [Grp] )
          AS ( SELECT   [Date] ,
                        [Time] ,
                        [BranchID] ,
                        [BookingCount] ,
                        DATEPART(HOUR, Time) * 60 + DATEPART(MINUTE, Time) - ROW_NUMBER() OVER ( PARTITION BY [BranchID], Date, [BookingCount] ORDER BY Time ) AS [Grp]
               FROM     PreRender
             ),
        DeletedBranches ( [BranchID] )
          AS ( SELECT   [ShopNo]
               FROM     [dbo].[vw_BranchList]
               WHERE    [Branch_Deleted] = 1
             ),
        FinalRender ( [BranchID], [Date], [Start Time], [End Time], [Duration], [EntryCount], [EntryColour] )
          AS ( SELECT   [BranchID] ,
                        [Date] ,
                        MIN([Time]) AS [Start Time] ,
                        MAX([Time]) AS [End Time] ,
                        ISNULL(DATEDIFF(MINUTE, MIN([Time]), MAX([Time])), 0) AS Duration ,
                        --dbo.format_timeV2(ISNULL(DATEDIFF(SECOND, MIN([Time]), MAX([Time])), 0)) AS DurationF ,
                        [BookingCount] AS EntryCount ,
                        CASE WHEN [BookingCount] = 0 THEN 'Red'
                             WHEN [BookingCount] = 1 THEN 'Green'
                             ELSE 'Yellow'
                        END AS EntryColour
               FROM     CrossTabPrep
               GROUP BY [BranchID] ,
                        [Date] ,
                        [BookingCount] ,
                        [Grp]
             )
            SELECT  [BranchID] ,
                    CONVERT(VARCHAR(10), DATEADD(DAY, 7, CONVERT(DATETIME, CONVERT(VARCHAR(10), DATEADD(day, -1 - ( DATEPART(dw, [Date]) + @@DATEFIRST - 2 ) % 7, [Date]), 103) + ' 23:59:59', 103)), 103) AS WeekEnding ,
                    [Date] ,
                    [Start Time] ,
                    [End Time] ,
                    [Duration] ,
                    CONVERT(VARCHAR, ( [Duration] * 60 ) / 3600) + 'h ' + CONVERT(VARCHAR, ROUND(( ( CONVERT(FLOAT, ( ( [Duration] * 60 ) % 3600 )) ) / 3600 ) * 60, 0)) + 'm' AS [DurationF] ,
                    [EntryCount] ,
                    [EntryColour] ,
                    CASE WHEN [EntryCount] = 0 THEN 'Red'
                         WHEN [EntryCount] >= 1 THEN 'Green'
                    END AS DurationColour ,
                    CASE WHEN [EntryCount] = 0 THEN 'This period of open-time isnt covered'
                         WHEN [EntryCount] >= 1 THEN 'This period of open-time is covered by ' + CONVERT(VARCHAR, [EntryCount]) + ' booking(s)'
                    END AS [DurationComment]
            FROM    FinalRender
            WHERE   FinalRender.BranchID NOT IN ( SELECT    [BranchID]
                                                  FROM      DeletedBranches )

Answer 1

这很有趣，因为你在最后用你的问题回答了你自己的问题。您应该尝试所有方法，但总结一下:

1. 实现 CTE 以获得更好的性能。您永远不知道 SQL Server 何时会多次评估 CTE
2. 您可以针对临时表构建 indexex。
3. 我不确定您是如何从 [DayOfWeek],DATEADD(MINUTE, N - 1, StartTime) 跳到加入 [Date],[Time] 另一方面，但这里有两列没有意义。使用单个 datetime 或代表一个纪元的 seconds 的 bigint。 UnixTimestamp 在这里工作得很好。