Уменьшаем объем кода и обмена с сервером

Table-valued parameters, TVP – это долгожданное решение для специфичного недостатка SQL Server, досаждавшего разработчикам: в SQL Server нет встроенного способа передать несколько записей как один параметр хранимой процедуре или параметризованному запросу.

Это кажущееся небольшим ограничение было большой головной болью для разработчиков. Обходов этой проблемы множество. Некоторые проверенные решения включают создание строк с разделителями, динамическое создание SQL-выражений и/или выполнение добавочных обменов с сервером. В последнее время разработчики начали использовать XML для отправки нескольких значений как одного параметра хранимой процедуры.

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

TVP все меняют. TVP предоставляют встроенный механизм для отправки нескольких строк данных как одного параметра хранимой процедуре или параметризованному запросу. И делают они это, не требуя особой логики или лишних обменов. С помощью TVP можно предавать множественные строки данных, используя DataTable, DbDataReader или IList<SqlDataRecord>, так же, как INT или NVARCHAR-параметр.

TVP – это новый тип данных в SQL Server 2008. TVP основаны на другой новой возможности SQL Server 2008, User-Defined Table Types (UDTTs). Полное обсуждение UDTT выходит за рамки этой статьи. Все, что нужно знать об UDTT для понимания сути TVP, – то, что они являются пользовательским типом, объявленным как таблица. В общем, TVP – это просто параметры, чей тип данных – это объявленная пользователем таблица. Можно думать о TVP как о массивах, которые можно передавать как параметры хранимых процедур.

У TVP есть несколько достоинств. Во-первых, для доступа к значениям колонок можно использовать стандартные T-SQL-выражения SELECT. Во-вторых, TVP строго типизированы. В-третьих, размер TVP ограничен только количеством памяти на сервере. В-четвертых, колонки определяемой пользователем таблицы можно индексировать.

У TVP тоже есть свои ограничения, но они минимальны, учитывая область применения. В частности, TVP доступны только для чтения. Вы не можете вставлять, изменять или удалять записи в TVP из тела хранимой процедуры, и должны при объявлении параметра использовать ключевое слово READONLY. TVP предназначены только для ввода. Их нельзя объявить как OUTPUT-параметры. Кроме того, их структуру нельзя изменять с помощью ALTER TABLE.

Жизнь до TVP

Давайте посмотрим на ситуацию, существовавшую до появления TVP. Предположим, что нужно создать приложение, которое должно выводить заказы в ListBox на форме. Форма позволяет выполнять поиск заказов, выбирая одну или несколько территорий из ListBox-а (рисунок 1).

Сейчас типичным решением для реализации этих требований является создание на клиенте строки с разделителями, включающей ID и названия всех выбранных территорий, и передача этой строки серверу как параметра хранимой процедуры (см. листинг 1).

Листинг 1: C#: Создание строки с разделителями на клиенте

Создание строки с разделителями – довольно тривиальная задача, если использовать объект StringBuilder. Код просто перебирает территории, перечисленные в ListBox, и создает строку, используя "|" как разделитель для значений колонок, и "@@" – для строк. Строка с разделителями затем передается хранимой процедуре как VARCHAR-параметр. Результаты поиска возвращаются из хранимой процедуры как DataSet и привязываются к сетке на форме.

private void btnGetOrdersDelimited_Click(object sender, EventArgs e)
{
   Cursor.Current = Cursors.WaitCursor;

   StringBuilder sb = new StringBuilder();
   ApplicationData.TerritoryDS.TerritoryRow 
   territory;

   // создание строки с разделителями, содержащей
   // выбранные территории для передачи в 
   // качестве параметра хранимой процедуры. Колонки
   // разделяются "|", а строки - "@@"
   
   for (int i = 0; i <       
   this.lstTerritory.SelectedIndices.Count; i++)
   {
     territory = 
       this._territoryDS.Territory[this.lstTerritory.SelectedIndices[i]];
     sb.Append(territory.ID 
       + "|" + territory.TerritoryName + "@@");
   }

   OrdersDS ds = 
      BusinessLayer.BusinessServices.GetOrders(
      sb.ToString(), DemoForm.databaseConnection);

   this.BindGrid(ds);

   Cursor.Current = Cursors.Default;
}

Создав строку с разделителями, вы передаете ее хранимой процедуре up_GetOrdersByTerriory_Delimited, используя SqlParameter-объект @territories, определенный как VARCHAR:

