T-SQL Лучшие Методы - не использовать Функции Скалярного значения в Списке столбцов или в секции WHERE

Tsql теория > T-SQL Лучшие Методы - не использовать Функции Скалярного значения в Списке столбцов или в секции WHERE
02.04.2013 18:10:12



Статья:

T-SQL Best Practices - Don't Use Scalar Value Functions in Column List or WHERE Clauses

T-SQL Лучшие Методы - не использовать Функции Скалярного значения в Списке столбцов или в секции WHERE

Удостоверяясь Ваши запросы, выполненные с такой скоростью, как возможный, важны, чтобы гарантировать, что Ваше приложение получает вид производительности, которой пользователи требуют или ожидают. На производительность запроса можно влиять партиями различных факторов. В этой статье я буду представлять другую передовую практику того, как и как не использовать функцию в пределах Ваших операторов T-SQL. То, куда Вы помещаете свою функцию в пределах Ваших операторов T-SQL, определяет, как Ваш запрос будет обработан и может строго воздействовать на план выполнения запроса и производительность Вашего запроса.
Типы Функций
Есть различные типы функций. Не все функции создаются равные. Некоторые функции читают данные, в то время как другие не делают. Некоторые функции возвращают многократные записи, в то время как другие возвращают только единственную запись. С целью этой статьи я буду обсуждать использование функций скалярного значения и как они влияют на производительность Вашего запроса.
Я уверен, что большинство из Вас уже знает, какова функция скалярного значения. Но на всякий случай Вы не делаете я предоставлю Вам определение, таким образом, мы будем всеми на той же самой странице. Функция скалярного значения является функцией, которая возвращает единственное значение.
Имейте в виду не, все скалярные функции создаются равные. Некоторое скалярное значение функционирует справочные данные в таблицах, где другие не делают. В цели эта статья мы будем говорить о функциях скалярного значения, которые принимают входные параметры, и затем используют те параметры, чтобы считать некоторые данные из Вашей базы данных, и возвратить значение назад вызову оператор T-SQL, который ссылался на функцию.
за и против  При использовании Скалярных функций
Есть, вероятно, много за\против связанный со скалярными функциями. Я собираюсь только обсудить единственный аспект использования функций скалярного значения. В этой статье я буду обсуждать, как Вы ссылаетесь на свою функцию скалярного значения в пределах оператора SELECT T-SQL. Размещение функции будет влиять, как запрос обрабатывается и как это может привести к плохим запросам выполнения или хорошо выполнению запросов.
Можно поместить функцию скалярного значения во многие различные места в пределах единственного оператора SELECT. С целью этой статьи я буду обсуждать воздействия размещения функции скалярного значения в пределах ИЗБРАННОГО списка столбцов и/или WHERE секция. Я буду тогда показывать Вам альтернативы для того, чтобы использовать скалярную функцию в этих расположениях.

Чтобы демонстрировать, как размещение скалярного значения функционирует в списке столбцов и в пределах, where пункт является плохой практикой, давайте смотреть на несколько примеров кодирования. Вот первый оператор SELECT, который рассмотрим:

USE AdventureWorks
GO

CREATE FUNCTION dbo.fn_GetName 
     (
       @CustomerID INT
      ) 
RETURNS VARCHAR(100)
AS
BEGIN
  DECLARE @CustomerName VARCHAR(100);
  SELECT @CustomerName =  PC.LastName + ', ' + PC.FirstName
  FROM Sales.Customer SC
  JOIN Sales.Individual SI
  ON SC.CustomerID = SI.CustomerID
  JOIN Person.Contact PC
  ON SI.ContactID = PC.ContactID 
  WHERE SC.CustomerID = @CustomerID
  RETURN @CustomerName
END
GO

SELECT dbo.fn_GetName(CustomerID) 
      ,CustomerType
FROM Sales.Customer
WHERE dbo.fn_GetName(CustomerID) IS NOT NULL
GO

