Início / dba / Perguntas / 4782
Accepted
Stuart Blackler
Asked: 2011-08-19 11:24:52 +0800 CST

Conselho com coluna de data SQL Server 2005

  • 772

Eu tenho duas colunas de data e hora no SQL Server 2005 que preciso consultar sem a parte de hora da data e hora. Atualmente, minha consulta se parece com isso (apenas um exemplo):

WITH Dates AS ( 
        SELECT [Date] = @StartDate
        UNION ALL SELECT [Date] = DATEADD(DAY, 1, [Date])
        FROM Dates WHERE [Date] < @EndDate
) 
SELECT DISTINCT ID
FROM table t
CROSS APPLY DATES d
WHERE d.[Date] BETWEEN CONVERT(DATETIME, CONVERT(VARCHAR, t.StartDate, 103)) AND CONVERT(DATETIME, CONVERT(VARCHAR, t.EndDate, 103))

Agora, isso resulta em uma varredura de índice clusterizada completa (surpresa, surpresa). Estou tentando pensar em maneiras de tornar isso mais rápido (a consulta real leva 3 minutos: O). Pensei em fazer o seguinte, mas ainda não tentei, pois fiquei sem tempo antes:

  1. Use uma coluna computada apenas com a parte da data
  2. Indexar a referida coluna computada (não tenho certeza se isso é possível?)
  3. Use uma exibição indexada (novamente, não tenho certeza se isso é possível, funcionará?)

A maneira mais fácil seria atualizar a coluna e remover todas as informações de tempo, mas não posso fazer isso :(

Alguma ideia?

Atualizar

Obrigado por todas as respostas até agora. Acho que o ponto da pergunta foi perdido porque não estava claro sobre o que queria. Foi mal. Eu pretendia apenas otimizar a parte de conversão de data da consulta, pois a quantidade de dados com a qual estou lidando é pequena na realidade (< 500.000 após a aplicação cruzada com um intervalo de datas de um ano). Desculpe pela confusão sobre isso.

Para aqueles que otimizam o restante da consulta para mim, posso ver o que as pessoas estão dizendo usando, < >mas considere o seguinte:

  1. Os parâmetros que são passados ​​são um intervalo de datas (por exemplo, 1º ao final deste mês)
  2. A data inicial na tabela pode aparecer antes ou durante o intervalo de datas dos parâmetros (por exemplo, apenas a data final está no intervalo de datas)
  3. A data final na tabela pode aparecer durante ou após o intervalo de datas dos parâmetros (por exemplo, apenas a data inicial está no intervalo de datas)
  4. Por fim, as datas de início e término na tabela estão no intervalo de datas dos parâmetros.

Pessoalmente, diante do exposto, nunca consegui uma < >solução para funcionar. A única maneira de fazê-lo funcionar corretamente e não perder nada é usando um CTE e dizendo where d.[Date] BETWEEN t.StartDate AND t.EndDate.

Eu espero que isso esclareça as coisas. Obrigado novamente.

sql-server-2005 performance

5 respostas

  1. Best Answer

    Você pode executar as etapas 1 e 2: mas use a técnica DATEADD/DATEDIFF conforme esta: Como obter a data atual sem a parte do tempo

    Você provavelmente não conseguirá indexar a coluna computada porque ela não será determinística com o método varchar

    • 5

  2. Você deve ser capaz de usar colunas computadas que você pode indexar. Eles devem ser determinísticos, pois você está saindo de um valor estático. Pode depender de como você obtém o valor na coluna calculada para saber se funcionará.

    • 4

  3. Posso estar errado, mas parece que isso foi muito complicado/mal compreendido ou simplesmente confuso pelo CTE em sua pergunta original. Pelos comentários que você adicionou a várias respostas, parece que:

    • Você tem uma tabela que inclui duas colunas do tipo DateTime, StartDate e EndDate. Isso inclui valores de tempo, ou seja, a parte do tempo não é fixada em um valor conhecido como '00:00:00'.
    • Você deseja encontrar a contagem de registros em sua tabela que possuem valores StartDate e EndDate que estão dentro do intervalo definido pelos parâmetros @StartDate e @EndDate

    Se eu perdi o ponto, espero que você possa pelo menos usar o seguinte script para criar alguns dados de teste :)

    IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
DROP TABLE [dbo].[MyTable]
GO

CREATE TABLE dbo.MyTable
(
    [id] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , [StartDate] DATETIME
    , [EndDate] DATETIME
)
GO

CREATE INDEX IX_MyTable_StartDate_EndDate ON dbo.MyTable ([StartDate] ASC, [EndDate] ASC)
GO

