Низкая производительность в запросе UPDATE на основе SELECT TOP 1

Question

Я пытаюсь улучшить производительность запроса, который занимает много времени, и буду признателен за любые указания на то, что мне нужно сделать по-другому, чтобы довести производительность до разумного. (Указатель и примерный план выполнения приведены ниже.)

Обновляемая таблица table1 содержит менее 1000 записей, но запрашиваемая таблица находится в диапазоне 100 миллионов. Странно то, что я использую почти один и тот же запрос для самых старых и самых последних значений, но запрос для самого последнего значения возвращается через две секунды. Это запрос для самого старого значения; для запуска требуется более двух минут:

UPDATE table1
SET firstVal = (
                    SELECT TOP 1 val 
                    FROM table2 
                    WHERE table1.ID = ID
                    AND valID = 123
                    ORDER BY entryDate
                    )

Запрос для самого последнего, возвращается через две секунды:

UPDATE table1
SET lastVal = (
                    SELECT TOP 1 val 
                    FROM table2  
                    WHERE table1.ID = ID
                    AND valID = 123
                    ORDER BY entryDate DESC
                    )

Индекс:

CREATE NONCLUSTERED INDEX [table2_IX9] ON [dbo].[table2]
(
    [valID] ASC,
    [entryDate] ASC,
    [ID] ASC
)
INCLUDE (   [val]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [AB]
GO

Поиск индекса (не в кластере) Стоимость: 68%
Максимальная стоимость: 29%

Можно ли сформулировать этот запрос для лучшей оптимизации? Есть ли что-то, что мне нужно по-другому на стороне индекса? Заранее спасибо за любое руководство!

Answer 1

Это слишком долго для комментариев, но это может работать быстрее:

UPDATE T1
SET firstVal = T2.val
FROM Table1 T1
     --This might need to be an OUTER APPLY, but impossible to tell from your post
     CROSS APPLY (SELECT TOP 1 ca.val
                  FROM Table2 ca
                  WHERE ca.ID = T1.ID
                    AND ca.valID = 123
                  ORDER BY ca.entryDate) T2;

То, как вы это делаете, может означать, что запрос выполняется отдельно для каждой строки, а не как набор данных. Этот способ заставит запрос работать как набор данных.

Answer 2

Что я в итоге сделал:

SELECT ID, val, entryDate
INTO #tmpT2
FROM table2 
WHERE ID IN (SELECT ID FROM table1)
AND valID = 123

А затем с небольшими изменениями поменяйте местами #tmpT2 для table2 в исходном запросе:

UPDATE table1
SET firstVal = (
                    SELECT TOP 1 val 
                    FROM #tmpT2 
                    WHERE table1.ID = ID
                    ORDER BY entryDate
                    )

Почему: Просто осознал, что с индексированием, которое выглядит точно так, как оно должно было выглядеть, а table1 был очень маленьким, я мог просто попытаться использовать временную таблицу и извлечь все идентификаторы, vals и entryDates для valID = 123 во временную таблицу., Этот шаг занял одну секунду, и использование исходного запроса было мгновенным. Спасибо, что нашли время, чтобы обдумать эту проблему и предложить идеи!