Início / dba / Perguntas / 230890
Accepted
WadeH
Asked: 2019-02-28 10:35:15 +0800 CST

Slow While Loop, Assistência de Melhoria de Consulta

  • 772

Estou trabalhando na criação de um Datawarehouse. Criei uma Dimensão de Tempo (Dim_Time), em intervalos de 5 minutos. As agregações de hora terão [Minutes] = NULL. Para efeito deste exemplo:

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

Então eu tenho uma tabela de entrada, que é atualizada a cada 5 minutos do banco de dados 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

Eu então tenho o seguinte loop While. Seu objetivo é obter o intervalo de tempo de 5 minutos correto (TimeID) relacionado a uma linha de entrada específica:

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

É um processo tão simples e, no entanto, não consigo descobrir uma maneira mais simples de atualizar o TimeID. De acordo com o CTE SELECT no loop, preciso obter o MAX(TimeID) onde o StartDateTime é menor que as linhas InsertTime. Como o tempo é a única relação, estou lutando com todas as opções para fazer isso em 1 consulta sem o loop, mas sinto que é possível

Por favor, alguém pode me ajudar aqui com uma opção melhor ou confirmando que esta é a maneira mais simples.

Muito obrigado pelo seu tempo e ajuda. Wade

sql-server t-sql

1 respostas

  1. Best Answer

    Criei o seguinte exemplo minimamente completo e verificável , com base nas duas tabelas da sua pergunta original. Ele usa a instrução LEAD T-SQL para obter um intervalo de tempo da tabela dbo.Dim_Time, que pode ser comparado com as linhas de entrada com bastante facilidade.

    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'));

    Esta peça substitui todo o seu WHILEloop, por uma única UPDATEinstrução, que é mais eficiente e mais fácil de entender.

    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;

    Os resultados, comparados lado a lado com a tabela Dim_Time:

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

    A saída se parece com:

    ╔════════════╦════════════╦════════╦══════════════ ═══════════╦════════╦═══════════════╗
║ 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 ║
╚════════════╩════════════╩════════╩══════════════ ═══════════╩════════╩═══════════════╝

    Supondo que não haja uma grande quantidade de linhas de entrada, isso pode funcionar muito bem. Esteja ciente, estou usando CONVERT()para converter a datetimecoluna de entrada em um time(0)valor, o que tem um custo do otimizador de consulta não poder usar as estatísticas disponíveis para ajudar a criar um ótimo plano. O plano de consulta "real" para a instrução de inserção mostra este aviso:

    A conversão de tipo na expressão (CONVERT(time(0),[iq].[InsertTime],0)>=[dt].[StartDateTime]) pode afetar "SeekPlan" na escolha do plano de consulta, a conversão de tipo na expressão (CONVERT(time (0),[iq].[InsertTime],0)<[Expr1002]) pode afetar "SeekPlan" na escolha do plano de consulta.

    Se você precisar evitar a conversão de tipo durante a atualização, poderá mover essa carga de trabalho para a operação de inserção atualizando a definição de dbo.Stg_IncomingQueuepara incluir uma coluna computada persistente, como em:

    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

    A instrução de atualização então se torna:

    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

relate perguntas