Maneira mais eficiente de recuperar intervalos de datas

Esse é um problema difícil de resolver em geral, mas há algumas coisas que podemos fazer para ajudar o otimizador a escolher um plano. Este script cria uma tabela com 10.000 linhas com uma distribuição pseudo-aleatória conhecida de linhas para ilustrar:

CREATE TABLE dbo.SomeDateTable
(
    Id          INTEGER IDENTITY(1, 1) PRIMARY KEY NOT NULL,
    StartDate   DATETIME NOT NULL,
    EndDate     DATETIME NOT NULL
);
GO
SET STATISTICS XML OFF
SET NOCOUNT ON;
DECLARE
    @i  INTEGER = 1,
    @s  FLOAT = RAND(20120104),
    @e  FLOAT = RAND();

WHILE @i <= 10000
BEGIN
    INSERT dbo.SomeDateTable
        (
        StartDate, 
        EndDate
        )
    VALUES
        (
        DATEADD(DAY, @s * 365, {d '2009-01-01'}),
        DATEADD(DAY, @s * 365 + @e * 14, {d '2009-01-01'})
        )
        
    SELECT
        @s = RAND(),
        @e = RAND(),
        @i += 1
END

A primeira questão é como indexar esta tabela. Uma opção é fornecer dois índices nas DATETIMEcolunas, para que o otimizador possa pelo menos escolher se busca em StartDateou EndDate.

CREATE INDEX nc1 ON dbo.SomeDateTable (StartDate, EndDate)
CREATE INDEX nc2 ON dbo.SomeDateTable (EndDate, StartDate)

Naturalmente, as desigualdades em ambos StartDatee EndDatesignificam que apenas uma coluna em cada índice pode suportar uma busca na consulta de exemplo, mas isso é o melhor que podemos fazer. Podemos considerar tornar a segunda coluna em cada índice INCLUDEem vez de uma chave, mas podemos ter outras consultas que podem realizar uma busca de igualdade na coluna inicial e uma busca de desigualdade na segunda coluna. Além disso, podemos obter melhores estatísticas dessa maneira. De qualquer forma...

DECLARE
    @StartDateBegin DATETIME = {d '2009-08-01'},
    @StartDateEnd DATETIME = {d '2009-10-15'},
    @EndDateBegin DATETIME = {d '2009-08-05'},
    @EndDateEnd DATETIME = {d '2009-10-22'}

SELECT
    COUNT_BIG(*)
FROM dbo.SomeDateTable AS sdt
WHERE
    sdt.StartDate BETWEEN @StartDateBegin AND @StartDateEnd
    AND sdt.EndDate BETWEEN @EndDateBegin AND @EndDateEnd

Essa consulta usa variáveis, portanto, em geral, o otimizador adivinhará a seletividade e a distribuição, resultando em uma estimativa de cardinalidade adivinhada de 81 linhas . Na verdade, a consulta produz 2.076 linhas, uma discrepância que pode ser importante em um exemplo mais complexo.

No SQL Server 2008 SP1 CU5 ou posterior (ou R2 RTM CU1), podemos aproveitar a Otimização de Incorporação de Parâmetros para obter melhores estimativas, simplesmente adicionando OPTION (RECOMPILE)à SELECTconsulta acima. Isso causa uma compilação logo antes da execução do lote, permitindo que o SQL Server 'veja' os valores reais dos parâmetros e os otimize. Com essa alteração, a estimativa aumenta para 468 linhas (embora você precise verificar o plano de tempo de execução para ver isso). Essa estimativa é melhor do que 81 linhas, mas ainda não é tão próxima. As extensões de modelagem habilitadas pelo sinalizador de rastreamento 2301 podem ajudar em alguns casos, mas não com esta consulta.

O problema é onde as linhas qualificadas pelas duas pesquisas de intervalo se sobrepõem. Uma das suposições simplificadoras feitas no componente de estimativa de custo e cardinalidade do otimizador é que os predicados são independentes (portanto, se ambos tiverem uma seletividade de 50%, o resultado da aplicação de ambos será considerado qualificar 50% de 50% = 25% das linhas ). Onde esse tipo de correlação é um problema, muitas vezes podemos contorná-lo com estatísticas de várias colunas e/ou filtradas. Com dois intervalos com pontos iniciais e finais desconhecidos, isso se torna impraticável. É aqui que às vezes temos que recorrer a reescrever a consulta para um formulário que produz uma estimativa melhor:

SELECT COUNT(*) FROM
(
    SELECT
        sdt.Id
    FROM dbo.SomeDateTable AS sdt
    WHERE 
        sdt.StartDate BETWEEN @StartDateBegin AND @StartDateEnd
    INTERSECT
    SELECT
        sdt.Id
    FROM dbo.SomeDateTable AS sdt 
    WHERE
        sdt.EndDate BETWEEN @EndDateBegin AND @EndDateEnd
) AS intersected (id)
OPTION (RECOMPILE)

Esse formulário produz uma estimativa de tempo de execução de 2.110 linhas (versus 2.076 reais). A menos que você tenha o TF 2301 ativado, nesse caso as técnicas de modelagem mais avançadas percebem o truque e produzem exatamente a mesma estimativa de antes: 468 linhas.

Um dia, o SQL Server pode obter suporte nativo para intervalos. Se isso vier com um bom suporte estatístico, os desenvolvedores podem temer um pouco menos o ajuste de planos de consulta como esse.

Não conheço uma solução que seja rápida para todas as distribuições de dados, mas se todos os seus intervalos forem curtos, geralmente podemos acelerá-la. Se, por exemplo, os intervalos forem menores que um dia, em vez desta consulta:

SELECT  TaskId ,    
        TaskDescription ,
        StartedAt ,    
        FinishedAt    
FROM    dbo.Tasks    
WHERE   '20101203' BETWEEN StartedAt AND FinishedAt

podemos adicionar mais uma condição:

SELECT  TaskId ,    
        TaskDescription ,
        StartedAt ,    
        FinishedAt    
FROM    dbo.Tasks    
WHERE   '20101203' BETWEEN StartedAt AND FinishedAt
    AND StartedAt >= '20101202'
    AND FinishedAt <= '20101204' ;

Como resultado, em vez de varrer a tabela inteira, a consulta varrerá apenas o intervalo de dois dias, o que é mais rápido. Se os intervalos forem mais longos, podemos armazená-los como sequências de intervalos mais curtos. Detalhes aqui: Ajustando consultas SQL com a ajuda de restrições