主页 / dba / 问题 / 4782
Accepted
Stuart Blackler
Asked: 2011-08-19 11:24:52 +0800 CST

带有日期列的建议 SQL Server 2005

  • 772

我在 SQL Server 2005 中有两个日期时间列，我需要在没有日期时间的时间部分的情况下进行查询。目前，我的查询看起来类似于此（只是一个例子）：

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

现在这会导致完整的聚簇索引扫描（惊喜，惊喜）。我正在想办法让它更快（实际查询需要 3 分钟 :O）。我曾考虑过执行以下操作，但由于我之前没有时间，所以还没有尝试过：

  1. 使用仅包含日期部分的计算列
  2. 索引表示计算列（不确定这是否可能？）
  3. 使用索引视图（同样，不确定这是否可行，是否可行？）

最简单的方法是更新列并删除所有时间信息，但我不能这样做:(

有任何想法吗？

更新

感谢您到目前为止的所有回答。我认为问题的重点被遗漏了，因为我不太清楚自己想要什么。我的错。我只是想优化查询的日期转换部分，因为我正在处理的数据量实际上很小（在交叉应用一年长的日期范围后 < 500,000）。很抱歉对此造成混淆。

对于那些为我优化其余查询的人，我可以通过使用看到人们在说什么，< >但请考虑以下几点：

  1. 传入的参数是一个日期范围（例如1号到本月底）
  2. 表中的开始日期可以出现在参数日期范围之前或期间（例如，只有结束日期在日期范围内）
  3. 表中的结束日期可以出现在参数日期范围内或之后（例如，只有开始日期在日期范围内）
  4. 最后，表中的开始和结束日期在参数日期范围内。

就个人而言，鉴于上述情况，我永远无法找到< >工作的解决方案。我能让它正常工作并且不会遗漏任何东西的唯一方法是使用 CTE 并说明 where d.[Date] BETWEEN t.StartDate AND t.EndDate.

我希望这能解决问题。再次感谢。

sql-server-2005 performance

5 个回答

  1. Best Answer

    您可以执行第 1 步和第 2 步：但按照以下说明使用 DATEADD/DATEDIFF 技术：How to the get current date without the time part

    您很可能无法为计算列编制索引，因为它无法通过 varchar 方法确定

    • 5

  2. 您应该能够使用可以索引的计算列。当您离开静态值时，这些应该是确定性的。这可能取决于您如何将值放入计算列中是否有效。

    • 4

  3. 我可能是错的，但看起来这在您的原始问题中被 CTE 过于复杂/误解或只是简单地混淆了。从您添加到各种答案的评论看来：

    • 您有一个表，其中包含两个 DateTime 类型的列，StartDate 和 EndDate。这些包括时间值，即时间部分不固定为已知值，例如“00:00:00”。
    • 您想要查找表中 StartDate 和 EndDate 值在参数 @StartDate 和 @EndDate 定义的范围内的记录数

    如果我错过了重点，您至少可以使用以下脚本来创建一些测试数据：)

    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

    因此，这不是一个简单的案例吗：

    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

    编辑：我在上面的查询中错过了一个明显的优化

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

  4. 正如我提到的，我最终会尝试摆脱交叉应用。

    据我所知，您最终会得到“表”表中的行，这些行的开始日期和结束日期（分别）在 Dates 表中的某行之前/之后。由于您的交叉应用，您会将“表格”结果乘以“日期”表格中相应的行数。然后，由于 Distinct，您将该乘法合并为一行。（这就是我看到效率低下的地方。）

    为什么不这样做：

    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

    警告 1：

    如果“表”表中的日期介于最小日期和最大日期之间但不包含日期表中的日期，则这将不起作用。因此，这是否有效完全取决于您的 CTE 是如何构建的。（由于我没有完整的源代码，我不得不假设它是根据“表”表构建的。

    例子：

    如果“表”有一行的开始日期/结束日期为 8 月 18 日/19 日（分别），但 CTE 的构建使得日期结果集中既没有 8 月 18 日也没有 8 月 19 日，那么这将不起作用。

    警告 2：

    我不确定那些 > 和 < 比较是否正确。我需要样本数据来验证这一点。

    • 2

  5. 我感觉到你的痛苦。我为那种任务写了以下内容，我经常使用它

    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

相关问题