Início / dba / Perguntas / 196362
Accepted
Razvan Zoitanu
Asked: 2018-01-27 10:51:05 +0800 CST

Conselhos sobre script T-SQL, mude para usar SQL dinâmico

  • 772

O script abaixo consultará a tabela de resultados da solução de manutenção da Ola (CommandLog) e apresentará uma comparação da duração bi-semanal do exec de reindexação, nas últimas 4 semanas.

Do jeito que está agora, é um truque rápido para me ajudar a ajustar as janelas de manutenção. Mas eu gostaria de remover a codificação de data, para que eu não precise adicionar manualmente todo fim de semana futuro como um novo JOIN.

Por favor, forneça uma reescrita completa (SQL dinâmico?) ou apenas algumas dicas sobre como conseguir isso ou alguns outros recursos úteis a serem incluídos. Ok para alterar o sproc e adicionar saída extra à tabela. Usando o SQL 2016. Se já houver um script por aí que atenda ao propósito, será um prazer usar esse.

WITH t0 AS 
(
SELECT ObjectName, IndexName, IndexType
FROM Tools.dbo.CommandLog
WHERE 1=1
AND DatabaseName = 'testdb'
AND CommandType = 'ALTER_INDEX'
GROUP BY ObjectName, IndexName, IndexType
)

SELECT 
 t0.ObjectName
,t0.IndexName
,t0.IndexType
,DATEDIFF(ss,t1.StartTime,t1.EndTime) as '20-40 01-06'
,DATEDIFF(ss,t2.StartTime,t2.EndTime) as '5-40 01-07'
,DATEDIFF(ss,t3.StartTime,t3.EndTime) as '20-40 01-13'
,DATEDIFF(ss,t4.StartTime,t4.EndTime) as '5-40 01-14'
,DATEDIFF(ss,t5.StartTime,t5.EndTime) as '20-40 01-20'
,DATEDIFF(ss,t6.StartTime,t6.EndTime) as '5-40 01-21'
,DATEDIFF(ss,t7.StartTime,t7.EndTime) as '20-40 01-27'
,DATEDIFF(ss,t8.StartTime,t8.EndTime) as '5-40 01-28'
FROM t0
LEFT JOIN Tools.dbo.CommandLog as t1 ON t0.ObjectName = t1.ObjectName AND t0.IndexName = t1.IndexName AND t1.StartTime BETWEEN '2018-01-06 00:30:00' AND '2018-01-06 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t2 ON t0.ObjectName = t2.ObjectName AND t0.IndexName = t2.IndexName AND t2.StartTime BETWEEN '2018-01-07 00:30:00' AND '2018-01-07 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t3 ON t0.ObjectName = t3.ObjectName AND t0.IndexName = t3.IndexName AND t3.StartTime BETWEEN '2018-01-13 00:30:00' AND '2018-01-13 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t4 ON t0.ObjectName = t4.ObjectName AND t0.IndexName = t4.IndexName AND t4.StartTime BETWEEN '2018-01-14 00:30:00' AND '2018-01-14 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t5 ON t0.ObjectName = t5.ObjectName AND t0.IndexName = t5.IndexName AND t5.StartTime BETWEEN '2018-01-20 00:30:00' AND '2018-01-20 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t6 ON t0.ObjectName = t6.ObjectName AND t0.IndexName = t6.IndexName AND t6.StartTime BETWEEN '2018-01-21 00:30:00' AND '2018-01-21 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t7 ON t0.ObjectName = t7.ObjectName AND t0.IndexName = t7.IndexName AND t7.StartTime BETWEEN '2018-01-27 00:30:00' AND '2018-01-27 23:59:59'
LEFT JOIN Tools.dbo.CommandLog as t8 ON t0.ObjectName = t8.ObjectName AND t0.IndexName = t8.IndexName AND t8.StartTime BETWEEN '2018-01-28 00:30:00' AND '2018-01-28 23:59:59'
WHERE 1=1
ORDER BY 
 t0.ObjectName
,t0.IndexName
,t0.IndexType
t-sql sql-server-2016

1 respostas

  1. Best Answer

    Tabela de calendário

    Como LowlyDBA e Anti-weakpasswords apontaram em seus comentários, você pode encontrar mais de um método para gerar uma tabela de calendário:

    Por causa da pergunta vou gerar uma tabela de calendário (básica) apenas para mostrar como usá-la com seus dados, você encontrará exemplos on-line que usam campos mais detalhados (segundos, dia do ano, trimestres, etc)

    CREATE TABLE Calendar(cDate datetime, cDay int, cDayOfWeek int, cDayName varchar(20));

