Início / dba / Perguntas / 51340
Accepted
Matthew
Asked: 2013-10-11 10:42:20 +0800 CST

Devo juntar datetime a uma data usando cast ou range?

  • 772

Esta pergunta é uma decolagem da excelente colocada aqui:

Cast to date é sargável, mas é uma boa ideia?

No meu caso, não estou preocupado com a WHEREcláusula e sim em ingressar em uma tabela de eventos que possui uma coluna do tipoDATE

Uma tabela tem DATETIME2e a outra tem DATE... para que eu possa efetivamente JOINusar uma CAST( AS DATE)ou posso usar uma consulta de intervalo "tradicional" (> = data E < data + 1).

Minha pergunta é qual é preferível? Os DATETIMEvalores quase nunca corresponderão ao valor do predicado DATE.

Espero ficar na ordem de 2 milhões de linhas com o DATETIMEe menos de 5k com o DATE(se essa consideração fizer diferença)

Devo esperar o mesmo comportamento no JOINque eu poderia usar a WHEREcláusula? Qual devo preferir para manter o desempenho com dimensionamento? A resposta muda com o MSSQL 2012?

Meu caso de uso generalizado é tratar minha tabela de eventos como uma tabela de calendário

SELECT
    events.columns
    ,SOME_AGGREGATIONS(tasks.column)
FROM
    events
LEFT OUTER JOIN
    tasks
        --This appropriately states my intent clearer
        ON CAST(tasks.datetimecolumn AS DATE) = events.datecolumn 
        --But is this more effective/scalable?
        --ON tasks.datetimecolumn >= events.datecolumn 
        --AND tasks.datetimecolumn < DATEADD(day,1,events.datecolumn)
GROUP BY
    events.columns
sql-server-2008-r2 sql-server-2012

2 respostas

  1. Best Answer

    "Depende".

    Uma vantagem do =predicado e castaté o momento é que a junção pode ser hash ou merge. A versão do intervalo forçará um plano de loops aninhados.

    Se não houver índices úteis a serem pesquisados datetimecolumn, tasksisso faria uma diferença substancial.

    Configurando os 5K/2 milhões de linhas de dados de teste mencionados na pergunta

    CREATE TABLE events
  (
     eventId    INT IDENTITY PRIMARY KEY,
     datecolumn DATE NOT NULL,
     details    CHAR(1000) DEFAULT 'D'
  )

INSERT INTO events
            (datecolumn)
SELECT TOP 5000 DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY @@SPID), GETDATE())
FROM   spt_values v1,
       spt_values v2

CREATE TABLE tasks
  (
     taskId         INT IDENTITY PRIMARY KEY,
     datetimecolumn DATETIME2 NOT NULL,
     details        CHAR(1000) DEFAULT 'D'
  );

WITH N
     AS (SELECT number
         FROM   spt_values
         WHERE  number BETWEEN 1 AND 40
                AND type = 'P')
INSERT INTO tasks
            (datetimecolumn)
SELECT DATEADD(MINUTE, number, CAST(datecolumn AS DATETIME2))
FROM   events,
       N

    Então ligando

    SET STATISTICS IO ON;
SET STATISTICS TIME ON;

    E tentando a CASTversão

    SELECT events.eventId,
       MAX(tasks.details)
FROM   events
       LEFT OUTER JOIN tasks
         ON CAST(tasks.datetimecolumn AS DATE) = events.datecolumn
GROUP  BY events.eventId

    Concluído em 7,4 segundos

    Table 'Worktable'. Scan count 0, logical reads 0
Table 'tasks'. Scan count 1, logical reads 28679
Table 'events'. Scan count 1, logical reads 719

   CPU time = 3042 ms,  elapsed time = 7434 ms.

    O número estimado de linhas saindo da junção e entrando no GROUP BYera muito pequeno (5.006,27 vs 2.000.000 reais) e o agregado de hash foi derramado paratempdb

    Plano 1

    Tentando o predicado de intervalo

    SELECT events.eventId,
       MAX(tasks.details)
