主页 / dba / 问题 / 230890
Accepted
WadeH
Asked: 2019-02-28 10:35:15 +0800 CST

慢速 While 循环，查询改进协助

  • 772

我正在创建一个数据仓库。我以 5 分钟的间隔创建了一个时间维度 (Dim_Time)。小时聚合将具有 [Minutes] = NULL。出于本示例的目的：

CREATE TABLE [dbo].[Dim_Time](
    [TimeID] [int] IDENTITY(1,1) NOT NULL,
    [StartDateTime] [datetime] NULL,
    [Hour] [int] NULL,
    [Minute] [int] NULL,
  CONSTRAINT [PK_Dim_Time] PRIMARY KEY CLUSTERED 
  ([TimeID] ASC)
  ) ON [PRIMARY]
GO

然后我有一个传入表，它每 5 分钟从 OLTP 数据库更新一次。

CREATE TABLE [dbo].[Stg_IncomingQueue](
    [IncomingID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerID] [int] NOT NULL,
    [TimeID] [int] NULL,
    [InsertTime] [datetime] NULL,
 CONSTRAINT [PK_IncomingQueueMonitor] PRIMARY KEY CLUSTERED 
([IncomingID] ASC)
) ON [PRIMARY]
GO

然后我有以下 While 循环。其目的是获取与特定传入行相关的正确 5 分钟时间段 (TimeID)：

WHILE 0 < (SELECT COUNT(*) FROM [dba_local].[dbo].[Stg_IncomingQueue] WHERE TimeID IS NULL)
BEGIN

    SELECT TOP 1 @IncomingID = IncomingID, @RowInserTime = InsertTime 
    FROM [dba_local].[dbo].[Stg_IncomingQueue] WHERE TimeID IS NULL


    ;WITH DimTime
    AS (
        SELECT MAX(TimeID) AS MaxTimeID FROM [dba_local].[dbo].[Dim_Time]
        WHERE StartDateTime < @RowInserTime AND [Minute] IS NOT NULL
    )
    UPDATE [dba_local].[dbo].[Stg_IncomingQueue]
    SET TimeID = (SELECT MaxTimeID FROM DimTime)
    WHERE IncomingID = @IncomingID

END

这是一个如此简单的过程，但我想不出更简单的方法来更新 TimeID。根据循环中的 CTE SELECT，我需要获取 MAX(TimeID)，其中 StartDateTime 小于行 InsertTime。因为时间是唯一的关系，所以我在没有循环的情况下在 1 个查询中执行此操作的所有选项都在努力，但我觉得这是可能的

请有人可以通过更好的选择或确认这是最简单的方法来帮助我。

非常感谢您的时间和帮助。韦德

sql-server t-sql

1 个回答

  1. Best Answer

    我根据原始问题中的两个表创建了以下最低限度的完整且可验证的示例。它使用LEAD T-SQL 语句从 dbo.Dim_Time 表中获取时间范围，可以很容易地将其与传入的行进行比较。

    IF OBJECT_ID(N'dbo.Stg_IncomingQueue', N'U') IS NOT NULL
DROP TABLE dbo.Stg_IncomingQueue;

IF OBJECT_ID(N'dbo.Dim_Time', N'U') IS NOT NULL
DROP TABLE dbo.Dim_time;

CREATE TABLE dbo.Dim_Time(
    TimeID int IDENTITY(1,1) NOT NULL,
    StartDateTime time(0) NULL,
  CONSTRAINT PK_Dim_Time PRIMARY KEY CLUSTERED 
  (TimeID ASC)
  ) ON [PRIMARY]
GO

;WITH src AS
(
    SELECT TOP (10) sv.number
    FROM master.dbo.spt_values sv
    WHERE sv.type = N'P'
    ORDER BY sv.number
)
INSERT INTO dbo.Dim_Time (StartDateTime)
SELECT TOP(289) CONVERT(time(0), DATEADD(minute, (s3.number * 100 + s2.number * 10 + s1.number) * 5, CONVERT(time(0), '00:00:00')))
FROM src s1
    CROSS JOIN src s2
    CROSS JOIN src s3
ORDER BY s3.number * 100 + s2.number * 10 + s1.number

CREATE TABLE dbo.Stg_IncomingQueue(
    IncomingID int IDENTITY(1,1) NOT NULL,
    CustomerID int NOT NULL,
    TimeID int NULL,
    InsertTime datetime NULL,
 CONSTRAINT PK_IncomingQueueMonitor PRIMARY KEY CLUSTERED 
(IncomingID ASC)
) ON [PRIMARY]
GO