DECLARE @date date = '20180101';
WHILE @date <= '20180131'
BEGIN
    INSERT INTO Calendar VALUES (@date, 
                                 DAY(@date), 
                                 DATEPART(weekday, @date), 
                                 DATENAME(weekday, @date));

    SET @date = DATEADD(day, 1, @date);
END

    De acordo com sua consulta, parece que você está interessado em obter resultados apenas para sábado e domingo. Nesse caso, você deve filtrar Calendara tabela por day nameou por day of week. Dê uma olhada em SET DATEFIRST se você quiser filtrar por day of week.

    SELECT * FROM Calendar WHERE cDayName IN ('Saturday', 'Sunday');
GO

    cData | cDia | cDiaDaSemana | cDayName
:------------------ | ---: | ---------: | :-------
01/06/2018 00:00:00 | 6 | 7 | Sábado
01/07/2018 00:00:00 | 7 | 1 | Domingo  
13/01/2018 00:00:00 | 13 | 7 | Sábado
14/01/2018 00:00:00 | 14 | 1 | Domingo  
20/01/2018 00:00:00 | 20 | 7 | Sábado
21/01/2018 00:00:00 | 21 | 1 | Domingo  
27/01/2018 00:00:00 | 27 | 7 | Sábado
28/01/2018 00:00:00 | 28 | 1 | Domingo

    Preenchendo a tabela CommandLog com dados aleatórios

    Agora deixe-me simular uma tabela CommandLog e preenchê-la com 1.000 datas aleatórias entre Jan-01 e Jan-31.

    CREATE TABLE CommandLog 
(
    ObjectName varchar(10), 
    IndexName varchar(10), 
    IndexType int, 
    StartTime datetime,
    EndTime datetime
);

DECLARE @step int = 1;
DECLARE @startDate datetime;

WHILE @step <= 1000
BEGIN
    SET @startDate = DATEADD(minute, RAND() * 59, 
                             DATEADD(hour, RAND() * 23, 
                                     DATEADD(day, RAND() * 31, '20180101')))

    INSERT INTO CommandLog VALUES('OBJ1', 
                                  'INDEX1', 
                                  1, 
                                  @startDate, 
                                  DATEADD(second, RAND() * 59,@startDate));
    SET @step += 1;
END

/* just to check first 10 records */
SELECT TOP 10 * FROM CommandLog;
GO

    ObjectName | IndexName | Tipo de índice | Hora de início | Fim do tempo            
:------------- | :-------- | --------: | :------------------ | :------------------
OBJ1 | ÍNDICE1 | 1 | 16/01/2018 04:36:00 | 16/01/2018 04:36:43
OBJ1 | ÍNDICE1 | 1 | 01/06/2018 09:11:00 | 01/06/2018 09:11:33
OBJ1 | ÍNDICE1 | 1 | 01/05/2018 14:23:00 | 01/05/2018 14:23:43
OBJ1 | ÍNDICE1 | 1 | 01/10/2018 19:53:00 | 01/10/2018 19:53:11
OBJ1 | ÍNDICE1 | 1 | 14/01/2018 14:31:00 | 14/01/2018 14:31:53
OBJ1 | ÍNDICE1 | 1 | 01/06/2018 18:43:00 | 01/06/2018 18:43:07
OBJ1 | ÍNDICE1 | 1 | 21/01/2018 21:52:00 | 21/01/2018 21:52:41
OBJ1 | ÍNDICE1 | 1 | 28/01/2018 06:51:00 | 28/01/2018 06:51:03
OBJ1 | ÍNDICE1 | 1 | 19/01/2018 08:39:00 | 19/01/2018 08:39:58
OBJ1 | ÍNDICE1 | 1 | 30/01/2018 19:57:00 | 30/01/2018 19:57:50

    Formatando dados

    Ok, até aí tudo bem, eu escolhi uma solução PIVOT neste caso, mas primeiro preciso formatar os dados de origem para que isso seja possível. Os registros podem ser filtrados ou agrupados usando a Calendartabela.

    ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
AND        cl.StartTime < DATEADD(day, 1, c.cDate)

    /* check first 10 records again */