Клиентский код прямолинеен, но этого не скажешь о T-SQL-коде, нужном для разбора строкового параметра с разделителями (см. Листинг 2). Это решение работает, но оно страдает от множества недостатков, присущих строкам с разделителями. Первая проблема в том, что около 75% T-SQL в процедуре не имеет другой цели, кроме разбора параметра со списком территорий и сохранения значений в табличной переменной. Для простой передачи параметра это слишком много. Вторая проблема в том, что это решение использует табличную переменную, объявленную внутри процедуры, для хранения разобранных строковых значений. Поскольку область видимости табличной переменной ограничена хранимой процедурой, ее не могут повторно использовать другие объекты БД. Третья проблема связана с передачей всех значений в виде одной строки. Такая передача значений превращает все значения в строковые; статическая типизация значений теряется. ID приходят на сервер в виде типа VARCHAR. Чтобы объединить таблицу Orders с табличной переменной, ID придется привести к типу INT при вставке в табличную переменную. Кроме того, если передавать символы, например, "XYZ", вместо цифрового значения ID, приложение успешно скомпилируется, но рухнет при исполнении. Наконец, возможны ошибки, если знак разделителя встретится в одном из разбираемых значений, или если длина строки окажется больше, чем определенная длина параметра.

Листинг 2. SQL?Server: Разбор строки @territories.

На сервере строка @territories разбирается на строки делением по разделителям строк ("@@"). Каждая строка затем разбирается на колонки делением по "|". Вся логика разбора выполняется в двух вложенных циклах WHILE. Результирующие значения хранятся в виде строк в табличной переменной. Табличная переменная используется для фильтрации подходящих записей из таблицы Orders. Она соединяется с таблицей Orders по полю ID, и возвращает только те заказы, чьи TerritoryId соответствуют территориям в табличной переменной. Пример кода отправляет хранимой процедуре как ID, так и имя территории. Это сделано, чтобы продемонстрировать, насколько сложным может стать серверный код при использовании в качестве решении проблемы строк с разделителями. Сложность возрастает, поскольку для разбора каждой строки и колонок в ней нужно несколько циклов. Кроме того, приходится отслеживать множество позиций разделителей строк и колонок.

CREATE procedure [dbo].[
   up_GetOrdersByTerritory_Delimited] (
   @territories varchar(max)
)

-- TEST: up_GetOrdersByTerritory_Delimited
--  '1|Northwest@@2|Northeast@@4|Southwest@@'
AS

DECLARE @territoryList TABLE (
   ID int,
   TerritoryName varchar(50)
)

BEGIN
      
   SET NOCOUNT ON
   
   DECLARE @columnDelimiter char(1) = '|'
   DECLARE @rowDelimiter char(2) = '@@'
   DECLARE @columnDelimiterPosition int = 0
   DECLARE @previousColumnDelimiterPosition int = 0
   DECLARE @rowDelimiterPosition bigint = 0
   DECLARE @previousRowDelimiterPosition bigint = 0
   DECLARE @rowValues varchar(100)

   SELECT @rowDelimiterPosition = 
          CHARINDEX(@rowDelimiter, @territories, 
          @rowDelimiterPosition)

   WHILE (@rowDelimiterPosition > 0)
   BEGIN

         SET   @rowValues = SUBSTRING(@territories, 
               @previousRowDelimiterPosition, 
               @rowDelimiterPosition - 
               @previousRowDelimiterPosition)
         SET   @columnDelimiterPosition = 
               CHARINDEX(@columnDelimiter, 
               @rowValues, 
               @previousColumnDelimiterPosition)

         WHILE (@columnDelimiterPosition > 0)
         BEGIN

            -- parse the string into indivual territory id and 
            -- name values. Insert each row into the 
            -- @territoryList table variable
            INSERT INTO @territoryList(ID, TerritoryName)
            SELECT  CAST(SUBSTRING(@rowValues, 
                  @previousColumnDelimiterPosition, 
                  @columnDelimiterPosition - 
                  @previousColumnDelimiterPosition) as int),
                  SUBSTRING(@rowValues,
                  @columnDelimiterPosition + 1, 
                  @rowDelimiterPosition - 
                  @previousColumnDelimiterPosition)

            SET    @previousColumnDelimiterPosition = 
                  @columnDelimiterPosition + 1
            SET   @columnDelimiterPosition = 
                  CHARINDEX(@columnDelimiter, 
                  @rowValues, 
                  @previousColumnDelimiterPosition)
         END
   
         SET    @previousColumnDelimiterPosition = 0
         SET    @previousRowDelimiterPosition = 
            @rowDelimiterPosition + 2
         SET    @rowDelimiterPosition = 
               CHARINDEX(@rowDelimiter, 
               @territories, 
               @previousRowDelimiterPosition)


   END

   -- return result set filtered by selected territories
   SELECT o.OrderDate, o.SalesOrderNumber, 
         o.TotalDue, o.SubTotal, o.TaxAmt,
         t.TerritoryName, o.Status, o.ShipDate, 
         o.AccountNumber
   FROM Sales.SalesOrderHeader o
         INNER JOIN @territoryList t ON 
         o.TerritoryID = t.ID

   SET NOCOUNT OFF