INSERT INTO dbo.Stg_IncomingQueue (CustomerID, InsertTime)
VALUES (1, DATEADD(SECOND, CONVERT(int, CRYPT_GEN_RANDOM(4), 0), '1901-01-01 00:00:00'))
    , (2, DATEADD(SECOND, CONVERT(int, CRYPT_GEN_RANDOM(4), 0), '1901-01-01 00:00:00'))
    , (3, DATEADD(SECOND, CONVERT(int, CRYPT_GEN_RANDOM(4), 0), '1901-01-01 00:00:00'))
    , (4, DATEADD(SECOND, CONVERT(int, CRYPT_GEN_RANDOM(4), 0), '1901-01-01 00:00:00'));

    WHILE这篇文章用一条语句替换了整个循环，UPDATE这样效率更高，也更容易理解。

    UPDATE dbo.Stg_IncomingQueue
SET TimeID = t.TimeID
FROM dbo.Stg_IncomingQueue iq
    INNER JOIN (
        SELECT dt.TimeID
            , dt.StartDateTime
            , EndDateTime = LEAD(dt.StartDateTime, 1) OVER (ORDER BY dt.StartDateTime)
        FROM dbo.Dim_Time dt 
        ) t ON CONVERT(time(0), iq.InsertTime) >= t.StartDateTime AND CONVERT(time(0), iq.InsertTime) < t.EndDateTime;

    结果与 Dim_Time 表并排比较：

    SELECT *
FROM dbo.Stg_IncomingQueue iq
    INNER JOIN dbo.Dim_Time dt ON iq.TimeID = dt.TimeID;

    输出看起来像：

    ╔════════════╦════════════╦════════╦══════════════ lu
║ IncomingID ║ CustomerID ║ TimeID ║ InsertTime ║ TimeID ║ StartDateTime ║
╠════════════╬════════════╬════════╬══════════════ ═══════════╬════════╬═══════════════╣
║ 1 ║ 1 ║ 271 ║ 1875-06-30 22:31:49.000 ║ 271 ║ 22:30:00 ║
║ 2 ║ 2 ║ 116 ║ 1857-07-01 09:38:59.000 ║ 116 ║ 09:35:00 ║
║ 3 ║ 3 ║ 218 ║ 1854-09-18 18:08:39.000 ║ 218 ║ 18:05:00 ║
║ 4 ║ 4 ║ 221 ║ 1860-05-31 18:22:25.000 ║ 221 ║ 18:20:00 ║
╚════════════╩════════════╩════════╩══════════════ ═══════════╩════════╩═══════════════╝

    假设没有大量的传入行，这可能会工作得很好。请注意，我正在使用CONVERT()将传入datetime列转换为一个time(0)值，这是以查询优化器无法使用可用统计信息来帮助创建出色计划为代价的。插入语句的“实际”查询计划显示此警告：

    表达式中的类型转换 (CONVERT(time(0),[iq].[InsertTime],0)>=[dt].[StartDateTime]) 可能会影响查询计划选择中的“SeekPlan”，表达式中的类型转换 (CONVERT(time (0),[iq].[InsertTime],0)<[Expr1002]) 可能会影响查询计划选择中的“SeekPlan”。

    dbo.Stg_IncomingQueue如果您需要在更新期间避免类型转换，您可以通过更新 的定义以包含持久计算列，将工作负载移至插入操作，如下所示：

    CREATE TABLE dbo.Stg_IncomingQueue(
    IncomingID int IDENTITY(1,1) NOT NULL,
    CustomerID int NOT NULL,
    TimeID int NULL,
    InsertTime datetime NULL,
    InsertTime0 AS CONVERT(TIME(0), InsertTime) PERSISTED
 CONSTRAINT PK_IncomingQueueMonitor PRIMARY KEY CLUSTERED 
(IncomingID ASC)
) ON [PRIMARY]
GO

    更新语句变为：

    UPDATE dbo.Stg_IncomingQueue
SET TimeID = t.TimeID
FROM dbo.Stg_IncomingQueue iq
    INNER JOIN (
        SELECT dt.TimeID
            , dt.StartDateTime
            , EndDateTime = LEAD(dt.StartDateTime, 1) OVER (ORDER BY dt.StartDateTime)
        FROM dbo.Dim_Time dt 
        ) t ON iq.InsertTime0 >= t.StartDateTime AND iq.InsertTime0 < t.EndDateTime;
    • 7

相关问题