Cross apply sql: CROSS APPLY operator | Interactive tutorial on SQL

Что такое SQL CROSS APPLY? Руководство по оператору T-SQL APPLY

Введенный Microsoft в SQL Server 2005, SQL CROSS APPLY позволяет передавать значения из таблицы или представления в определяемую пользователем функцию или подзапрос. В этом руководстве будет рассказано о невероятно полезном и гибком операторе APPLY, например о том, как работают операторы CROSS APPLY и OUTER APPLY, чем они похожи на INNER и LEFT OUTER JOIN, а также приведены некоторые примеры обоих. Во всех примерах используется база данных AdventureWorks.

Позже в этой статье я также рассмотрю широко распространенную проблему производительности SQL Server, с которой я до сих пор еженедельно сталкиваюсь. Эта проблема связана с использованием оператора APPLY для определенного типа пользовательской функции. Это , так что проблематично и может привести к перегрузке tempdb, что приведет к обходу вашего экземпляра SQL Server!

Пример создания встроенной функции с табличным значением

Встроенную функцию с табличным значением (TVF) можно рассматривать как параметризованное представление, поскольку функция принимает параметры в качестве аргументов, но тело функции возвращает только один SELECT заявление.

Красота встроенного TVF заключается в том, что код внутри функции выполняется в соответствии с вызывающим оператором SQL, а не вызывается для каждой строки, передаваемой в функцию. В приведенном ниже примере передается одно значение параметра @ProductID и возвращается сумма столбца UnitPrice:

 

Обратите внимание на GROUP BY в теле функции выше. Причина, по которой я включил предложение GROUP BY, несмотря на то, что передается одно значение ProductID, заключается в том, как значения возвращаются с помощью агрегатных функций. Агрегатная функция SUM возвращает значение NULL, если в оператор SELECT не включено предложение GROUP BY. Поскольку NULL является значением, хотя и НЕИЗВЕСТНЫМ, эти значения ProductID, переданные в функцию, все равно будут возвращать строку, даже если из таблицы SalesOrderDetail не возвращено никакого значения. При включенном предложении GROUP BY, если в таблице SalesOrderDetail не найдена строка, строка не возвращается.

Я буду продолжать ссылаться на эту таблицу, объясняя оператор CROSS APPLY. Теперь давайте посмотрим, как пример функции GetSalesByProduct вызывается с помощью CROSS APPLY.

Как работает CROSS APPLY?

Когда значение передается из таблицы или представления слева от оператора CROSS APPLY, эта строка будет включена в результаты оператора только в том случае, если вызов функции с правой стороны возвращает одно или несколько значений.

Используя CROSS APPLY, я могу передать столбец ProductID из таблицы Production.Product в функцию GetSalesByProduct, которая выводит столбец TotalSales. Использование CROSS APPLY таким образом гарантирует, что единственными возвращаемыми строками из Production.Product являются строки, связанные с ними в таблице Sales.SalesOrderDetail.

CROSS APPLY, OUTER APPLY и LEFT OUTER JOIN

В отличие от CROSS APPLY, который возвращает только строки Production.Product, связанные с таблицей Sales.SalesOrderDetail в примере, OUTER APPLY сохраняет и включает таблицу или представление в LEFT оператора OUTER APPLY в результирующем наборе. OUTER APPLY логически очень похож на LEFT OUTER JOIN, поскольку LEFT OUTER JOIN также сохраняет то, что находится слева от оператора.

Поскольку значения из этой таблицы/представления передаются в функцию или подзапрос ПРАВО оператора OUTER APPLY, если функция возвращает значение, оно будет включено в результаты. Если функция не возвращает значение для переданных ей параметров, в результатах будет возвращено значение NULL.

В выходных данных ниже значения ProductID 1, 3, 2 и 316 не имеют связанных продаж, поэтому столбец TotalSales из функции Sales.GetSalesByProduction возвращает NULL.

Использование APPLY с подзапросами