END

Решаем проблемы SQL с помощью TVP

Потенциал TVP раскрывается при их применении в сценариях, подобных описанному ниже. TVP-решение решает все проблемы подхода с использованием строк с разделителями.

Первый шаг при использовании TVP – создание UDTT. Это UDTT служит основой для TVP. Синтаксис создания UDTT очень похож на синтаксис создания обычной таблицы или табличной переменной:

Такие UDTT, как TerritoryTableType, способствуют строгой типизации. Значения, вводимые в колонку ID, имеют тип INT, а значения TerritoryName имеют тип NVARCHAR. Это снимает необходимость конвертации типов данных при соединении с таблицей Orders. Кроме того, UDTT видны любому объекту БД, что позволяет использовать их в БД многократно.

Определив тип, вы можете создавать переменные и параметры этого типа. Хранимая процедура up_GetOrdersByTerritory_TVP возвращает тот же набор результатов, что и up_GetOrdersByTerritory_Delimited; но при этом она использует TVP:

Первое, что вы должны были заметить в параметре @territoryList – он объявлен как TerritoryTableType. Параметр @territoryList теперь строго типизирован и принимает множественные строки данных.

О параметре @territoryList нужно сказать еще, что он обзавелся атрибутом READONLY. В февральском СTP SQL Server 2008 TVP нельзя обновлять. Это ограничение может быть снять в финальной версии SQL Server 2008. Однако эта статья написана на основе CTP-версии, так что хранимая процедура не скомпилируется, если не включить атрибут READONLY.

CREATE PROCEDURE up_GetOrdersByTerritory_TVP(
  @territoryList TerritoryTableType READONLY)

AS

BEGIN
  SET NOCOUNT ON

  SELECT o.OrderDate, o.SalesOrderNumber, 
      o.TotalDue, o.SubTotal, o.TaxAmt,
      t.TerritoryName, o.Status, o.ShipDate, 
      o.AccountNumber
    FROM Sales.SalesOrderHeader o
      INNER JOIN @territoryList t 
        ON o.TerritoryID = t.ID
  
  SET NOCOUNT OFF
END

Обратите внимание, насколько она короче, чем up_GetOrdersByTerritory_Delimited. Код сокращен полностью за счет исключения логики разбора строки с разделителями. После удаления логики разбора процедура соединяет параметр @territoryList с таблицей Orders напрямую по колонке ID. Выполнение хранимой процедуры с TVP требует некоторых изменений в ADO.NET-коде для передачи процедуре значений.

Метод GetSelectedTerritoriesForTVP весьма похож на клиентский код, использованный в примере со строкой с разделителями.

private TerritoryDS.TerritoryDataTable 
   GetSelectedTerritoriesForTVP()
{
   TerritoryDS.TerritoryDataTable 
      functionResult = new 
      TerritoryDS.TerritoryDataTable();
   TerritoryDS.TerritoryRow territory;

   // создаем таблицу, содержащую выбранные 
   // территории, для передачи в виде параметра хранимой процедуры

   for (int i = 0; i < 
      this.lstTerritory.SelectedIndices.Count; i++)
   {
      territory = this._territoryDS.Territory[
         this.lstTerritory.SelectedIndices[i]];
      functionResult.AddTerritoryRow(
         territory.ID, territory.TerritoryName);
   }

   return functionResult;
}

Клиент так же перебирает выбранные территории, но вместо создания строки с разделителями код вставляет выбранные значения как DataRows в новый экземпляр строго типизированной DataTable, чья схема соответствует схеме TerritoryTableType:

TerritoryDS.TerritoryDataTable functionResult 
   = new TerritoryDS.TerritoryDataTable();
TerritoryDS.TerritoryRow territory;

for (int i = 0; i < 
   this.lstTerritory.SelectedIndices.Count; i++)
{
   territory = this._territoryDS.Territory[
      this.lstTerritory.SelectedIndices[i]];
   functionResult.AddTerritoryRow(
      territory.ID, territory.TerritoryName);
}

