Sql-server – Top 2 rows per partition from an absolute value date difference

ctepartitioningperformancesql serversql-server-2005

I have a database of security camera footage in a denormalized database. I have Locations which have multiple Cameras which take multiple images.

Location + Camera + image capture_date is the clustered primary key, and currently the only index on the table. The kicker is searching a single camera takes <1 millisecond from SSMS and ~70ms from my web application. My current working CTE solutions take around 3 minutes for three cameras.

To give an overview of the cameras at a location I need to select 2 images from each camera nearest a given date (such as the current date). Because of this I need an absolute value (dates before or after the search date are equally valid), thus I'm searching by the smallest ABS(@date -capture_date).

Here's the current code. It works but it's not SARGable and it's extremely slow. I also only need the top 2 rows per camera in the CTE, since there may be hundreds of thousands of images per partition.

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

Best Answer

This is just a starting point and will likely need some tweaking.

Essentially this will get you the 4 closest capture dates to your specified date (2 closest after and 2 closest before).

You will need to add some logic to your outer select to pick which ones to use, but you will be doing a DATEDIFF on 4 fields instead of all of them.

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