SELECT     TOP 10 
           cl.ObjectName, cl.IndexName, cl.IndexType, cDayName,
           CASE cDayName 
                WHEN 'Saturday' THEN QUOTENAME('20-40 ' + FORMAT(cDate, 'dd-MM'))
                WHEN 'Sunday'   THEN QUOTENAME('5-40 ' + FORMAT(cDate, 'dd-MM'))
           END FormatDate,
           DATEDIFF(ss, cl.StartTime, cl.EndTime) DiffSeconds
FROM       CommandLog cl
INNER JOIN Calendar c
ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
AND        cl.StartTime < DATEADD(day, 1, c.cDate)
WHERE      cDayName IN ('Saturday', 'Sunday')
GO

    ObjectName | IndexName | Tipo de índice | cDayName | FormatoData | DiffSeconds
:------------- | :-------- | --------: | :------- | :------------ | ----------:
OBJ1 | ÍNDICE1 | 1 | Sábado | [20-40 06-01] | 33
OBJ1 | ÍNDICE1 | 1 | Sábado | [20-40 06-01] | 7
OBJ1 | ÍNDICE1 | 1 | Sábado | [20-40 06-01] | 25
OBJ1 | ÍNDICE1 | 1 | Sábado | [20-40 06-01] | 40
OBJ1 | ÍNDICE1 | 1 | Sábado | [20-40 06-01] | 0
OBJ1 | ÍNDICE1 | 1 | Sábado | [20-40 06-01] | 33
OBJ1 | ÍNDICE1 | 1 | Sábado | [20-40 06-01] | 13
OBJ1 | ÍNDICE1 | 1 | Sábado | [20-40 06-01] | 52
OBJ1 | ÍNDICE1 | 1 | Sábado | [20-40 06-01] | 50
OBJ1 | ÍNDICE1 | 1 | Sábado | [20-40 06-01] | 15

    Consulta PIVOT

    Bem, esta é a consulta PIVOT que estou tentando simular. Lembre-se de que as consultas PIVOT exigem um número bem conhecido de colunas e geralmente você deve gerá-lo dinamicamente.

    SELECT ObjectName, IndexName, IndexType,
      [20-40 06-01],[5-40 07-01],[20-40 13-01],[5-40 14-01],
      [20-40 20-01],[5-40 21-01],[20-40 27-01],[5-40 28-01]
FROM
(
    SELECT     cl.ObjectName, cl.IndexName, cl.IndexType,
               CASE cDayName WHEN 'Saturday' THEN '20-40 ' + FORMAT(cDate, 'dd-MM')
                             WHEN 'Sunday'   THEN '5-40 ' + FORMAT(cDate, 'dd-MM')
                             END FormatDate,
               DATEDIFF(ss, cl.StartTime, cl.EndTime) DiffSeconds
    FROM       CommandLog cl
    INNER JOIN Calendar c
    ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
    AND        cl.StartTime < DATEADD(day, 1, c.cDate)
    WHERE      cDayName IN ('Saturday', 'Sunday')
)src
PIVOT 
(
    SUM(DiffSeconds) FOR FormatDate IN ([20-40 06-01],[5-40 07-01],[20-40 13-01],
                                        [5-40 14-01],[20-40 20-01],[5-40 21-01],
                                        [20-40 27-01],[5-40 28-01])
) pvt;
GO

    ObjectName | IndexName | Tipo de índice | 20-40 06-01 | 5-40 07-01 | 20-40 13-01 | 5-40 14-01 | 20-40 20-01 | 5-40 21-01 | 20-40 27-01 | 5-40 28-01
:------------- | :-------- | --------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------:
OBJ1 | ÍNDICE1 | 1 | 1196 | 1480 | 747 | 1010 | 1197 | 1031 | 901 | 605

    Gerando consulta dinâmica

    Você pode usar a função STUFF para gerar nomes de colunas dinamicamente. E então simplesmente concatene as strings.

    DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT ',' + 
                   CASE cDayName 
                     WHEN 'Saturday' THEN QUOTENAME('20-40 ' + FORMAT(cDate, 'dd-MM'))
                     WHEN 'Sunday'   THEN QUOTENAME('5-40 ' + FORMAT(cDate, 'dd-MM'))
                   END
                   FROM Calendar
                   WHERE cDayName IN ('Saturday', 'Sunday')
                   ORDER BY cDate
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @cmd nvarchar(max);