Теперь рассмотрим детали вызова хранимой процедуры up_GetOrdersByTerritory_TVP с использованием обычных объектов ADO.NET:

public static OrdersDS GetOrdersWithTVP(
   string connectionString, DataTable territoryList)
   {
   OrdersDS functionResult = new OrdersDS();
   SqlDataAdapter da = new SqlDataAdapter(
      "up_GetOrdersByTerritory_TVP", 
   new SqlConnection(connectionString));

   da.SelectCommand.CommandType = 
      CommandType.StoredProcedure;
   da.TableMappings.Add(
      "Table", functionResult.Orders.TableName);
   da.SelectCommand.Parameters.AddWithValue(
      "@territoryList", territoryList);
   da.SelectCommand.Parameters[0].SqlDbType = 
      SqlDbType.Structured;
   da.Fill(functionResult);

   return functionResult;
}

Вы добавляете объект SqlParameter в SqlCommand с помощью метода AddWithValue. Вызов AddWithValue задает значение параметра @territoryList, передавая ему экземпляр строго типизированного DataTable:

Свойство SqlDbType объекта SqlParameter, представляющего параметр @territoryList, должно иметь значение SqlDbType.Structured. «Structured» – это новое значение в перечислении SqlDbType, появившееся в .NET Framework 3.5. Оно указывает, что в TVP содержатся структурированные данные. Если SqlDbType имеет значение Structured, хранимая процедура ожидает, что параметр – это TVP.

Увеличение производительности приложений

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

Пример приложения позволяет сохранять критерии поиска для повторного использования. Пользователи вводят описание поиска в текстовое окно Saved Search Description. Критерии поиска сохраняются щелчком по кнопке Save Search Criteria. Выбранные территории сохраняются в таблицу БД. Пользователи могут вызвать и использовать сохраненный сеанс поиска, выбрав сохраненные критерии поиска из списка и нажав кнопку Run Saved Search. Распространенная методика сохранения критериев поиска заключается в переборе выбранных территорий и вставке территорий по одной за раз, с одним вызовом хранимой процедуры на одну вставляемую строку. Это неэффективно, поскольку при каждом вызове хранимой процедуры выполняется обмен данными между сервером и клиентом. Гораздо эффективнее использовать TVP для вставки всех выбранных территорий за один вызов сервера.

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

CREATE PROCEDURE 
   up_SaveTerritoryMRU (
   @territoryList TerritoryTableType 
      READONLY,
   @searchDate datetime,
   @searchDescription nvarchar(50)
)

AS

DECLARE @newTerritorySearchHistoryID int

SET NOCOUNT ON

-- вставка мастер-записи
INSERT INTO Sales.TerritorySearchHistory (
   Description, SearchDate)
VALUES (@searchDescription, @searchDate)

SET @newTerritorySearchHistoryID 
   = SCOPE_IDENTITY()

-- вставка наиболее часто используемых 
-- при поиске территорий, по ID
INSERT INTO 

Sales.TerritorySearchHistoryDetails(
   TerritorySearchHistoryID, TerritoryID,
   TerritoryName)
SELECT @newTerritorySearchHistoryID, 
   t.ID, t.TerritoryName
FROM   @territoryList t

SET NOCOUNT OFF

Как и процедура up_GetSelectedTerritories_TVP, up_SaveTerritoryMRU принимает параметр TerritoryTableType с именем @territoryList. Как уже говорилось, прелесть UDTT, подобных TerritoryTableType, состоит в возможности повторного использования. Нет нужды создавать новый тип, если он уже был определен; можно просто использовать созданный ранее. up_SaveTerritoryMRU вставляет записи, выбирая их из TVP @territoryList:

Клиент сохраняет выбранные территории в строго типизированном DataTable и передает его процедуре up_SaveTerritoryMRU вместе с другими параметрами хранимой процедуры. Такое использование TVP сокращает число обменов с сервером, упаковывая все выбранные территории в DataTable и выполняя один вызов хранимой поцедуры.

Взгляд на данные, предоставляемые SQL Server Profiler, показывает, как ADO.NET исполняет процедуру up_SaveTerritoryMRU. Profiler показывает, что для выполнения процедуры делается один вызов. В этом вызове создается и заполняется значениями, передаваемыми параметру @territoryList с помощью серии выражений INSERT, экземпляр TerritoryTableType:

Заключение

TVP в SQL Server – это простая возможность, решающая проблемы, с которыми разработчики сталкиваются с самых ранних дней БД-программирования – передачу нескольких значений в одном параметре хранимой процедуры.

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

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

Технология Клиент-Сервер 2008'3