主页 / dba / 问题 / 9159
Accepted
Ben Brocka
Asked: 2011-12-15 07:18:33 +0800 CST

每个分区的前 2 行来自绝对值日期差异

  • 772

我在非规范化数据库中有一个安全摄像机镜头数据库。我有多个位置，这些位置有多个拍摄多张图像的相机。

Location + Camera + image capture_date 是聚簇主键，也是目前表上唯一的索引。踢球者正在搜索单个摄像头，SSMS 耗时 <1 毫秒，我的 Web 应用程序耗时约 70 毫秒。我目前工作的 CTE 解决方案需要大约 3 分钟的时间来处理三个摄像头。

为了概述某个位置的摄像机，我需要从最接近给定日期（例如当前日期）的每个摄像机中选择 2 张图像。因此，我需要一个绝对值（搜索日期之前或之后的日期同样有效），因此我按最小的ABS(@date -capture_date).

这是当前代码。它可以工作，但它不是 SARGable，而且速度非常慢。我还只需要 CTE 中每个摄像头的前 2 行，因为每个分区可能有数十万张图像。

DECLARE @date datetime,
        @location varchar(4)
SET     @date ='2011-12-13 12:00:00'
SET     @location='CS01';
WITH CTE AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION  BY Camera ORDER BY  abs(datediff(second,@date, [capture_date]))) AS Ranking
    FROM rs_camera_pictures
    WHERE 
        location=@location)
SELECT * FROM CTE WHERE Ranking <= 2
sql-server sql-server-2005

4 个回答

  1. Best Answer

    这只是一个起点，可能需要进行一些调整。

    从本质上讲，这将为您提供与指定日期最接近的 4 个捕获日期（2 个最接近之后和 2 个最接近之前）。

    您将需要向外部选择添加一些逻辑以选择要使用的那些，但您将DATEDIFF在 4 个字段而不是所有字段上执行操作。

    WITH CTE AS
(
SELECT camera, MIN(r1.capture_date) as 'After', MAX(r2.capture_date) as 'Before'
FROM rs_camera_pictures r1
INNER JOIN rs_camera_pictures r2
    ON r1.location = r2.location
    AND r1.camera = r2.camera
WHERE r1.capture_date > @date
AND r2.capture_date < @date
GROUP BY camera
UNION ALL
SELECT camera, MIN(r1.capture_date) as 'After', MAX(r2.capture_date) as 'Before'
FROM rs_camera_pictures r1
INNER JOIN rs_camera_pictures r2
    ON r1.location = r2.location
    AND r1.camera = r2.camera
INNER JOIN CTE c
    ON r1.location = c.location
    AND r1.camera = c.camera
WHERE r1.capture_date > c.[After]
AND r2.capture_date < c.Before
GROUP BY camera
)
    • 1

  2. 这应该为所选位置的每个摄像头返回四行，然后对它们进行排名，为每个摄像头返回最好的两行。

    LocationCamera 是一个位置的相机的不同列表。

    DECLARE @date datetime,
        @location varchar(4)
SET     @date ='2011-12-13 12:00:00'
SET     @location='CS01';

SELECT      Location,Camera,Capture_Date,Ranking
FROM        (
                SELECT      Location,Camera,Capture_Date,
                            ROW_NUMBER() OVER (PARTITION  BY Camera ORDER BY  abs(datediff(second,@date, [capture_date]))) AS Ranking
                FROM        (
                                SELECT      L.Location,
                                            L.Camera,
                                            CP.capture_date
                                FROM        LocationCamera  L
                                CROSS APPLY (
                                                SELECT      TOP (2)
                                                            CP.[capture_date]
                                                FROM        rs_camera_pictures  CP
                                                WHERE       L.Location          = CP.Location
                                                AND         L.Camera            = CP.Camera
                                                AND         CP.capture_date     >= @date
                                                ORDER BY    [capture_date]
                                            )   TopN
                                WHERE       L.Location      = @Location
                                UNION ALL
                                SELECT      L.Location,
                                            L.Camera,
                                            CP.[capture_date]
                                FROM        LocationCamera  L
                                CROSS APPLY (
                                                SELECT      TOP (2)
                                                            CP.[capture_date]
                                                FROM        rs_camera_pictures  CP
                                                WHERE       L.Location          = CP.Location
                                                AND         L.Camera            = CP.Camera
                                                AND         CP.capture_date     < @date
                                                ORDER BY    [capture_date]      DESC
                                            )   BottomN
                                WHERE       L.Location      = @location
                            )   D
            )   R
WHERE       Ranking <= 2
    • 1

  3. 据我所知（加上一些谷歌搜索和黑客攻击，看看是否有一种我还不知道的方法）没有解决方案可以避免非 SARGable ABS(DATEDIFF 并且优于你现在拥有的。

    因此，如果您必须实施非 SARGable 搜索，最重要的是找到 SARGable 方法来限制集合的大小。目前，您的 WHERE 子句确实包含一个 SARGable 子句，但如果我们谈论的是每个摄像头可能有数十万行（即每个位置可能有数百万行），则进一步缩小问题域是有意义的。

    我的建议是为最大搜索距离选择一个合理的值，并在日期上建立一个上限和下限。例如，假设您的系统通常至少每 5 分钟拍摄一张照片，除非相机出现故障。我们可以设置 6 分钟的上限和下限（以允许时间上的“抖动”）并且只对这些范围内的图像进行排名。看起来像这样：

    DECLARE @date datetime,
        @location varchar(4) ,
        @dateUB DATETIME,
        @dateLB DATETIME
SET     @date ='2011-12-13 12:00:00'
SET     @DateUB = DATEADD(minute, 6, @matchDate)
SET     @DateLB = DATEADD(minute, -6, @matchDate)
SET     @location='CS01';
WITH CTE AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION  BY Camera ORDER BY  abs(datediff(second,@date, [capture_date]))) AS Ranking
    FROM rs_camera_pictures
    WHERE 
        location=@location
    AND capture_date BETWEEN @dateUB AND @dateLB )
SELECT * FROM CTE WHERE Ranking <= 2

    如果 capture_date 和 location 被索引覆盖，这应该会给您带来显着的改进。

    • 1

  4. 也许从选定的日期开始在两个方向上取前 2 名，然后从中取前 2 名。

    DECLARE @date datetime,
        @location varchar(4)
SET     @date ='2011-12-13 12:00:00'
SET     @location='CS01';
WITH candidates AS (
    SELECT * FROM (
        SELECT TOP 2 *
        FROM rs_camera_pictures
        WHERE location=@location
            AND capture_date <= @date
        ORDER BY capture_date DESC
    ) [before]
    UNION
    SELECT * FROM (
        SELECT TOP 2 *
        FROM rs_camera_pictures
        WHERE location=@location
            AND capture_date >= @date
        ORDER BY capture_date ASC
    ) [after]
),
CTE AS (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION  BY Camera ORDER BY  abs(datediff(second,@date, [capture_date]))) AS Ranking
    FROM candidates
    WHERE 
        location=@location
)
SELECT * FROM CTE WHERE Ranking <= 2
    • 0

相关问题