Оптимальное выполнение запроса для последней записи для каждого N

Вот сценарий, в котором я нахожусь.

У меня достаточно большая таблица, из которой мне нужно запрашивать последние записи. Вот создание для основных столбцов для запроса:

CREATE TABLE [dbo].[ChannelValue](
   [ID] [bigint] IDENTITY(1,1) NOT NULL,
   [UpdateRecord] [bit] NOT NULL,
   [VehicleID] [int] NOT NULL,
   [UnitID] [int] NOT NULL,
   [RecordInsert] [datetime] NOT NULL,
   [TimeStamp] [datetime] NOT NULL
   ) ON [PRIMARY]
GO

Столбец ID представляет собой первичный ключ, и для VehicleID и TimeStamp имеется некластеризованный индекс

CREATE NONCLUSTERED INDEX [IX_ChannelValue_TimeStamp_VehicleID] ON [dbo].[ChannelValue] 
(
    [TimeStamp] ASC,
    [VehicleID] ASC
)ON [PRIMARY]
GO

Таблица, над которой я работаю, чтобы оптимизировать мой запрос, имеет чуть более 23 миллионов строк и всего лишь десятую часть размеров, с которыми должен работать запрос.

Мне нужно вернуть последнюю строку для каждого идентификатора транспортного средства.

Я просматривал ответы на этот вопрос здесь, в Stackru, и я провел немало поисков в Google, и, кажется, есть 3 или 4 распространенных способа сделать это на SQL Server 2005 и выше.

Пока самый быстрый метод, который я нашел, это следующий запрос:

SELECT cv.*
FROM ChannelValue cv
WHERE cv.TimeStamp = (
SELECT
    MAX(TimeStamp)
FROM ChannelValue
WHERE ChannelValue.VehicleID = cv.VehicleID
)

При текущем объеме данных в таблице для выполнения требуется около 6 секунд, что находится в разумных пределах, но при объеме данных, которые таблица будет содержать в реальной среде, запрос начинает выполняться слишком медленно.

Глядя на план выполнения, я беспокоюсь о том, что делает SQL Server для возврата строк.

Я не могу опубликовать изображение плана выполнения, потому что моя репутация недостаточно высока, но сканирование индекса анализирует каждую строку в таблице, что сильно замедляет запрос.

План выполнения

Я попытался переписать запрос несколькими различными методами, в том числе с помощью метода разделов SQL 2005, например:

WITH cte
AS (
    SELECT *,
    ROW_NUMBER() OVER(PARTITION BY VehicleID ORDER BY TimeStamp DESC) AS seq
     FROM ChannelValue
)

SELECT
   VehicleID,
   TimeStamp,
   Col1
FROM cte
WHERE seq = 1

Но производительность этого запроса еще хуже на довольно большую величину.

Я попытался реструктурировать запрос следующим образом, но скорость выполнения и план выполнения запроса практически идентичны:

SELECT cv.*
FROM (
   SELECT VehicleID
    ,MAX(TimeStamp) AS [TimeStamp]
   FROM ChannelValue
   GROUP BY VehicleID
) AS [q]
INNER JOIN ChannelValue cv
   ON cv.VehicleID = q.VehicleID
   AND cv.TimeStamp = q.TimeStamp

У меня есть некоторая гибкость в отношении структуры таблицы (хотя и в ограниченной степени), поэтому я могу добавлять индексы, индексированные представления и т. Д. Или даже дополнительные таблицы в базу данных.

Я был бы очень признателен за любую помощь здесь.

Редактировать Добавлена ​​ссылка на изображение плана выполнения.

Источник

3 ответа

Решение

Зависит от ваших данных (сколько строк в группе?) И ваших индексов.

Посмотрите Оптимизацию TOP N на групповые запросы для некоторых сравнений производительности 3 подходов.

В вашем случае с миллионами строк только для небольшого количества транспортных средств, я бы добавил индекс VehicleID, Timestamp и делать

SELECT CA.*
FROM   Vehicles V
       CROSS APPLY (SELECT TOP 1 *
                    FROM   ChannelValue CV
                    WHERE  CV.VehicleID = V.VehicleID
                    ORDER  BY TimeStamp DESC) CA
Источник

Если ваши записи вставляются последовательно, заменяя TimeStamp в вашем запросе с ID может иметь значение.

Как примечание, сколько записей это возвращает? Ваша задержка может вызвать перегрузку сети, если вы получаете сотни тысяч строк назад.

Источник

Попробуй это:

SELECT SequencedChannelValue.* -- Specify only the columns you need, exclude the SequencedChannelValue
FROM
    (
        SELECT 
            ChannelValue.*,   -- Specify only the columns you need
            SeqValue = ROW_NUMBER() OVER(PARTITION BY VehicleID ORDER BY TimeStamp DESC)
        FROM ChannelValue
    ) AS SequencedChannelValue
WHERE SequencedChannelValue.SeqValue = 1

Ожидается сканирование таблицы или индекса, потому что вы никоим образом не фильтруете данные. Вы запрашиваете последнюю метку времени для всех идентификаторов транспортных средств - механизм запросов ДОЛЖЕН просмотреть каждую строку, чтобы найти последнюю метку времени.

Вы можете помочь, сузив число возвращаемых столбцов (не используйте SELECT *), и предоставив индекс, который состоит из VehicleID + TimeStamp.

Источник
Другие вопросы по тегам