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