FROM   events
       LEFT OUTER JOIN tasks
         ON tasks.datetimecolumn >= events.datecolumn
            AND tasks.datetimecolumn < DATEADD(day, 1, events.datecolumn)
GROUP  BY events.eventId

    A falta de um predicado de igualdade força um plano de loops aninhados. Como não há índices úteis para dar suporte a essa consulta, ele não tem opção a não ser verificar a tabela de 2 milhões de linhas 5.000 vezes.

    Na minha máquina que deu um plano paralelo que acabou completando depois de 1 minuto e 40 segundos.

    Table 'tasks'. Scan count 4, logical reads 143390000
Table 'events'. Scan count 5, logical reads 788
Table 'Worktable'. Scan count 0, logical reads 0
  CPU time = 368193 ms,  elapsed time = 100528 ms.

    Desta vez, o número de linhas saindo da junção e entrando no agregado foi grosseiramente superestimado ( em estimados 124.939.000 versus 2.000.000 reais)

    Plano 2

    Repetir o experimento após alterar as tabelas para tornar as respectivas colunas de data/hora a chave primária agrupada alterou os resultados.

    Ambas as consultas acabaram escolhendo um plano de loops aninhados. A versão CASTas DATEdeu uma versão serial que completou em 4,5 segundos e a versão range um plano paralelo que completou em tempo decorrido 1,1 segundos com tempo de CPU de 3,2 segundos.

    A aplicação MAXDOP 1à segunda consulta para tornar os números mais facilmente comparáveis ​​retorna o seguinte.

    Consulta 1

    Table 'Worktable'. Scan count 0, logical reads 0
Table 'tasks'. Scan count 5000, logical reads 78137
Table 'events'. Scan count 1, logical reads 719
   CPU time = 3167 ms,  elapsed time = 4497 ms.

    Consulta 2

    Table 'tasks'. Scan count 5000, logical reads 49440
Table 'events'. Scan count 1, logical reads 719
   CPU time = 3042 ms,  elapsed time = 3147 ms.

    A consulta 1 teve uma estimativa de 5.006,73 linhas saindo da junção e o agregado de hash foi derramado tempdbnovamente.

    A consulta 2 novamente tem uma grande superestimativa (em 120.927.000 desta vez).

    A outra diferença óbvia entre os dois resultados é que a consulta de intervalo parece conseguir buscar tasksde alguma forma com mais eficiência. Apenas lendo 49,440páginas 78,137vs.

    O intervalo que a versão de conversão de data procura é derivado de uma função interna GetRangeThroughConvert. O plano mostra um predicado residual em CONVERT(date,[dbo].[tasks].[datetimecolumn],0)= [dbo].[events].[datecolumn].

    Se a Consulta 2 for alterada para

       LEFT OUTER JOIN tasks
     ON tasks.datetimecolumn > DATEADD(day, -1, events.datecolumn)
        AND tasks.datetimecolumn < DATEADD(day, 1, events.datecolumn)

    Em seguida, o número de leituras torna-se o mesmo. A busca dinâmica usada pela CAST AS DATEversão lê linhas desnecessárias (dois dias em vez de um) e depois as descarta com o predicado residual.

    Uma outra possibilidade seria reestruturar a tabela para armazenar os componentes datee timeem colunas diferentes.

    CREATE TABLE [dbo].[tasks](
    [taskId] [int] IDENTITY(1,1) NOT NULL,
    [datecolumn] date NOT NULL,
    [timecolumn] time NOT NULL,
    [datetimecolumn]  AS DATEADD(day, DATEDIFF(DAY,0,[datecolumn]), CAST([timecolumn] AS DATETIME2(7))),
    [details] [char](1000) NULL,
PRIMARY KEY CLUSTERED 
(
    [datecolumn] ASC,
    [timecolumn] ASC
))

    O datetimecolumnpode ser derivado das partes componentes e isso não tem efeito no tamanho da linha (já que a largura de date+ time(n)é igual à largura de datetime2(n)). (Com exceção se a coluna adicional aumentar o tamanho do NULL_BITMAP)

    =A consulta é então um predicado direto

    SELECT events.eventId,
       MAX(tasks.details)