Если Вы смотрите на этот код, можно видеть, что я создаю функцию, которая принимает CustomerID и затем возвращает LastName и FirstName, связанный вместе с запятой промежуточный фамилия и имя клиента. Теперь смотрите на оператор SELECT и отметьте, что я вызываю функцию изнутри списка столбцов.
Если Вы выполните первый блок кода, который создает функцию в одном пакете и чем выполненный оператор SELECT в отдельном пакете, то Вы будете видеть, что оператор SELECT берет некоторое время, чтобы работать. На моей машине потребовалось приблизительно 10 секунд. Чтобы идентифицировать, почему этот оператор SELECT занимает много времени и почему я считаю это плохой практикой, у Вас должен также быть Профилировщик, работающий и следящий за развитием событий SP:COMPLETED И SQL:BATCHCOMPLETED, в то время как Вы выполняете этот запрос. Вот экранная печать моего сеанса Профилировщика для заключительной части обработки, связанной с вышеупомянутым оператором SELECT.

Если Вы смотрите на этот вывод, можно видеть, что оператор SELECT выполняется много раз. Помещая вызов функции в список столбцов и в, WHERE clause, функция вызывается многократно. Можно видеть это выше, отмечая все различные события SP:Completed. Кроме того, отметьте ЦП, Чтения, Запись и Продолжительность на строке EventClass SQL_BatchCompleted у основания этого снимка экрана. Это событие получает истинную стоимость выполнения этого запроса. Помните эти числа. Позже я покажу Вам, как записать этот запрос по-другому, чтобы сделать это более эффективным.

Теперь смотрите на этот код:

SELECT PC.LastName + ', ' + PC.FirstName [Customer Name]
      ,SC.CustomerType
FROM Sales.Customer SC
JOIN Sales.Individual SI
ON SC.CustomerID = SI.CustomerID
JOIN Person.Contact PC
ON SI.ContactID = PC.ContactID 
WHERE dbo.fn_GetName(SC.CustomerID) = 'Roy, Luke'

Здесь я вызываю ту же самую функцию, но на сей раз только в WHERE clause. Если Вы выполните этот код, то Вы будете видеть, что он требует времени к данным возврата. В моем случае потребовалось 3 секунды. Еще раз, если мы контролируем эту команду с Профилировщиком, мы видим следующие события трассировки:

Еще раз, смотря на этот вывод можно видеть, что оператор SELECT, кажется, выполняется много раз. Это происходит, потому что функция fn_GetName еще раз должна быть выполнена многократно. Все это дополнительное выполнение этой функции заставляет этот запрос быть очень неэффективным. Финал накапливался, стоимость для этого запроса еще раз может быть отмечена, смотря на ЦП, Чтения, Записи, и столбцы Duration на событии SQL:BatchCompleted у основания Профилировщика выводят.
Есть много различных вариантов для того, чтобы переписать этот запрос, как использование логики СОЕДИНЕНИЯ JOIN, встроенной функции табличного значения или представления. Я пройду через каждую из этих опций и сравню производительность каждого из них против исходного запроса выше.
Во-первых, я собираюсь переписать первый запрос выше использования логики СОЕДИНЕНИЯ JOIN. Вот мой новый код, используя метод JOIN:

SELECT PC.LastName + ', ' + PC.FirstName [Customer Name]
      ,SC.CustomerType
FROM Sales.Customer SC
JOIN Sales.Individual SI
ON SC.CustomerID = SI.CustomerID
JOIN Person.Contact PC
ON SI.ContactID = PC.ContactID 
GO

В этом коде я теперь взял таблицы, используемые в функции, и принес им в ИЗ пункта. Я также заменил вызов функции в списке столбцов, и теперь только вычисляю столбец “Customer Name”, связывая двух Людей. Свяжитесь со столбцами непосредственно в списке столбцов. Профилировщик выводил ниже, показывает статистику выполнения для этой перезаписи:

Смотря на вышеупомянутый вывод, можно видеть, что моей перезаписи только вывели на экран одно событие в Профиле. Кроме того, смотря на ЦП, Чтения, Записи, и столбцы Duration можно видеть, что эта перезапись использовала значительно меньше ресурсов. Если мы только будем смотреть на сбережения ввода-вывода этой перезаписи по исходному запросу, то Вы будете видеть, что я сохранил 130 983 ввода-вывода. Это - существенные сбережения.

