Оптимизировать запрос с несколькими OUTER APPLY

У меня запрос с несколькими OUTER APPLY но у всех таблиц есть первичные ключи в соединенных столбцах (поэтому здесь используются кластерные индексы), поэтому я не знаю, как оптимизировать этот запрос в дальнейшем. Также здесь невозможно использовать индексированное представление, потому что ORDER BY а также TOP им запрещено.

Так что у меня

  • Таблица Fields с Id первичный ключ и большое разнообразие других столбцов.

  • WeatherHistory таблица со сложным первичным ключом (FieldId а также [Date]) и много столбцов,

  • NdviImageHistory стол с FieldId, [Date], [Base64] столбцы (сложный первичный ключ FieldId а также [Date]) где [Base64] хранит изображение base64,

  • NaturalColorImageHistory стол с FieldId, [Date], [Base64] столбцы (сложный первичный ключ FieldId а также [Date]) где [Base64] хранит изображение base64,

  • NdviHistory стол с FieldId, [Date], MeanNdvi столбцы (сложный первичный ключ FieldId а также [Date]),

  • FieldSeasonHistory стол с Field, StartDate, EndDate столбцы (сложный первичный ключ FieldId а также [Date]).

Мой запрос

SELECT Fields.*,
    WeatherHistory.TempSumC AS CurrentTempSumC,
    TempSumF AS CurrentTempSumF,
    PrecipitationSumMm AS CurrentPrecipitationSumMm,
    nih.[Base64] AS CurrentNdviImageBase64,
    ncih.[Base64] AS CurrentNaturalColorImageBase64,
    MeanNdvi AS CurrentMeanNdvi,
    IsOpenSeason
FROM Fields
LEFT JOIN WeatherHistory ON FieldId = Id AND [Date] = CAST(GETUTCDATE() AS DATE)
OUTER APPLY
(
    SELECT TOP 1 [Base64]
    FROM NdviImageHistory
    WHERE FieldId = Id
    ORDER BY [Date] DESC
) nih
OUTER APPLY
(
    SELECT TOP 1 [Base64]
    FROM NaturalColorImageHistory
    WHERE FieldId = Id
    ORDER BY [Date] DESC
) ncih
OUTER APPLY
(
    SELECT TOP 1 MeanNdvi
    FROM NdviHistory
    WHERE FieldId = Id
    ORDER BY [Date] DESC
) nh
OUTER APPLY
(
    SELECT TOP 1 CASE WHEN EndDate IS NULL THEN 1 ELSE 0 END AS IsOpenSeason
    FROM FieldSeasonHistory
    WHERE FieldId = Id
    ORDER BY [StartDate] DESC
) fsh
WHERE UserId = (SELECT Id FROM Users WHERE Email = @email) AND IsArchived = 0

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

Изменить: добавлены индексы для UserId а также IsArchived столбцы Fields Таблица. План выполнения запроса:

** Редактировать 2:** Статистика:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

(13 row(s) affected)
Table 'FieldSeasonHistory'. Scan count 13, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NdviHistory'. Scan count 13, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'NaturalColorImageHistory'. Scan count 13, logical reads 26, physical reads 0, read-ahead reads 0, lob logical reads 604, lob physical reads 0, lob read-ahead reads 0.
Table 'NdviImageHistory'. Scan count 13, logical reads 39, physical reads 0, read-ahead reads 0, lob logical reads 68, lob physical reads 0, lob read-ahead reads 0.
Table 'WeatherHistory'. Scan count 0, logical reads 39, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Users'. Scan count 0, logical reads 228, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Fields'. Scan count 1, logical reads 19, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 16 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
Источник

2 ответа

Решение

Вам нужны многоколоночные индексы для каждой из таблиц. Индекс должен быть столбцом в где, столбец в order by а затем столбец в select, Например:

  • NdviImageHistory(FieldId, [Date], [Base64])
  • NaturalColorImageHistory(FieldId, [Date], [Base64])
  • и так далее.
Источник

Обновите ваш запрос следующим образом.

SELECT Fields.*,
    WeatherHistory.TempSumC AS CurrentTempSumC,
    TempSumF AS CurrentTempSumF,
    PrecipitationSumMm AS CurrentPrecipitationSumMm,
    nih.[Base64] AS CurrentNdviImageBase64,
    ncih.[Base64] AS CurrentNaturalColorImageBase64,
    MeanNdvi AS CurrentMeanNdvi,
    IsOpenSeason
FROM Fields
LEFT JOIN WeatherHistory ON FieldId = Id AND [Date] = CAST(GETUTCDATE() AS DATE)
OUTER APPLY
(
    SELECT MAX(NdviImageHistory.ID) MAX_ID
    FROM NdviImageHistory
    WHERE FieldId = Id

) nih_ID
OUTER APPLY
(
    SELECT [Base64] FROM NdviImageHistory X WHERE X.ID = nih_ID.MAX_ID
)nih

Пользователь более внешний применить для всех этих таблиц {nih, ncih, nh, fsh } и попробуйте это.

Я использую только 1 внешний для [Nih]

Удалить TOP 1 и Order by из OUTER APPLY Joins

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