Оператор APPLY также отлично подходит для передачи значений в подзапросы. Следующий запрос передает столбец SalesOrderID из Sales.SalesOrderHeader в левой части подзапроса, суммирующего столбцы UnitPrice и OrderQty из Sales.SalesOrderDetail. Способ, которым это достигается за кулисами, подобен циклу FOR, где каждое значение SalesOrderID из Sales. SalesOrderHeader передается в подзапрос по одному. Это реализовано за кулисами как соединение NESTED LOOP.

Из выходных данных STATISTICS IO видно, что для таблицы Sales.SalesOrderDetail генерируется большое количество операций ввода-вывода.

Причина этого в том, что для каждой записи, переданной в функцию из Sales.SalesOrderHeader, выполняется операция поиска для этого значения в таблице Sales.SalesOrderDetail, как показано в плане запроса ниже.

CROSS APPLY лучше, чем INNER JOIN?

Хотя операторы APPLY имеют схожую логику с соединениями, использование APPLY не всегда является лучшим способом наиболее эффективного написания запроса.

В приведенном ниже запросе на соединение я переписал приведенный выше запрос CROSS APPLY, чтобы использовать предложение соединения с подзапросом в таблице SalesOrderDetail. Оптимизатор запросов замечает, что подзапрос не нужен, и вместо этого экстраполирует запрос как простое ВНУТРЕННЕЕ СОЕДИНЕНИЕ, где условием соединения является, по сути, предложение where из подзапроса выше. Это приводит к гораздо более эффективному плану выполнения, поскольку оптимизатор может использовать СОЕДИНЕНИЕ СЛИЯНИЕМ, а не вынужденное использование ВНУТРЕННЕГО СОЕДИНЕНИЯ, как в случае запроса CROSS APPLY выше.

Из выходных данных STATISTICS IO и плана запроса видно, что запрос был возвращен, просто просмотрев обе таблицы и используя оператор MERGE JOIN для объединения наборов результатов.

Дополнительные сведения об использовании объединений можно найти в нашем подробном руководстве по типам JOIN в SQL Server.

Распространенные проблемы с производительностью при использовании APPLY с табличными функциями с несколькими операторами

Оператор APPLY также используется для передачи значений из таблицы или представления в табличную функцию с несколькими операторами (MSTVF). MSTVF принимает ноль или более значений параметров точно так же, как встроенный TVF. Однако он также позволяет вам вставлять, обновлять и удалять значения, существующие в таблице-переменной (которая определена как часть структуры MSTVF).

Хотя утилита MSTVF может быть привлекательной, исторически эти вызовы функций могут быть абсолютным кошмаром с точки зрения производительности. Первая причина заключается в том, что для каждого значения, передаваемого в MSTVF, должно быть выделено некоторое пространство в базе данных tempdb для размещения табличной переменной (это НЕ конструкции, предназначенные только для памяти). Если в MSTVF передается много строк или функция слишком часто вызывается из нескольких подключений, это может привести к конфликту на страницах распределения SGAM и PFS в базе данных tempdb. Более поздние версии SQL Server проделали действительно хорошую работу по уменьшению этого соперничества, но вы все еще можете перегрузить базу данных tempdb, часто вызывая эти функции с избыточными данными.

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

Даже сегодня проблемы с производительностью, связанные с чрезмерным использованием MSTVF, по-прежнему являются одними из наиболее распространенных проблем, которые нам приходится решать, и это проблема с производительностью, о которой я упоминал во введении к этой статье. MSTVF привлекательны для использования из-за их гибкости, но будьте осторожны с ними. Как правило, не так уж сложно провести рефакторинг кода базы данных для использования чего-то другого, кроме MSTVF.

MSTVF и встроенные TVF

Несмотря на то, что встроенные TVF и MSTVF очень похожи по тому, как они вызываются и по результатам, которые они возвращают конечному пользователю, они не являются совместимыми объектами. Например, если я попытаюсь ИЗМЕНИТЬ встроенный TVF, созданный выше, я получу следующую синтаксическую ошибку:

Итак, я отброшу предыдущий встроенный TVF и заменю его на MSTVF.

 

MSTVF вызывается так же, как встроенный TVF — с помощью оператора APPLY. В приведенном ниже примере каждое значение из Production.Product передается в MSFTF и возвращаются все значения продукта, имеющие связанные записи, возвращенные из вызова функции.