INSERT dbo.MyTable 
(
    [StartDate]
    , [EndDate]
)
VALUES
(
    DATEADD(MI, (ABS(CHECKSUM(NEWID())) % 1339), DATEADD(DAY, -(ABS(CHECKSUM(NEWID())) % 500), GETDATE()))
    , DATEADD(MI, (ABS(CHECKSUM(NEWID())) % 1339), DATEADD(DAY, (ABS(CHECKSUM(NEWID())) % 500), GETDATE()))
)
GO 10000

    Não é este, portanto, um caso simples de:

    DECLARE 
    @StartDate CHAR(8), @EndDate CHAR(8) -- Date only values passed to procedure
    , @StartDateTime DATETIME, @EndDateTime DATETIME -- Internal DateTime values

SELECT 
    @StartDate = '20110101'
    , @EndDate = '20110831'

SELECT
    @StartDateTime = CAST(@StartDate AS DATETIME) -- '2011-01-01 00:00:00'
    , @EndDateTime = DATEADD(DAY, 1, CAST(@EndDate AS DATETIME)) -- '2011-09-01 00:00:00'

SELECT
    COUNT([id])
FROM
    dbo.MyTable
WHERE
    StartDate >= @StartDateTime
AND
    EndDate < @EndDateTime

    EDIT: perdi uma otimização óbvia na consulta acima

    SELECT
    COUNT([id])
FROM
    dbo.MyTable
WHERE
    StartDate BETWEEN @StartDateTime AND @EndDatetime
AND
    EndDate < @EndDateTime
    • 3

  4. Como mencionei, em última análise, tentaria me livrar da aplicação cruzada.

    Pelo que pude perceber, você terminará com linhas da tabela "table" que têm uma data de início e uma data de término antes/depois (respectivamente) de alguma linha na tabela Dates. Por causa de sua aplicação cruzada, você multiplicará os resultados da "tabela" pelo número de linhas correspondentes na tabela "Datas". Então, por causa do Distinct, você mesclará essa multiplicação em uma única linha. (É aí que vejo a ineficiência.)

    Por que não apenas fazer isso:

    DECLARE @MinDate AS DATETIME
DECLARE @MaxDate AS DATETIME

SELECT 
    @MinDate = MIN(d.StartDate), 
    @MaxDate = MAX(d.EndDate)
FROM Dates d

SELECT DISTINCT ID
FROM table t
WHERE 
    DATEADD(day, DATEDIFF(day, 0, t.StartDate), 0) < @MaxDate OR
    DATEADD(day, DATEDIFF(day, 0, t.EndDate), 0) > @MinDate

    Advertência 1:

    Isso não funcionará se houver datas na tabela "tabela" que estiverem entre as datas mínima e máxima, mas não cercarem uma data na tabela Datas. Então, se isso funciona é baseado completamente em como seu CTE é construído. (Como não tenho a fonte completa, devo presumir que ela foi construída a partir da tabela "mesa".

    Exemplo:

    Se "Tabela" tiver uma linha com StartDate/EndDate de 18/19 de agosto (respectivamente), mas o CTE for criado para que não haja 18 de agosto nem 19 de agosto no conjunto de resultados de Datas, isso não funcionará.

    Advertência 2:

    Não tenho certeza se essas comparações > e < estão corretas. Preciso de dados de amostra para validar isso.

    • 2

  5. Eu sinto sua dor. Eu escrevi o seguinte para esse tipo de tarefa, eu uso regularmente

    DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME; -- these should be typed, no hour

SET @StartDate = '2011-01-01';
SET @EndDate = '2011-01-31';

WITH TALLY AS -- GENERATE AN ON THE FLY TALLY TABLE WITH REQUIRED AMOUNT OF ROWS
(
    SELECT TOP (DATEDIFF(DD,@StartDate,@EndDate)+1) ROW_NUMBER() OVER (ORDER BY (SELECT 1))-1 N FROM sys.objects A
),   DATES AS -- GENERATE ALL D, D+1 POSSIBILITIES, YOU CAN ADJUST TO ADD 23:59:59.999 INSTEAD OF A WHOLE DAY
(
    SELECT DATEADD(DD,N,@StartDate) StartDate,DATEADD(DD,N+1,@EndDate) EndDate FROM TALLY
)
SELECT DISTINCT ID
FROM table t
INNER JOIN DATES d ON t.YourDateWithHours BETWEEN d.StartDate AND d.EndDate -- THIS IS SARGABLE AND DETERMINISTIC, INDEXES WILL BE USED
    • -1

relate perguntas