Для следующей перезаписи я возьму свою исходную функцию скалярного значения и превращу это во встроенную функцию табличного значения. Я тогда буду использовать эту встроенную функцию табличного значения в переписанном операторе SELECT T-SQL, который использует CROSS APPLY оператор. Во-первых, давайте рассмотрим встроенную функцию табличного значения:

CREATE FUNCTION fn_GetNameTable(@CustomerID int)
RETURNS TABLE
AS 
RETURN (
  SELECT  LastName + ', ' +  FirstName [Customer Name]
  FROM Sales.Customer SC
  JOIN Sales.Individual SI
  ON SC.CustomerID = SI.CustomerID
  JOIN Person.Contact PC
  ON SI.ContactID = PC.ContactID 
  WHERE SC.CustomerID = @CustomerID
 )

Этот код является почти точно тем же самым как моим исходным функциональным кодом. Единственной разницей здесь является эта функция, теперь возвращает ТАБЛИЦУ вместо столбца VARCHAR. Чтобы использовать это новое встроенное табличное значение функционируют, я должен буду теперь использовать КРЕСТ, ПРИМЕНЯЮТ оператор. Вот мой новый T-SQL SELECT statemen:

SELECT I.[Customer Name]
      ,SC.CustomerType
FROM Sales.Customer SC
CROSS APPLY fn_GetNameTable(SC.CustomerID) I

Здесь я ссылался на свою новую функцию в пределах ОТ части оператора. Я использую CROSS оператор, чтобы более или менее присоединиться к продажам. Информация о клиенте с результатами моей функции табличного значения, основанной на CustomerID. Моя функция fn_GetNameTable оценивается с каждым CustomerID от продаж. Потребительская таблица. Когда функция возвращает "Имя клиента", к ней присоединяются со столбцом “CustomerType” от продаж. Потребительская таблица, чтобы произвести окончательный результат. Давайте смотреть на то, насколько эффективный этот метод перезаписи, рассматривая вывод Профилировщика:

Здесь можно видеть, что опция CROSS JOIN намного более эффективна тогда использование вызова функции в пределах списка столбцов. Число ввода-вывода использовало при использовании CROSS JOIN , то же самое как мой предшествующий пример, используя опцию JOIN. Однако, этот пример использует немного меньше ЦП.

Для последнего примера перезаписи моего оригинала плохо запрос выполнения я буду использовать представление. Вот код T-SQL для моего представления:

CREATE VIEW vw_GetName
AS
  SELECT LastName + ', ' + FirstName [Customer Name]
        ,SC.CustomerID 
  FROM Sales.Customer SC
  JOIN Sales.Individual SI
  ON SC.CustomerID = SI.CustomerID
  JOIN Person.Contact PC
  ON SI.ContactID = PC.ContactID
GO

Представление здесь выглядит подобным коду для функции. Этот код объединяется продажи. Информация о клиенте с Человеком. Контактная информация, так, чтобы поле “Customer Name” могло быть создано. Вот код, который использует это представление, чтобы возвратить тот же самый официальный набор документов как мой исходный пример..

-- Rewrite using View  
SELECT V.[Customer Name] 
      ,CustomerType
FROM Sales.Customer SC
JOIN vw_GetName V
ON SC.CustomerID = V.CustomerID
GO

Если мы смотрим на вывод Профилировщика ниже, мы можем определить, насколько эффективный этот метод кодирования по сравнению с исходными, опциями JOIN И CROSS JOIN. Вот вывод Профилировщика для этого примера:

Еще раз использование представления намного более эффективно чем исходный код. Здесь мое представление потребовало того же самого числа ввода-вывода как опция JOIN И CROSS JOIN. Представление использует немного больше ЦП чем другие две опции перезаписи.

Заключение:

Вы должны быть осторожными, как Вы используете скалярную функцию в своих операторах T-SQL. Скалярное значение функционирует когда использующийся в списке столбцов, или WHERE clause выполняет много как курсор и вызывается неоднократно, чтобы разрешить запрос.