SET @cmd =     
    'SELECT ObjectName, IndexName, IndexType, ' + @cols
 + ' FROM
     (
       SELECT     cl.ObjectName, cl.IndexName, cl.IndexType,
                  CASE cDayName 
                       WHEN ''Saturday'' THEN ''20-40 '' + FORMAT(cDate, ''dd-MM'')
                       WHEN ''Sunday''   THEN ''5-40 '' + FORMAT(cDate, ''dd-MM'')
                  END FormatDate,
                  DATEDIFF(ss, cl.StartTime, cl.EndTime) DiffSeconds
       FROM       CommandLog cl
       INNER JOIN Calendar c
       ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
       AND        cl.StartTime < DATEADD(day, 1, c.cDate)
       WHERE      cDayName IN (''Saturday'', ''Sunday'')
     )src
    PIVOT 
    (
      SUM(DiffSeconds) FOR FormatDate IN (' + @cols + ')'
 + ') pvt';

EXEC sp_executesql @cmd;
GO

    Resultado final

    ObjectName | IndexName | Tipo de índice | 20-40 06-01 | 5-40 07-01 | 20-40 13-01 | 5-40 14-01 | 20-40 20-01 | 5-40 21-01 | 20-40 27-01 | 5-40 28-01
:------------- | :-------- | --------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------:
OBJ1 | ÍNDICE1 | 1 | 1196 | 1480 | 747 | 1010 | 1197 | 1031 | 901 | 605

    Agrupar por solução

    Ok, é sábado à tarde e não tenho mais nada para fazer do que ler um livro interessante. Dando uma olhada em sua consulta, acho que você poderia substituir todas essas junções de tabela por uma única consulta GROUP BY + SUM(CASE...Calendar tabela.

    Semelhante a este:

    SELECT     cl.ObjectName, cl.IndexName, cl.IndexType,
           SUM(CASE WHEN cDay = 6 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 06-01],
           SUM(CASE WHEN cDay = 7 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 07-01],
           SUM(CASE WHEN cDay = 13 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 13-01],
           SUM(CASE WHEN cDay = 14 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 14-01],
           SUM(CASE WHEN cDay = 20 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 20-01],
           SUM(CASE WHEN cDay = 21 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 21-01],
           SUM(CASE WHEN cDay = 27 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [20-40 27-01],
           SUM(CASE WHEN cDay = 28 THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) ELSE 0 END) AS [5-40 28-01]
FROM       CommandLog cl
INNER JOIN Calendar c
ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
AND        cl.StartTime < DATEADD(day, 1, c.cDate)
WHERE      cDayName IN ('Saturday', 'Sunday')
GROUP BY   cl.ObjectName, cl.IndexName, cl.IndexType;
GO

    ObjectName | IndexName | Tipo de índice | 20-40 06-01 | 5-40 07-01 | 20-40 13-01 | 5-40 14-01 | 20-40 20-01 | 5-40 21-01 | 20-40 27-01 | 5-40 28-01
:------------- | :-------- | --------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------: | ----------: | ---------:
OBJ1 | ÍNDICE1 | 1 | 1196 | 1480 | 747 | 1010 | 1197 | 1031 | 901 | 605

    Esta consulta também pode ser gerada dinamicamente:

    DECLARE @cols nvarchar(max);

SET @cols = STUFF((SELECT ', ' + ('SUM(CASE WHEN cDay = ' 
                                 + FORMAT(cDay, 'D')
                                 + ' THEN DATEDIFF(ss, cl.StartTime, cl.EndTime) END) AS '
                                 + CASE cDayName 
                                        WHEN 'Saturday' THEN QUOTENAME('20-40 ' + FORMAT(cDate, 'dd-MM'))
                                        WHEN 'Sunday'   THEN QUOTENAME('5-40 ' + FORMAT(cDate, 'dd-MM'))
                                   END
                                 + ' ')
                   FROM Calendar
                   WHERE cDayName IN ('Saturday', 'Sunday')
                   ORDER BY cDate
                   FOR XML PATH(''), TYPE
                  ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @cmd nvarchar(max);

SET @cmd =   'SELECT     cl.ObjectName, cl.IndexName, cl.IndexType, '
           + @cols
           + 'FROM       CommandLog cl
              INNER JOIN Calendar c
              ON         cl.StartTime >= DATEADD(minute, 30, c.cDate)
              AND        cl.StartTime < DATEADD(day, 1, c.cDate)
              WHERE      cDayName IN (''Saturday'', ''Sunday'')
              GROUP BY   cl.ObjectName, cl.IndexName, cl.IndexType';

/* REMOVE Warning: Null value is eliminated by an aggregate or other SET operation. */
SET ANSI_WARNINGS OFF;

EXEC sp_executesql @cmd;
GO

    dbfiddle aqui

    • 9

relate perguntas