FROM   events
       LEFT OUTER JOIN tasks
         ON tasks.datecolumn = events.datecolumn
GROUP  BY events.eventId

    Isso permitiria uma junção de mesclagem entre as tabelas sem a necessidade de classificar. Embora para esses tamanhos de tabela, uma junção de loops aninhados tenha sido escolhida de qualquer maneira com as estatísticas abaixo.

    Table 'tasks'. Scan count 5000, logical reads 44285
Table 'events'. Scan count 1, logical reads 717
   CPU time = 2980 ms,  elapsed time = 3012 ms.

    Além de permitir potencialmente diferentes tipos de junção lógica armazenando dateseparadamente como a coluna de índice principal, também potencialmente beneficiaria outras consultas tasks, como agrupamento por data.

    Quanto ao motivo pelo qual o =predicado mostra menos leituras lógicas do tasksque a > <=versão com o mesmo plano de loops aninhados ( 44,285vs 49,440), isso parece estar relacionado ao mecanismo de leitura antecipada.

    Ativar o sinalizador de rastreamento 652reduz as leituras lógicas da versão de intervalo para a mesma da versão igual.

    • 6

  2. Concordo com Martin que as estimativas de cardinalidade podem sofrer com essa abordagem versus uma abordagem de intervalo de datas. Também acrescentarei que usar CONVERT(DATEe ainda obter sargability pode implicar para outras pessoas lendo código ou aprendendo com ele que é uma boa ideia em geral usar funções contra a coluna, principalmente quando a coluna é indexada. Uma vez que este é o únicoexceção onde isso funciona, e em todos os outros casos isso realmente força uma varredura quando uma busca pode ter sido possível, não acho que seja uma boa prática usar uma exceção que não tenha nenhum benefício real, exceto para o autor do código , e mesmo isso é de curta duração - você economiza alguns segundos escrevendo uma expressão mais concisa, e é algo que você faz uma vez. Enfrento a mesma oposição o tempo todo ao responder perguntas - vejo outras pessoas postando respostas que incluem maus hábitos, como declarar varcharsem comprimento, e muitas vezes comento. A desculpa que ouço de volta é que funciona bem nestecaso, mas esse não é o ponto - as pessoas aprendem com este caso e aplicam o que aprenderam a outros casos, onde pode não funcionar tão bem. E pode até quebrar no mesmo caso - por exemplo, imagine se mais tarde você quiser ingressar em semanas ou meio dia ou algo assim, precisará usar um tipo de dados diferente e poderá perder o benefício que achava que estava recebendo.

    Para um INNER JOIN, usar a cláusula WHERE versus a cláusula ON não fará diferença. No entanto, da mesma forma, eu preferiria manter critérios de junção na cláusula ON e critérios de filtragem na cláusula WHERE. Isso muda se você estiver falando de um OUTER JOIN, é claro, já que o posicionamento de certos critérios pode alterar a semântica.

    Eu escreveria sua consulta dessa maneira (e tomaria cuidado onde você usa o performer "palavra" inventado, pois pode receber olhares engraçados da maioria):

    LEFT OUTER JOIN
  dbo.tasks -- always use schema prefix!
  ON tasks.datetimecolumn >= events.datecolumn
    AND tasks.datetimecolumn < DATEADD(DAY, 1, events.datecolumn)

    Claro, você deve testar isso para ver o impacto que isso DATEADD()tem na tabela de eventos. Como parece que essa é a mesa menor por uma grande margem, não espero que o efeito seja enorme, mas não custa verificar.

    • 4

relate perguntas