Início / dba / Perguntas / 9159
Accepted
Ben Brocka
Asked: 2011-12-15 07:18:33 +0800 CST

As 2 primeiras linhas por partição de uma diferença de data de valor absoluto

  • 772

Eu tenho um banco de dados de imagens de câmeras de segurança em um banco de dados desnormalizado. Eu tenho locais que possuem várias câmeras que tiram várias imagens.

Location + Camera + image capture_date é a chave primária agrupada e, atualmente, o único índice na tabela. O kicker está procurando uma única câmera leva <1 milissegundo do SSMS e ~ 70ms do meu aplicativo da web. Minhas soluções CTE de trabalho atuais levam cerca de 3 minutos para três câmeras .

Para dar uma visão geral das câmeras em um local, preciso selecionar 2 imagens de cada câmera mais próximas de uma determinada data (como a data atual). Por isso preciso de um valor absoluto (datas anteriores ou posteriores à data de busca são igualmente válidas), por isso estou buscando pelo menor ABS(@date -capture_date).

Aqui está o código atual. Funciona, mas não é SARGable e é extremamente lento. Também preciso apenas das 2 primeiras linhas por câmera no CTE, pois pode haver centenas de milhares de imagens por partição.

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 respostas

  1. Best Answer

    Este é apenas um ponto de partida e provavelmente precisará de alguns ajustes.

    Essencialmente, isso fornecerá as 4 datas de captura mais próximas da data especificada (2 mais próximas depois e 2 mais próximas antes).

    Você precisará adicionar alguma lógica à sua seleção externa para escolher quais usar, mas estará fazendo um DATEDIFFem 4 campos em vez de todos eles.

    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. Isso deve retornar quatro linhas por câmera no local selecionado e, em seguida, classificá-las para retornar as duas melhores para cada câmera.

    LocationCamera é a lista distinta das câmeras para um local.

    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. Pelo que sei (além de algumas pesquisas no Google e hacking para ver se havia uma maneira que eu ainda não conhecia), não há solução que evite o ABS não SARGable (DATEDIFF e supere o que você tem agora.

    Portanto, se você deve implementar uma pesquisa não SARGable, a coisa mais importante a fazer é encontrar maneiras SARGable de restringir o tamanho do conjunto. No momento, sua cláusula WHERE inclui uma cláusula SARGable, mas se estivermos falando de potencialmente centenas de milhares de linhas por câmera (ou seja, milhões em potencial por local), faz sentido reduzir ainda mais o domínio do problema.

    Minha recomendação é escolher um valor razoável para a distância máxima de pesquisa e estabelecer um limite superior e inferior na data. Por exemplo, digamos que seu sistema normalmente tiraria uma foto pelo menos a cada 5 minutos, a menos que a câmera esteja falhando. Podemos definir um limite superior e inferior de 6 minutos (para permitir "jitter" no tempo) e apenas classificar as imagens dentro desses limites. Isso ficaria assim:

    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

    Se capture_date e location forem cobertos por índices, isso deve proporcionar uma melhoria significativa.

    • 1

  4. Talvez pegue os 2 primeiros em ambas as direções a partir da data selecionada e, em seguida, pegue os 2 primeiros a partir disso.

    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

relate perguntas