В плане выполнения показан вызов таблицы Production.Product и вызов MSTVF GetSalesByProduct. Это серьезная проблема с просмотром вызовов MSTVF в плане выполнения, потому что вы не можете видеть, что происходит внутри MSTVF.

Кроме того, просмотр вывода STATISTICS IO не очень помогает, поскольку он не дает никакой ценной информации о MSTVF:

используйте инструмент расширенного профилировщика событий. Чтобы открыть этот инструмент, разверните инструмент XEvent Profiler в обозревателе объектов SSMS и дважды щелкните стандартный профиль, как показано ниже:

После того, как инструмент прослушивает события на сервере, я могу снова выполнить указанный выше запрос и получить результат. Здесь вы можете видеть, что логическое количество чтений из запроса составляет чуть более 369 КБ. Вау!

Небольшое изменение в использовании MSTVF может значительно увеличить количество логических операций чтения для запроса. Имейте в виду это обычное поведение, когда вы видите их в производственной среде, поскольку они так часто используются. Тем не менее, я все еще постоянно исправляю проблемы с производительностью, рефакторинг этих проблемных объектов.

Использование APPLY с коррелированными подзапросами

Одной из замечательных особенностей использования APPLY для передачи значений в подзапросы является то, что подзапрос может возвращать несколько столбцов. В следующем примере используются два коррелированных подзапроса для подсчета количества строк в таблице Sales.SalesOrderDetail и суммы UnitPrice по CarrierTrackingNumber. Таким образом, для каждого номера CarrierTracking в таблице Sales.SalesOrderDetail возвращается количество строк для каждого CarrierTrackingNumber вместе с текущим суммированием UnitPrice по CarrierTrackingNumber.

 

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

Проблема описанного выше подхода к представлению сводных значений в результирующем наборе заключается в его невероятной неэффективности. Было выполнено три сканирования таблицы Sales.SalesOrderDetail для возврата результирующего набора, что привело к более чем одному миллиону логических операций чтения, как показано ниже:

Как повысить производительность с помощью CROSS APPLY

Используя CROSS APPLY с одним подзапросом, который возвращает необходимые столбцы, я могу сократить количество логических чтений и количество обращений к таблице Sales.SalesOrderDetail.

 

Здесь я сократил количество логических операций чтения вдвое по сравнению с использованием подхода с множественными коррелированными подзапросами.

Я могу сделать это еще быстрее, переписав приведенный выше запрос так, чтобы он использовал агрегатные оконные функции SUM и COUNT. Эти встроенные функции T-SQL обеспечивают ту же функциональность, что и запрос выше, но с менее сложным программированием и большей эффективностью.

 

Количество операций логического чтения для этого запроса сократилось с почти 550 тыс. (показано выше) до чуть более 365 тыс. — ОЧЕНЬ КРУТО.

Что нужно знать при использовании SQL APPLY

Оператор APPLY позволяет передавать значения из таблицы в функции и подзапросы, возвращающие табличное значение. Используя APPLY, вы можете значительно расширить функциональность кода базы данных по сравнению с тем, что позволяет вам простой оператор соединения. Однако вы должны соблюдать осторожность при использовании оператора APPLY, так как это не всегда самый эффективный способ возврата результатов из базы данных.

Мой совет: всегда помните, насколько дорогими являются запросы, которые вы пишете, и старайтесь прилагать дополнительные усилия для оптимизации ваших запросов.

Собирая и группируя похожие запросы в централизованное представление, SolarWinds ® SQL Sentry поможет вам лучше понять их общее влияние на производительность базы данных. Вы можете узнать больше о функции SQL Sentry SSAS Top Commands здесь.

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

