主页 / coding / 问题 / 77516580
Accepted
John D
Asked: 2023-11-20 22:18:45 +0800 CST

如何使用 where 子句中的 CONVERT() 函数从具有一百万条记录的表中为给定的 DATETIME 日期选择几条记录？

  • 772

我有一个查询，它使用 CONVERT() 函数来获取特定日期（天）的记录。我从SO-answer中了解到，当有数百万条记录时，CONVERT()不应该出现在where子句中（SQL查询快速选择百万条记录）。

当我们在 where 子句中使用 CONVERT() 函数在 MOTRIP 表中拥有一百万条记录时，我担心查询的速度。

我寻求建议，将“新的仅日期字段”(TRIP_START_DATE) 放入 MOTRIP 表中，以消除在需要特定日期的最后一次行程的查询中使用 CONVERT() 函数是否是个好主意。???

MOTRIP 表在 DTTM_TRIP_START (smalldatetime) 列上有一个索引，在 UID_CUSTOMER (int) 列上有一个索引。

我使用 CONVERT(DATE,DTTM_TRIP_START) 函数检查一天中发生的行程，以根据其他条件了解一天中的最后一次行程。

DECLARE @UID_MOTRIP int = 409;
SELECT TOP (1)
       @UID_MOTRIP AS UID_MOTRIP,
       UID_MOTRIP AS TRIP_WITH_POST_UID,
       IIF(UID_INSPECTION_POST = 0, 1, 0) AS IS_POSTTRIP_MISSING
FROM MOTRIP
WHERE (UID_CUSTOMER = (SELECT UID_CUSTOMER
                       FROM MOTRIP
                       WHERE (UID_MOTRIP = @UID_MOTRIP)))
  AND (CONVERT(date, DTTM_TRIP_START) = (SELECT CONVERT(date, DTTM_TRIP_START)
                                         FROM MOTRIP
                                         WHERE (UID_MOTRIP = @jjUID_MOTRIP)))
ORDER BY DTTM_TRIP_START DESC;

我当前的“测试”数据库只有 200 条记录，结果是即时的。

当考虑将来有一百万条记录时，我添加了一个 where 子句来限制基于 MOTRIP=409 的客户的提取。

sql

1 个回答

  1. Best Answer

    首先，如果该索引（或以这两列开头的类似索引）尚不存在，您应该在 上创建一个组合索引。MOTRIP(UID_CUSTOMER, DTTM_TRIP_START)单独的UID_CUSTOMER和DTTM_TRIP_START索引不提供相同的有效访问。

    一旦该索引就位，就可以通过计算等于以下的开始和结束日期/时间范围来实现有效的查找：

    • 范围开始 = 感兴趣日期的 00:00
    • 范围结束 = 第二天 00:00。

    range-start包含在内，range-end包含在内。这是日期/时间范围查找的常见做法。不需要仅日期列。

    UID_CUSTOMER最后，您可以在匹配andDTTM_TRIP_START >= range-start和上查找所需的行DTTM_TRIP_START < range-end。请注意使用 来<表示唯一的结束日期/时间。SQL Server 应该对上面提到的索引执行范围查找。

    我在下面修改后的查询中应用了这个逻辑。我还将双子选择更改为单连接以消除重复，并向所有列引用添加限定符。

    DECLARE @UID_MOTRIP int = 409;
SELECT TOP (1)
       @UID_MOTRIP AS UID_MOTRIP,
       M.UID_MOTRIP AS TRIP_WITH_POST_UID,
       IIF(M.UID_INSPECTION_POST = 0, 1, 0) AS M.IS_POSTTRIP_MISSING
FROM MOTRIP MRef
JOIN MOTRIP M
    ON M.UID_CUSTOMER = MRef.UID_CUSTOMER
    AND M.DTTM_TRIP_START >= CONVERT(date, MRef.DTTM_TRIP_START)
    AND M.DTTM_TRIP_START < DATEADD(day, 1, CONVERT(date, MRef.DTTM_TRIP_START))
WHERE MRef.UID_MOTRIP = @jjUID_MOTRIP
ORDER BY M.DTTM_TRIP_START DESC;

    这也可以被编码为逻辑上相同的：

    DECLARE @UID_MOTRIP int = 409;
SELECT TOP (1)
       @UID_MOTRIP AS UID_MOTRIP,
       M.UID_MOTRIP AS TRIP_WITH_POST_UID,
       IIF(M.UID_INSPECTION_POST = 0, 1, 0) AS M.IS_POSTTRIP_MISSING
FROM (
    SELECT
        MRef.UID_CUSTOMER,
        CONVERT(date, MRef.DTTM_TRIP_START) AS RangeStart,
        DATEADD(day, 1, CONVERT(date, MRef.DTTM_TRIP_START)) AS RangeEnd
    FROM MOTRIP MRef
    WHERE MRef.UID_MOTRIP = @jjUID_MOTRIP
) MRef
JOIN MOTRIP M
    ON M.UID_CUSTOMER = MRef.UID_CUSTOMER
    AND M.DTTM_TRIP_START >= MRef.RangeStart
    AND M.DTTM_TRIP_START < MRef.RangeEnd
ORDER BY M.DTTM_TRIP_START DESC;
    • 1

相关问题