Пол С. Рэндал — генеральный директор SQLskills.com, которым он управляет вместе со своей женой Кимберли Л. Трипп. И Пол, и Кимберли являются широко известными и уважаемыми экспертами в мире SQL Server, и оба являются давними MVP SQL Server. Пол был редактором журнала TechNet Magazine, где он два раза в месяц писал колонку вопросов и ответов по SQL и тематические статьи. Он также провел лучшие семинары и сессии на PASS Summit и TechEd. Пол активно участвует в сообществе SQL Server, от групп пользователей до онлайн-форумов и помощи в Твиттере (@PaulRandal — проверьте тег #sqlhelp). Его популярный и широко цитируемый блог можно найти по адресу https://www.sqlskills.com/blogs/paul/, а с ним можно связаться по адресу paul@sqlskills. com.

sql server — Почему T-SQL CROSS APPLY иногда ведет себя как LEFT JOIN

спросил

Изменено
9 месяцев назад

Просмотрено
2к раз

Большая часть документации, которую я прочитал, предполагает, что CROSS APPLY действует аналогично ВНУТРЕННЕМУ СОЕДИНЕНИЮ, при котором строка включается в вывод только в том случае, если в обеих исходных таблицах есть совпадающие строки.

Однако это не всегда так, например, если вы запустите следующий SQL-запрос, результаты будут содержать 3 строки, одна из которых содержит несколько NULL из-за отсутствия строки в правой части. таблица:

 CREATE TABLE #Заказ
(
    Id int ПЕРВИЧНЫЙ КЛЮЧ
)
СОЗДАТЬ ТАБЛИЦУ #OrderItem
(
    OrderId int NOT NULL,
    Десятичная цена (18, 2) NOT NULL
)
ВСТАВЬТЕ В #Заказ
ЗНАЧЕНИЯ(1), (2), (3)
ВСТАВИТЬ В #OrderItem
ЗНАЧЕНИЯ(1, 10), (1, 20), (3100)
ВЫБИРАТЬ *
ОТ #Заказ o
ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ
(
    ВЫБЕРИТЕ СУММУ (Цена) КАК Общая Цена, СЧЕТЧИК (*) КАК Товаров, МИН (Цена) КАК Минимальная Цена
    ОТ #OrderItem
    ГДЕ OrderId = o. Id
) т
DROP TABLE #Заказ
УДАЛИТЬ ТАБЛИЦУ #OrderItem

Кто-нибудь знает, почему это так?

  • sql
  • sql-server
  • tsql
  • агрегатные функции
  • перекрестное применение

4

TL;DR;

Это происходит потому, что агрегат является скалярным агрегатом.

Существует два типа агрегатов:

Тот, который вы использовали, является скалярным агрегатом, поэтому всегда возвращается одна строка.

Чтобы получить векторный агрегат, нужно добавить GROUP BY 

 SELECT *
ОТ #Заказ o
ПЕРЕКРЕСТНОЕ ПРИМЕНЕНИЕ
(
    ВЫБЕРИТЕ СУММУ(oi.Price) КАК TotalPrice, COUNT(*) КАК Товаров, MIN(oi.Price) КАК MinPrice
    ОТ #OrderItem oi
    WHERE oi.OrderId = o.Id -- всегда указывать внутреннюю таблицу в ссылках на столбцы
    GROUP BY () -- пустой набор
-- альтернативно
    СГРУППИРОВАТЬ ПО oi. OrderId
) т

См. также эту прекрасную статью @PaulWhite: Fun with Scalar and Vector Aggregates

1

Кто-нибудь знает, почему это так?

Поскольку запрос, который вы ПРИМЕНЯЕТЕ, возвращает строку независимо от того, существуют ли совпадающие строки, поскольку это совокупный запрос.

Похоже, вы думаете, что агрегат не возвращает строк, когда нет подходящих строк. Это неверно, если нет предложения GROUP BY . Возьмем следующий бессмысленный запрос:

 SELECT COUNT(*) AS C,
       СУММ(идентификатор_объекта) КАК S,
       МАКС(ID_объекта) КАК М
ИЗ sys.tables
ГДЕ [имя]= N'sdfhjklsdgfgjklb807ty3480A645*)&TY0';

Теперь, если у вас нет очень глупого имени для одного из ваших объектов, вы все равно получите набор результатов с одним здесь:

 C S M
----------- ----------- -----------
0 НУЛЬ НУЛЬ

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