Массивы и Списки в SQL Server

Вячеслава Брылёва, aka Glory.

1. Предисловие

Часто в форумах можно видеть вопросы вида Как я могу использовать массивы в SQL сервере ? или Почему не работает запрос SELECT * FROM tbl WHERE col IN (@list) ? Данная статья описывает несколько способов решения этих вопросов как хороших, так и плохих. Также представлены данные о производительности этих способов решения.

Это достаточно длинная статья, поэтому если Вы хотите просто узнать, как оперировать списком значений, разделенных запятыми, то выберите одну из 3-х ссылок, в зависимости от версии SQL сервера, которые вы используете: SQL2000, SQL7, SQL6.5. Если предложенные там решения не подходят Вам, тогда вернитесь назаад и начните читать сначала. :-)

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

2. Постановка задачи.

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

   SQL = "SELECT ProductID, ProductName FROM Northwind..Products " & _
         "WHERE ProductID IN (" & List & ")"
   rs = cmd.Execute(SQL)

где List - это переменная, которая содержит список string значений, разделенных запятыми, например, "9, 12, 27, 39".

Однако, при использовании хранимых процедур мы видим, что нет очевидного метода для реализации такого запроса. Некоторые пытаются использовать следующую процедуру:

   CREATE PROCEDURE get_product_names @ids varchar(50) AS
      SELECT ProductID, ProductName
      FROM   Northwind..Products
      WHERE  ProductID IN (@ids)

которую потом пытаются вызвать таким орбразом:

EXEC get_product_names '9, 12, 27, 37'

но получают следующее сообщение об ошибке:

Server: Msg 245, Level 16, State 1, Procedure get_product_names, Line 2 Syntax error converting the varchar value '9, 12, 27, 37' to a column of data type int.

Это происходит потому, что в этом случае мы уже не составляем SQL запрос динамически, и @ids есть только одно из значений в предложении IN. IN предложение также может интерпретироваться как:

   ... WHERE col IN (@a, @b, @c)

Другие, сталкиваясь с этой проблемой, говорят себе: Вот если бы в T-SQL были бы массивы как в любом другом нормальном языке, то не было бы и проблемы . Но в T-SQL есть массивы. Называются они таблицами и для решения всех задач они являются более естественным выбором, чем массивы. Если все, что Вам нужно, это простой массив с единичными integer значениями в каждой ячейке, то такой массив легко эмулируется с помощью таблицы. Однако, Вы не можете передавать таблицу как параметр в хранимую процедуру. Если в одной хранимой процедуре вызыввется другая хранимая процедура, то проблемы не существует, т.к. можно для обмена данными использовать общую таблицу. Но такой способ невозможен, когда хранимая процедура запускается из клиентского приложения. НО: клиентское приложение может передать в качестве входного параметра строку, которую в SQL Вы можете "распаковать" в таблицу. Большинство методов, описанных в данной статье, используют как раз такой метод.

3. Обзор методов решения

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

Хорошие методы:

Метод итераций. Метод основан на циклической проверке строки, в которой элементы разделены запятой (a comma-separated list). Пользовательская функция или хранимая процедура могут возвращать таблицу результатов работы этого метода. Данный метод обладает приличной производительностью, лёгок для понимания и легко расширяется.
Использование вспомогательной таблицы для извлечения элементов из строки с разделителем (a comma-separated list) в пользовательской функции либо процедуре. Один из самых быстрых способ для работы со строкой с разделителями.
Массив элементов фиксированного размера. Прежде чем использовать строку с разделителем(comma-separated list), попробуйте использовать строку, в которой каждый элемент имеет одинаковую длину. Извлекать элементы можно с помощью вспомогательной таблицы или в UDF или в SP. Наиболее быстрый метод из известных мне.
XML. "Убийственный" метод для строк с разделителем, но изумительный для массивов структурированных данных. Возможен толко в SQL 2000. Приличная производительность, но меньшая чем у метода итераций.

Методы, которые лучше не использовать:

Динамический SQL. Для списка с числами этот метод может оказаться более простым чем другие, но имеет некоторые ограничения в применении. И непригоден для работы со списком string значений. Производительность метода неприемлима при для больших исходных данных. Хотя практически это единственно возможный метод в SQL 6.5.
Фокус с использованием UNION. Преобразует строку с разделителем в запрос INSERT. Или множество запросов INSERT. Милое решение, но длина исходных данных не может быть больше чем ~3000 символов. Также не обладает никакими другими специфическими достоинствами.
Действительно медленные методы. Методы, которые используют charindex, patindex или LIKE. Эти решения невероятно медленно даже для исходных данных небольшого размера.

Вы также можете ознакомиться с кратким обзором методов решения от Anith Sen по адресу http://www.bizdatasolutions.com/tsql/sqlarrays.asp. Хотя его оценки некоторых методов не всегда совпадают с моими.

4. Основные соображения по интерфейсу

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

Большинство представленных в статье методов могут быть оформлены в виде функций, возвращающих таблицу(table-valued functions) или в виде хранимых процедур. Это очень хорошо, т.к. это позволяет Вам выбрать метод решения, написать одну-две функции и затем использовать их в любом месте. Вот, например, таким образом:


       CREATE PROCEDURE get_company_names_iter @customers nvarchar(2000) AS
       SELECT C.CustomerID, C.CompanyName
       FROM   Northwind..Customers C
       JOIN   charlist_to_table(@customers) s ON C.CustomerID = s.str
    go
    EXEC get_company_names_iter 'ALFKI, BONAP, CACTU, FRANK'

В SQL7 Вы правда должны будете использовать вместо пользовательской функцииo хранимую процедуру. Вы можете ознакомиться с подбробностями такого использования здесь

SQL7.

Далее, как бы мог выглядеть интерфейс функции charlist_to_table? Входной параметер мог бы быть безразмерного типа, предпочтительно text или ntext, чтобы функция могла оперировать входными данными любого размера. Но некоторые методы решения используют функции T-SQL, которые не поддерживают типы данных text/ntext. В этом случае Вы должны использовать типы данных varchar(8000) или nvarchar(4000). Вы также можете добавить параметер, который будет определять разделитель в переданной строке отличный от запятой, или любые другие необходимые Вам параметры. Вы можете встретить примеры использования таких дополнительных параметров в данной статье.

Как бы могла выглядеть таблица с результатами работы функции ? Очевидно, что она должна включать поле, которе будет содержать значения из переданного списка элементов (мы вернемся к этому чуть ниже). Иногда Вам также может понадобиться поле, которое будет содержать порядковый номер элемента в списке. Такое поле легко возможно реализовать в одних методах и очень трудно реализовать в других.

Итак, имеется входной список элементов, но какого типа данных эти элементы? Вы можете написать функцию, в которой учитывать все возможные типы данных SQL Server. В данной же статье обсуждаются только списки integer и string элементов, потому что по моим предположениям эти типы данных используются в 99% всех случаев.

Если Вы используете список integer элементов, то Вы должны будете преобразовать их каким-то образом из типа string в тип integer, потому что входные данные являются текстовыми. Как именно Вы напишите функцию, возвращающую таблицу integer значений есть дело Вашего вкуса. Возможно Вы предпочтете такой способ:

 
      CREATE PROCEDURE get_product_names_tblnum @ids varchar(50) AS
      SELECT P.ProductName, P.ProductID
      FROM   Northwind..Products P
      JOIN   charlist_to_table(@ids) s ON P.ProductID = convert(int, i.str)
   go
   EXEC get_product_names_tblnum '9, 12, 27, 37'

Если Ваша функция возвращает список string элементов, то Вам может показаться, что нет никаких проблем с конвертацией типов. Но берегитесь! В SQL Server есть две группы текстовых типов днных: 8-битные типы данных char/varchar/text и 16-битные типы данных nchar/nvarchar/ntext для Unicode. Так какой из этих типов данных должна возвращать ваша функция ? Вам может показаться, что использование типа данных nvarchar(4000) для поля таблицы есть наилучшее решение, потому что Вы получите правильные результаты как для 8-битных так и для 16-битных типов данных. Но не торопитесь! Правильный ответ такой: если Вы связываете таблицы по полю типа char/varchar, то Ваша функция должна также возвращать тип данных varchar, а если по полю nchar/nvarchar, то, соответственно, функция должна возвращать nvarchar. И причина в следующем. В данном запросе:

   
   SELECT *
   FROM   tbl
   JOIN   charlist_to_table(@list) c ON tbl.varcharcol = c.nvarcharcol

поле tbl.varcharcol будет неявно преобразовано к типу nvarchar и это преобразование предотвратит использование SQL Server-ом любого индекса по полю tbl.varcharcol . А это в свою очередь может очень сильно сказать на производительности.

Есть два способа решить эту проблему. Первый - это написать 2 функции: одна будет возвращать тип данных varchar(8000), другая - nvarchar(4000).
Второй способ - написать одну функцию, которая принимает входной параметр типа ntext и возвращает таблицу из 2-х ролей, типа varchar и типа nvarchar соответственно. Для некотрых методов решения задачи такой подход уменьшит производительность (в пределах 5-10% для способа с двумя полями). Для других методов такой подход напротив никак не скажется на производительности. Вы можете увидеть примеры использования обеих способов в данной статье.

5. Метод итераций

Если все, что Вам нужно, это готовое решение, чтобы продолжить создание своего программного кода, то данный метод как раз для Вас. Хотя есть более быстрые методы, чем данный, но для практического подтверждения этого вам понадобятся входные данные действительно очень большого размера. Так что главный козырь этого метода - его простота. Всего делов-то - загружаете предложенные примеры функций и вперед и выше. А более быстрые методы требуют для работы наличия вспомогательной таблицы с правильными данными. Это конечно не такая чтобы уж большая проблема, но все же требует наличия еще одного объекта. Ко всему прочему программный код очень легок для понимания, что немаловажно, если у Вас есть опыт работы на C или Visual Basic. Не последнюю роль играет и то, что Вы можете адаптировать предложенный код для использования со входной строкой произвольного формата.

5.1. Список integer элементов

Вот пример пользовательской функции в in SQL 2000 для работы со списком integer элементов.


      CREATE PROCEDURE get_product_names_iter @ids varchar(50) AS
      SELECT P.ProductName, P.ProductID
      FROM   Northwind..Products P
      JOIN   iter_intlist_to_table(@ids) i ON P.ProductID = i.number
   go
   EXEC get_product_names_iter '9 12 27 37'

Оба-на, что мы видим? Это ведь не список значений разделенных запятой ? Я заметил, что для списка integer значений запятая как разделитель в действительности не играет никакой роли. Поэтому при написании функции я выбрал вместо запятой пробел в качестве разделителя. Вот код функции iter_intlist_to_table:


      CREATE FUNCTION iter_intlist_to_table (@list ntext)
      RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                          number  int NOT NULL) AS
   BEGIN
      DECLARE @pos      int,
              @textpos  int,
              @chunklen smallint,
              @str      nvarchar(4000),
              @tmpstr   nvarchar(4000),
              @leftover nvarchar(4000)

      SET @textpos = 1
      SET @leftover = ''
      WHILE @textpos <= datalength(@list) / 2
      BEGIN
         SET @chunklen = 4000 - datalength(@leftover) / 2
         SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
         SET @textpos = @textpos + @chunklen

         SET @pos = charindex(' ', @tmpstr)
         WHILE @pos > 0
         BEGIN
            SET @str = substring(@tmpstr, 1, @pos - 1)
            INSERT @tbl (number) VALUES(convert(int, @str))
            SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
            SET @pos = charindex(' ', @tmpstr)
         END

         SET @leftover = @tmpstr
      END

      IF ltrim(rtrim(@leftover)) <> ''
         INSERT @tbl (number) VALUES(convert(int, @leftover))

      RETURN
   END

Возвращаемая данной функцией таблица состоит их двух полей. number - это значение элемента списка, а listpos - это порядковый номер элемента в списке.

Т.к. функция оперирует только числовыми элементами, то Вас может удивить то, что в ней используются символьные типы данных в кодировке Unicode, т.е. ntext и nvarchar. Однако я выяснил, что при использовании данных такого типа можно увеличить производительность функции на 10% по сравнению с использованием 8-битных символьных типов данных. Я точно не знаю почему именно, но предполагаю, что это связано с тем, что функция charindex на внутреннем уровне оперирует данными в Unicode. Поэтому для конвертации 8-битных символных типов данных требуется дополнителное время.

Собственно метод итераций представляет из себя 2-хуровневый цикл. Это вызвано тем, что входной парметер у нас типа ntext, а значит он безразмерный. Но не все функции T-SQL принимают text/ntext в качестве парметров (а те функции, которые принимают, все равно не оперируют данными с длиной большей чем у varchar/nvarchar.). Поэтому я разбиваю входную строку на порции по 4000 символов и затем перехожу к основному циклу, в котором используется функция charindex для поиска символа пробела, который был выбран в качестве разделителя. Если Вы предпочитаете в качестве разделителя видеть запятую, то Вы легко можете изменить функцию. Когда внутренний цикл завершается, то в текущей порции входной строки могут еще оставаться какие-то символы, которые нужно присоединить к началу следующей порции. И в самом конце, когда обработана последняя порция, выбирается последний элемент списка.

Вы могли заметить, что я использую как функцию datalength так и len. Эти две функции обманчиво похожи друг на друга, но использовать их надо осторожно. Обе они возвращают длину строки, но datalength учитывает хвостовые пробелы, а len нет. Одноко более важно то, что только функция datalength поддерживает типы данных text/ntext. И еще: функция datalength подсчитывает длину строки в байтах, а len - в символах. Поэтому я и делю результат функции datalength на 2, а результат функции len нет.

Функция рассчитана на то, что входной параметер @list содержит только number элементы. В противном случае Вы получите сообщение об ошибке конвертации и выполнение бэтча прервется. Если Вы формируете список в клиентском приложении, то Вы должны убедиться втом, что передаете правильные данные. Но такая проверка не является слишком уж трудной задачей.Но что делать, если Вы хотите добавить проверку входных данных? Например, если Вы как DBA не очень то доверяете разработчикам клиентского приложения? Вы можете добавить в функцию проверку является ли @str в действительности числом. Но в случае ошибки Вы не сможете использовать RAISERROR, т.к. это запрещено в пользовательских функциях. Самое лучшее, что Вы можете сделать, это пропустить ошибочный элемент списка. Или добавить NULL в качестве его значения (в этом случае не забудьте разрешить использование NULL в возвращаемой таблице). Если же Вы хотите выдавать сообщение об ошибке, то Вам придется вместо пользовательской функции использовать хранимую процедуру. Вы можете найти пример такой процедуры здесь SQL7.

5.2. Список string элементов

Вот простая функция, которая возвращает таблицу со string элементами списка.


         CREATE FUNCTION iter_charlist_to_table
                    (@list      ntext,
                     @delimiter nchar(1) = N',')
         RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
                             str     varchar(4000),
                             nstr    nvarchar(2000)) AS

   BEGIN
      DECLARE @pos      int,
              @textpos  int,
              @chunklen smallint,
              @tmpstr   nvarchar(4000),
              @leftover nvarchar(4000),
              @tmpval   nvarchar(4000)

      SET @textpos = 1
      SET @leftover = ''
      WHILE @textpos <= datalength(@list) / 2
      BEGIN
         SET @chunklen = 4000 - datalength(@leftover) / 2
         SET @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
         SET @textpos = @textpos + @chunklen

         SET @pos = charindex(@delimiter, @tmpstr)

         WHILE @pos > 0
         BEGIN
            SET @tmpval = ltrim(rtrim(left(@tmpstr, charindex(@delimiter, @tmpstr) - 1)))
            INSERT @tbl (str, nstr) VALUES(@tmpval, @tmpval)
            SET @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
            SET @pos = charindex(@delimiter, @tmpstr)
         END

         SET @leftover = @tmpstr
      END

      INSERT @tbl(str, nstr) VALUES (ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)))
   RETURN
   END

А вот пример использования этой функции:


       CREATE PROCEDURE get_company_names_iter @customers nvarchar(2000) AS
       SELECT C.CustomerID, C.CompanyName
       FROM   Northwind..Customers C
       JOIN   iter_charlist_to_table(@customers, DEFAULT) s ON C.CustomerID = s.nstr
    go
    EXEC get_company_names_iter 'ALFKI, BONAP, CACTU, FRANK'

Эта функция очень похожа на функцию iter_intlist_to_table. Есть только одно различие в интерфейсе - Вы можете задать разделитель элементов в списке. Для разделителя существует значение по-умолчанию, но даже если Вам как раз и нужен такой разделитель, Вы все равно должны использовать DEFAULT при вызове функции. Потому что в T-SQL при вызове пользовательской функции должны быть явно указаны все параметры. В независимости от того, какой разделитель Вы используете, в результатах функции ведущие и хвостовые пробелы будут удалены.

Возвращаемая данной функцией таблица состоит их трех полей: listpos , str и nstr . Два последних поля содержат значение одного и того же элемента списка, первое в виде varchar, а второе - nvarchar. При объединении с другими таблицами Вы можете использовать то поле, которое лучше всего подходит Вам в данный момент. Например, для объединения по полю Northwind .. Customers . CustomerID , тип которого nchar(10), в примере было выбрано поле nstr . Не используйте поле nstr для объединения с другой таблицей по полю типа varchar, потому что тем самым вы очень сильно снизить производительность запроса! (Этот вопрос поднимался здесь Основные соображения по интерфейсу.) Вы можете возразить, что сам по себе дополнительный столбец в результатах также влияет на производительность в сторону ее ухудшения. Но при тестировании производителности я не смог обнаружить такого факта.

Одно достоинств данного метода состоит в возможности легко его расширить. Предположим, что входные данные выглядят таким образом:

"Yes, I want to", "Charlie", "Give it to me"

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

6. Метод с использованием вспомогательной таблицы

Самым быстрым методом извлечения элементов из спсика с разделителем является метод с использованием вспомогательной таблицы с числами. Это очень простая таблица, состоящая из одно поля типа integer column, которая содержит значения 1, 2, 3, ... и тд Вот наиболее легкий способ получить такую таблицу:


   SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
   INTO   Numbers
   FROM   pubs..authors t1, pubs..authors t2, pubs..authors t3

От переводчика

Т.к. база pubs имеется не на каждом сервере, то вместо таблицы authors можно использовать sysobjects


   SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
   INTO   Numbers
   FROM   sysobjects t1, sysobjects t2, sysobjects t3

[В начало]

6.1. Базовая функция

В первоначальном варианте функции для демонстрации алгоритма работы базовой функции ограничим длину входных данных 7998-ю символами. Я позаимстововал эту функцию с сайта Anith Sen-а.


   CREATE FUNCTION inline_split_me (@param varchar(7998)) RETURNS TABLE AS
   RETURN(SELECT substring(',' + @param + ',', Number + 1,
                    charindex(',', ',' + @param + ',', Number + 1) - Number - 1)
                 AS Value
          FROM   Numbers
          WHERE  Number <= len(',' + @param + ',') - 1
            AND  substring(',' + @param + ',', Number, 1) = ',')

В отличии от метода итераций, который выглядит более громоздким, но и более промолинейным, предлагаемая функция гораздо компактнее, но не все смогут сразу же ухватить суть этого SQL запроса. (Я столкнулся с такими же трудностями.) Первое, что бросается в глаза, это появление в тексте запроса выражения ',' + @param + ',' ни больше ни меньше как 4 раза. Добавляя в начало и конец входной строки разделитель, получаем то, что первый и последний элементы списка ничем не отличаются от остальных элементов.

Далее, рассмотрим условие WHERE нашего запроса. Выражение:

substring(',' + @param + ',', Number, 1) = ','

будет иметь значение TRUE для всех позиций строки, в которых содержится разделитель. А выражение:

Number <= len(',' + @param + ',') - 1

просто определяет максимальное используемое нами в запросе число.

Рассмотрим теперь формируемое нашим запросом выражение Value . Мы извлекаем из входной строки с помощью функции substring подстроку, начиная со следующего после разделителя символа. Длину извлекаемой подстроки мы определяем из результата функции charindex, с помощью которой мы ищем позицию следующего разделителя. Третий параметер функции charindex мы используем для указания позиции, с которой мы наичнаем поиск этого следующего разделителя. Если следующий разделитель найден, то мы вычитаем из номера его позиции номер позиции текущего разделителя и еще 1 (т.к. сам разделитель в результатах нам не нужен) и получаем длину текущего элемента списка.

Вот пример использования этой функции:


    CREATE PROCEDURE get_company_names_inline @customers nvarchar(2000) AS
        SELECT C.CustomerID, C.CompanyName
        FROM   Northwind..Customers C
        JOIN   inline_split_me(@customers) s ON C.CustomerID = s.Value
    go
    EXEC get_company_names_inline 'ALFKI,BONAP,CACTU,FRANK'

Заметьте, что т.к. данная функция не удаляет ведущие и хвостовые пробелы элементов списка, то входная строка не должна и содержать таких пробелов.

Данная функция является inline функцией в отличии от функции метода итераций. А inline функция по сути есть макро, поэтому оптимизатор во время построения плана выполнения просто включает текст функции в сам запрос, что делает возможным общую оптимизацию. Я правда не верю в то, что для такой специфической функции можно добиться каких-то преимуществ, сделав ее inline функцией. Функция сама по себе достоточна сложна, и я не смог обнаружить каких-либо значительных преимуществ ее inline варианта при

6.2. Входные данные неограниченного размера

Функция inline_split_me ограничивает длину входных данных 7998-ю символами (или 3998-ю для nvarchar). В таком варианте функции невозможно использовать входные данные типа text или ntext, потому что невозможно использовать параметер text/ntext в выражении ',' + @param + ','. Или, что более важно, функция charindex не может оперировать данными text/ntext, длина которых превышает максимально возможную длину для varchar/nvarchar.

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


   CREATE FUNCTION duo_text_split_me(@list  ntext,
                                     @delim nchar(1) = N',')
   RETURNS @t TABLE (str varchar(4000),
                     nstr nvarchar(2000)) AS
   BEGIN
      DECLARE @slices TABLE (slice nvarchar(4000) NOT NULL)
      DECLARE @slice nvarchar(4000),
              @textpos int,
              @maxlen int,
              @stoppos int

      SELECT @textpos = 1, @maxlen = 4000 - 2
      WHILE datalength(@list) / 2 - (@textpos - 1) >= @maxlen
      BEGIN
         SELECT @slice = substring(@list, @textpos, @maxlen)
         SELECT @stoppos = @maxlen - charindex(@delim, reverse(@slice))
         INSERT @slices (slice) VALUES (@delim + left(@slice, @stoppos) + @delim)
         SELECT @textpos = @textpos - 1 + @stoppos + 2   -- On the other side of the comma.
      END
      INSERT @slices (slice)
          VALUES (@delim + substring(@list, @textpos, @maxlen) + @delim)

      INSERT @t (str, nstr)
         SELECT str, str
         FROM   (SELECT str = ltrim(rtrim(substring(s.slice, N.Number + 1,
                        charindex(@delim, s.slice, N.Number + 1) - N.Number - 1)))
                 FROM  Numbers N
                 JOIN  @slices s ON N.Number <= len(s.slice) - 1
                                AND substring(s.slice, N.Number, 1) = @delim) AS x

      RETURN
   END

Сначала мы разобъем входные данные на порции и поместим их в таблицу-переменную @slices. В процессе такого разбиения мы должны проверять, что в текущей порции разделитель является последним символом. Для этого используем маленький фокус: для поиска разделителя передадим в функцию charindex результат функции reverse над текущей порцией. В итоге мы будем совершенно уверены в том, что все записи в таблице @slices начинаются и заканчиваются раделителем. Вы можете заметить, что если длина входных данных не будет превышать предела для типа nvarchar, то цикл по разбиению на порции не выполнится ни разу, т.к. мы сразу же добавим входные данные в таблицу @slices.

После того, как таблица @slices заполнена, мы можем выполнить основное преобразование. Замечу, что для этого нам не нужен цикл по записям таблицы @slices, мы просто связываем ее непосредственно с таблицей Numbers . Использование derived таблицы позволяет нам не повторять сложные вычисления функций substring и charindex, а также дает возможность удалить ведущие и хвостовые пробелы элементов списка. (derived таблица - это таблица, создаваемая непосредственно в ходе выполнения всего запроса. Такие таблицы очень полезны в сложных запросах. Псевдоним таблицы AS x не имеет никакого особого значения, но по правилам SQL синтаксиса любая derived таблица должна иметь псевдоним.)

Также как и iter_charlist_to_table, данная функция возвращает таблицу состоящую из 2-х столбцов - varchar и nvarchar.Однако в данном случае тестирование показало что при использовании только поля nvarchar производительность увеличивается на 3-5%. Но т.к. время выполнение измеряется в миллисекундах, то такая разница выглядит не очень существенной. Правда при сравнении производительности методов я использовал вариант функции, возвращающий только поле nvarchar.

В таблице с результатами нет поля listpos с номером элемента в списке. В данном методе получить такой номер довольно таки сложно. Один способ - это добавить в таблтцу @slices поле sliceno и поле IDENTITY в таблицу результатов. Тогда при добавлении в конечную таблицу мы можем упорядочить результ по sliceno и Number . Однако, нет полной гаратии того, что значения в поле IDENTITY будут соответсвовать порядку, заданному в ORDER BY. Более надежный способ - это связать таблицу Numbers с собой же, как показано в примере на сайте Anith Sen-а.

Теперь Вы наверное ожидаете увидеть вариант функции для работы со списком number элементов, но такого варианта в этой статье нет. Вы можете сами написать такую функцию, используя convert в нужном месте. Но можете поступить и следующим образом:


   CREATE PROCEDURE get_product_names_tblnum @ids varchar(50) AS
      SELECT P.ProductName, P.ProductID
      FROM   Northwind..Products P
      JOIN   duo_text_split_me(@ids, DEFAULT) i ON P.ProductID = convert(int, i.str)
   go
   EXEC get_product_names_tblnum '9, 12, 27, 37'

6.3. Список элементов как поле таблицы

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


    CREATE TABLE jackets (model   varchar(30),
                          sizes   varchar(200),
                          colours varchar(200))

поля sizes и colours могут содержать данные о размерах и цветах курток в виде списка элементов разделенных запятыми. Такая схема данных правда противоречит первой нормальной форме, что в большинстве случаев является результатом ошибок при проектировании. Но, оставляя эту тему в стороне, допустим, что нам нужно извлечить эти списки в отдельные таблицы. Вы, конечно, можете использовать любую из рассмотренных уже функций, но в любом случае Вы должны будете обработать каждую запись исходной таблицы в цикле, потому что невозможно передать поле таблицы в пользовательскую функцию в виде параметра. Возможно Вы уже знаете, что построчная обработка таблицы может быть значительно медленнее чем обработка таблицы как набора. Поэтому идея соединить исходную таблицу непосредственно с таблицей Numbers выглядит более лучшим решением. Я не привожу примера такой обработки непосредственно здесь, но Вы уже видели такой способ в функции duo_text_split_me, где мы применили его к таблице @slices.

7. Массив с элементами фиксированного размера

Этот метод, предложенный Steve Kass-ом (SQL Server MVP), основан на идеи, описанной Ken Henderson-ом в его книге The Guru's Guide to Transact-SQL .

Основная идея метода состоит в том, что массив определяется как список элементов фиксированного размера, а не как список элементов разделенных запятыми. Преимущество такого массива заключается в том, что разделитель отсутствует во входных данных. Но главное преимущество - это производительность. Данный метод является самым быстрым, из всех представленных в данной статье (но посмотрите приведенные далее в статье обзор потенциальных проблем). Вот пример использования данного метода непосредственно для таблицы:


   CREATE PROCEDURE get_product_names_fix @ids varchar(8000),
                                          @itemlen tinyint AS
   SELECT P.ProductID, P.ProductName
   FROM   Northwind..Products P
   JOIN   Numbers n ON P.ProductID = convert(int,
            substring(@ids, @itemlen * (n.Number - 1) + 1, @itemlen))
     AND  n.Number <= len(@ids) / @itemlen
   GO
   EXEC get_product_names_fix '   9  12  27  37', 4

Каждый элемент "массива" имеет одинаквую длину, которая задается параметром @itemlen. Для извлечения конкретного элемента мы используем функцию substring. Использованная в запросе таблица Numbers аналогична таблице, которую мы использовали в методе со вспомогательной таблицей .

Вот функция извлекающая элементы из строки.


   CREATE FUNCTION fixstring_single(@str text, @itemlen tinyint)
   RETURNS TABLE
   AS
   RETURN(SELECT listpos = n.Number,
                 str = substring(@str, @itemlen * (n.Number - 1) + 1, @itemlen)
          FROM   Numbers n
          WHERE  n.Number <= datalength(@str) / @itemlen +
                 CASE datalength(@str) % @itemlen WHEN 0 THEN 0 ELSE 1 END)

Задача выражения в последней строке - разрешить последнему элементу массива быть меньшего размера, чем остальные элементы, в случае, когда хвостовые пробелы были удалены. Вот пример использования функции fixstring_single для списка string элементов:


   CREATE PROCEDURE get_company_names_fix @customers nvarchar(2000) AS
      SELECT C.CustomerID, C.CompanyName
      FROM   Northwind..Customers C
      JOIN   fixstring_single(@customers, 6) s ON C.CustomerID = s.str
   go
   EXEC get_company_names_fix 'ALFKI BONAP CACTU FRANK'

Данная функция имеет входной параметер типа text и возвращает таблицу с полем типа varchar. Таким образом для Unicode данных Вам нужна функция-близнец, которая будет иметь входной параметер типа ntext и возвращать таблицу с полем типа nvarchar. В других методов, описанных в этой статье, я предлагал возвращать из одной функции одновременно как varchar поле так и nvarchar, но в данной функции такой подход череват большими перегрузками(потому он такой и быстрый). Снова предупрежу Вас - не используйте Unicode функции при соединении по полям типа char/varchar. Это может губительно сказаться на производительности. См. Основные соображения по интерфейсу.

Таблица результатов имеет также поле listpos , которое содержит порядковый номер элемента в массиве. Очень удобно то, что это поле есть число из таблицы Numbers .

Функция fixstring_single может извлечь из входной строки столько элементов, сколько чисел содержится в таблице Numbers . В данной статье используется таблица с 8000 номеров, что кажется вполне достаточным для большинства клиентских приложений. Если же вы хотите, чтобы функция оперировала большим числом, то просто увелить число номеров в таблице Numbers . Если же Вы хотите, чтобы функция оперировала входными данными любой длины, то Вы можете написать multi-step функцию, которая будет разбивать входную строку на порции. Есть правда другой "финт", который использует Steve Kass. Функция fixstring_multi использует самосоединение таблицы Numbers , превращая таким образом 8000 записей в 64 миллиона:


   CREATE FUNCTION fixstring_multi(@str text, @itemlen tinyint)
   RETURNS TABLE AS
   RETURN(SELECT listpos = n1.Number + m.maxnum * (n2.Number - 1),
              str = substring(@str,
                    @itemlen * (n1.Number + m.maxnum * (n2.Number - 1) - 1) + 1,
                    @itemlen)
       FROM   Numbers n1
       CROSS JOIN (SELECT maxnum = MAX(Number) FROM Numbers) AS m
       JOIN   Numbers n2 ON
              @itemlen * (n1.Number + m.maxnum * (n2.Number - 1) - 1) + 1 <=
              datalength(@str)
       WHERE  n2.Number <= datalength(@str) / (m.maxnum * @itemlen) + 1
         AND  n1.Number <= CASE WHEN datalength(@str) / @itemlen <= m.maxnum
                                THEN datalength(@str) / @itemlen +
                                     CASE datalength(@str) % @itemlen
                                          WHEN 0 THEN 0
                                          ELSE 1
                                     END
                                ELSE m.maxnum
                           END)

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

7.1. Массив элементов фиксированной длины и Метод итераций

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

7.2. Возможные проблемы с производительностью и форматом данных

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

Потенциальная проблема номер 1: Строка с элементами фиксированной длины наиболее чувствителена к формату. Один лишний символ в произвольном месте строки может привести к тому, что часть строки справа от этого символа будет "поломана". Если Вы формируете строку в клиентском приложение, то эта проблема не выглядит серьезной, т.к. решается один только раз. Но, однако, если смотреть шире, то мы переходим к

Потенциальная проблема номер 2: Стоимость программирования составления строки с элементами фиксированной длины больше чем строки с разделителем. Я не учитывал этот фактор при тестировании производительности, т.к. это сделало бы сам процесс тестирования более сложным. Однако, учитывайте и эти 2 фактора:

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

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

Потенциальная проблема номер 3: Длина передаваемой из клиентского приложения строки больше в случае использования списка с элементами фиксированной длины. Т.е. имеет место больший сетевой траффик. Вот это действительно может быть проблемой для низкоскоростных соединений или перегруженных сетей. Сама по себе проблема проблема не выглядит такой уж серьезной, однако, рассмотрение объема передаваемой информации приводит нас к

4-ая и самая важная потенциальная проблема: Важное значение имеет то, как Вы осуществялете вызов процедуры. Я в своих тестах использовал RPC (remote procedure call) и рекомендую Вам этот способ вызова процедур из клиентского приложения. (Например, если в ADO Вы используете adCmdStoredProcedure для свойства CommandType объекта Command object, то Вы используете RPC.) Другой способ вызвать хранимую процедуру - это пакетное выполнение команд с помощью EXEC, т.е. так, как Вы делеает это в Query Analyzer. Я выяснил, что начинаная с некоторой длины входных данных этот метод становится несколько медленнее чем использование списка с разделителем и вспомогательной таблицей(см. функцию duo_text_split_me). Фактически при вызове через RPC альтернативная версия функции fixstring_multi оказалась несколько быстрее, чем приведенная чуть выше функция. Но при использовании пакетного выполнения команд, время отклика было несколько секунд, что очень далеко от приемлимого. Почему так происходит - я не знаю. Но размер входной строки определенно играет какую-то роль. Я не проверял точно, при какой длине входных данных эффект имеет место быть, но по приблизительным данным где-то в районе 8000, т.е. в районе максимально размера типа данных varchar.

8. OPENXML

В последние годы на роль стандарта для обмена данными был выдвинут XML. Также как и HTML, XML возник на основе SGML стандарта, поэтому чисто внешне XML и HTML очень похожи. Но есть очень важные различия. В отличии от HTML, XML чувствителен в регистру и одна единственная ошибка в XML документе делает его полностью неправильным. Главное предназначение XML не в отображении, но передаче структурированных данных.В Microsoft SQL Server 2000 была добавлена широкая поддержка XML, из которой нас больше всего интересует функция OPENXML, которая может извлекать данные из XML строки и возвращать их в виде таблицы.

Это дает нам возможность представить список значений в виде XML строки и затем в T-SQL извлечеть эти значения с помощью OPENXML. Но не будем столь прямолинейны - это совершенно неправильный способ. И дело не производительности(по-настоящему медленные методы будут показаны ниже), она как раз вполне приемлима для большинства случаев. Просто дело в том, что XML слишком сложен для решения поставленной задачи. Кроме, конечно, случая если у Вас уже имеется готовый XML документ. Но строить XML строку только для моделирования массива - это по-моему самоубийство.

Но если Вам нужно передать на SQL Server массив структурированных данных (может быть даже многоуровневый), чтобы добавить эти данные в одну или несколько таблиц, то XML в этом случае действительно выход.

Но давайте же все-таки рассмотрим случай с простым списком. Вот пример того, как могла бы выглядеть функция get_product_names с применением XML:


   CREATE PROCEDURE get_product_names_xml @ids ntext AS

   DECLARE @idoc  int,
           @err   int

   EXEC  @err = sp_xml_preparedocument @idoc OUTPUT, @ids
   SELECT @err = @@error + coalesce(@err, 4711)
   IF @err <> 0 RETURN @err

   SELECT P.ProductName, P.ProductID
   FROM   Northwind..Products P
   JOIN   OPENXML(@idoc, '/Root/Num', 1)
          WITH (num int) AS a ON P.ProductID = a.num

   EXEC sp_xml_removedocument @idoc
   go
   EXEC get_product_names_xml N'<Root><Num num="9"/><Num num="12"/>
                                <Num num="27"/><Num num="37"/></Root>

Здесь важно отметить то, что при вызове sp_xml_prepraredocument SQL Server создает внутреннее представление переданной XML строки. Переменная @idoc есть указатель на это представление, который мы используем при выполнении OPENXML, которая представляет из себя табличную функцию (хотя в Books Online используется другое определение - rowset provider ). Перед выходом из нашей процедуры мы вызываем sp_xml_removedocument для того, чтобы освободить память, выделенную для XML документа. Если этого не делать, то в результате утечки памяти в один прекрасный момент запуск процедуры sp_xml_preparedocument закончится ошибкой. (Между прочим, т.к. хорошим тоном является проверка возвращаемого процедурой статуса завершения, то не пытайтесь проверять таким образом sp_xml_removedocument. Она все время возвращает 1. Это просто баг этой процедуры. Проверяйте лучше @@error.)

При составлении XML строки будьте внимательны, т.к. такие символы как <, & and " в XML являются метасимволами, т.е. Вы должны кодировать их приблизительно также как и в HTML. Тоже самое касается и не-ASCII данных в случае, если вы не используете Unicode. Помните также, что XML очень требователен к наличию пары для каждого тэга. Поэтому Вам для составления XML строки лучше воспользоваться какой-нибудь библиотечной функцией. Это в принципе не моя область, но кажется MS XML предлагает какие-то методы для этого, например, класс XmlTextWriter в .Net Framework.

Вот где XML действительно показывает все свою мощь, так это в случае когда Вам нужно добавить в SQL Server большой набор данных, например, заказы и их содержание. До появления поддержки XML в SQL Server наилучшим вариантом был запуск хранимой процедуры для каждой добавляемой записи. (Это всегда была операция bulk-copy, но все же это было неудобное решение.) С XML же Вы можете составить XML документ, содержащий все данные, и написать хранимую процедуру, вызывающую один раз sp_xml_prepredocument и затем дважды OPENXML - первый раз для добавления заказов, второй раз для добавления содержания заказов. Замена тысяч вызовов через сеть хранимых процедур на один - это действительно большой выигрыш в производительности.

По причине того, что статья и так получается довольно длинной, я не привожу здесь пример функции для такого рода задачи. Лучше предоставлю возможность решить эту задачу Вам. Если Вы совершенно не знакомы с XML и тем более с использованием XML в SQL Server, то Вы можете прочитать пару книжек на эту тему. Или поискать кой-какую полезную информацию на сайте SQL Server MVP Bryant Likes http://www.sqlxml.org/.

Примечение: те, кто знает XML, увидели, что в предложенном выше примере использовался attribute-centred XML. При составлении XML строки можно также использовать element-centred XML или оба сразу. Я не привожу примера для element-centred XML, т.к. разница между ними лишь в разборе элементов списка, но никак не в производительности.

8.1. Когда OPENXML НЕ нужен

Мне попадался на глаза способ решения нашей задачи, в котором передаваемая в процедуру строка с разделителем с помощью функции replace преобразовывалась в XML строку для того, чтобы дальше использоваться в OPENXML. По-моему, это очень плохая идея. И вот почему:

Т.к. результат функции replace должен быть типа varchar, то полученная XML строка не может быть длиннее 8000 символов. А значит, длина входной строки должна быть еще меньше.
Для удаления всех "неправильных" для XML символов функцию replace придется запускать несколько раз. Примеры, которые я видел, совершенно не учитывали эту проблему.
И т.к. XML более медленный метод, чем другие, которые к тому же и более легки в использовании, то такой подход совершенно бесполезен.

9. Динамический SQL

Для списка number элементов этот метод из-за своей простоты может казаться обманчиво хорошим:


    CREATE PROCEDURE get_product_names_exec @ids varchar(255) AS

    EXEC('SELECT ProductName, ProductID
          FROM   Northwind..Products
          WHERE  ProductID IN (' + @ids + ')')
    go
    EXEC get_product_names_exec '9, 12, 27, 37'

Этот пример очень похож на тот клиентский код, котрый был дан в начале статьи. Фактически, этот метод всего лишь вариант формирования SQL команды на стороне клиента и поэтому он имеет такие же недостатки, которые мы сейчас и рассмотрим. Сначала рассмотрим использование данного метода для списка string элементов и увидим, что в этом случае он уже не кажется таким привлекательным:


    CREATE PROCEDURE get_company_names_exec @customers nvarchar(2000) AS
        EXEC('SELECT CustomerID, CompanyName
             FROM   Northwind..Customers
             WHERE  CustomerID IN (' + @customers + ')')
    go
    EXEC get_company_names_exec '''ALFKI'', ''BONAP'', ''CACTU'', ''FRANK'''

Вложенные кавычки делают вызов процедуры на основе этого метода сложноватым.

Ну так каковы же недостатки этого метода?

Производительность. Этот метод медленнее всех других, рассмотренных в этой статье, и время его выполнения растет очень быстро при увеличении размера входного параметра. Для строки из 1000 элементов динамический SQL выполнялся более 10 секунд, в то время как другим методам понадобились доли секунды. Выполнение запроса как таковое не занимает много времени, его занимает построение SQL Server-ом плана выполнения запроса для того длинного выражения IN. Т.к. план выполнения записывается в кэш, то при повторном вызове процедуры с тем же списком, время выполнения может оказаться меньше чем для большинства других методов. Но для этого список должен быть абсолютно тем же самым, различие в одном единственном символе вынудит SQL Server снова тратить время на построение плана.
Права доступа. Данный метод требует, чтобы пользователю были даны явные права доступа на SELECT используемых в запросе таблиц. В обычных хранимых процедурах пользователю нужны только права на EXEC процедуры, а права на таблицы он получает через права владельца процедуры.
SQL Injection. С помощью данного метода злоумышленник может через интерфейс ввести какие-то данные и, если эти данные используются для построения SQL запроса без всякой дополнительной проверки, то злоумышленник может включить в выполняемый SQL пакет дополнительные действия.

Более подробно о динамическом SQL можно прочитать в моей статье The Curse and Blessings of Dynamic SQL.

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

Есть одно исключение: в версии SQL Server 6.5 все другие методы могут оперировать только списком типа varchar(255), поэтому динамический SQL в этой версии есть возможно единственное жизнеспособное решение.

10. Фокус с использованием UNION

SQL Server MVP Steve Kass предложил такой метод:


   CREATE PROCEDURE unpack_with_union
                    @list      varchar(8000),
                    @tbl       varchar(30),
                    @delimiter char(1) = ',' AS
   DECLARE @sql varchar(8000)
   SET @sql = 'INSERT INTO ' + @tbl + ' SELECT ''' +
               REPLACE(@list, @delimiter, ''' UNION ALL SELECT ''') + ''''
   EXEC (@sql)

Идея заключатеся в преобразовании списка в запрос SELECT с помощью оператора UNION ALL. (UNION ALL используется потому, что в отличии от UNION он не удаляет повторяющиеся значения.) Затем мы используем динамический SQL для выполнения этого запроса и добавления данных во временную таблицу, имя которой задается входным параметром. Т.к. в динамическом SQL используется только временная таблица, то проблемы с правами доступа пользователя в данном случае нет.

Вот пример использования :


   CREATE PROCEDURE get_company_names_union @customers varchar(8000) AS

   CREATE TABLE #temp (custid nchar(10) NOT NULL)
   EXEC unpack_with_union @customers, '#temp'

   SELECT C.CustomerID, C.CompanyName
   FROM   Northwind..Customers C
   JOIN   #temp t ON C.CustomerID = t.custid
   go
   EXEC get_company_names_union 'ALFKI,BONAP,CACTU,FRANK'

Этот метод вполне бы мог получить награду за непритязательность, но рассмотрим его более внимательно. Данный метод не может оперировать входными данными с длиной больше, чем ~3000. Вы, конечно, можете написать версию, которая принимает ntext параметер и разбивает его на порции. Но я не задавался такой целью, т.к. при тестировании производительности этот метод оказался значительно медленнее метода с OPENXML, который в свою очередь медленнее метода итераций и метода со вспомогательной таблицей. Никакими другими специфическими достоинствами данный метод также не обладает.

Anith Sen предложил простой метод, который просто преобразует список в набор INSERT запросов statements:


   CREATE PROCEDURE unpack_with_insert
                    @list      varchar(8000),
                    @tbl       varchar(30),
                    @delimiter char(1) = ',' AS
   DECLARE @sql varchar(8000)
   SET @sql = 'INSERT ' + @tbl + ' VALUES (' +
               REPLACE(@list, ',', ') INSERT ' + @tbl + ' VALUES (') + ')'
   EXEC (@sql)

Этот метод имеет такие же проблемы, как и вариант с UNION запросом. В тестах на производительность он вел себя даже хуже последнего.

11. Действительно медленные методы

В разделе Вопрос/Ответ одного из SQL журналов предлагалось следующее решение:


    CREATE PROCEDURE get_company_names_charindex @customers nvarchar(2000) AS
    SELECT CustomerID, CompanyName
    FROM   Northwind..Customers
    WHERE  charindex(',' + CustomerID + ',', ',' + @customers + ',') > 0
    go
    EXEC get_company_names_charindex 'ALFKI,BONAP,CACTU,FRANK'

Возможно, оно напомнит Вам метод с использованием вспомогательной таблицы. Добавление запятых в начало и конец входной строки позволяет нам использовать функцию charindex для поиска ",ALFKI," и т.д. (Заметьте, что в данном случае входная строка не должна содержать внутренних пробелов.)

Автор данного метода в своей статье заявил, что метод большой производительностью не отличатеся, т.к. использование поля таблицы в выражении исключает использование индекса по этому полю, приводя к сканированию таблицы. Но это только начало. Сканирование таблицы при тестировании занимало меньше 100 миллисекунд, если таблица полностью находилась в кэше. А этот метод отрабатывал за 42 секунды, даже для самого маленького тестового набора из 15 элементов общей длиной в 200 символов!

Варианты выражения WHERE могут еще такими:

WHERE patindex('%,' + CustomerID + ',%', ',' + @customers + ',') > 0 WHERE ',' + @customers + ',' LIKE '%,' + CustomerID + ',%'

Вариант с использованием patindex также выполняется 42 секунды. Вариант с использованием LIKE на одной из тестовых машин был фактически в 4 раза быстрее, но точно также медленным как с использованием charindex и patindex на других машинах. Как не прискорбно, но это все равно в 100 раз медленнее метода с динамическим SQL и в 200 раз медленнее самого быстрого метода.

12. SQL Server 7

Если Вы работаете с SQL Server 7, то у Вас нет возможности использовать пользовательские функции или XML. Поэтому в процедуре Вы можете использовать метод итераций или вспомогательную таблицу, для списка с разделителем или списка элементов фиксированной длины. Здесь я привожу пример для метода итераций. Вы можете легко адаптирвать его к двум другим методам.

12.1. Процедура для работы со списком string элементов

Данная процедура очень похожа на приведенную выше функцию iter_charlist_to_table. Но вместо возврата таблиицы-переменной процедура заполняет временную таблицу #strings:


   CREATE PROCEDURE charlist_to_table_sp
                    @list      ntext,
                    @delimiter char(1) = N',' AS

   DECLARE @pos      int,
           @textpos  int,
           @chunklen smallint,
           @tmpstr   nvarchar(4000),
           @leftover nvarchar(4000),
           @tmpval   nvarchar(4000),
           @sql      nvarchar(4000)

   SET NOCOUNT ON

   SELECT @textpos = 1, @leftover = ''
   WHILE @textpos <= datalength(@list) / 2
   BEGIN
      SELECT @chunklen = 4000 - datalength(@leftover) / 2
      SELECT @tmpstr = @leftover + substring(@list, @textpos, @chunklen)
      SELECT @textpos = @textpos + @chunklen

      SELECT @pos = charindex(@delimiter, @tmpstr)

      WHILE @pos > 0
      BEGIN
         SELECT @tmpval = left(@tmpstr, charindex(@delimiter, @tmpstr) - 1)
         SELECT @tmpval = ltrim(rtrim(@tmpval))
         INSERT #strings(str) VALUES (@tmpval)
         SELECT @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr))
         SELECT @pos = charindex(@delimiter, @tmpstr)
      END

      SELECT @leftover = @tmpstr
   END

   INSERT #strings(str) VALUES(ltrim(rtrim(@leftover)))

Вот пример использования этой процедуры:


   CREATE PROCEDURE get_company_names_iterproc @customerids nvarchar(2000) AS
   CREATE TABLE #strings (str nchar(10) NOT NULL)
   EXEC charlist_to_table_sp @customerids
   SELECT C.CustomerID, C.CompanyName
   FROM   Northwind..Customers C
   JOIN   #strings s ON C.CustomerID = s.str
   go
   EXEC get_company_names_iterproc 'ALFKI, BONAP, CACTU, FRANK'

Функция, разбирающая список string элементов, возвращала таблицу из двух полей - varchar и nvarchar. В случае с процедурой нет веской причины делать тоже самое. Пользователь процедуры сам определяет тип данных, с которыми он работает. По той же причине процедура не создает поле listpos . Если Вы хотите знать номер элемента в списке, то Вы можете добавить поле IDENTITY в таблицу.

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

Есть еще одна потенциальная проблема производительности в предложенном выше методе. Все зависит от того, как Вы используете его. Обычно при каждом вызове создается новая временная таблица. В этом случае процедура charlist_to_table_sp будет перекомпилироваться при каждом вызове. В большинстве случаев такая перекомпиляция не будет являться проблемой. Фактически, при тестировании я выяснил, что эта процедура несколько быстрее соответсвующей функции по преобразованию строки в таблицу, несмотря на перекомпиляцию. Однако, в загруженной системе при массовых вызовах Вы можете столкнуться с блокировками компиляции, как показано в KB article 263889.

Избежать этого можно, если использовать постоянную таблицу, которая может выглядеть примерно так:


    CREATE TABLE stringarray (spid    int            NOT NULL,
                              listpos int            NOT NULL,
                              str     varchar(4000)  NOT NULL,
                              nstr    nvarchar(2000) NOT NULL,
                              CONSTRAINT pk_array PRIMARY KEY (spid, listpos))

Для заполнения поля spid Вы можете использовать глобальную переменную @@spid - идентификатор соединения. Заполняющая такую таблицу процедура может в одной из первых строк содержать запрос:

DELETE stringarray WHERE spid = @@spid

и пользователь должен помнить о необходимости использовать поле spid в запросах. Например:


   CREATE PROCEDURE get_company_names_spid @customerids nvarchar(2000) AS
   EXEC charlist_to_table_spid @customerids
   SELECT C.CustomerID, C.CompanyName
   FROM   Northwind..Customers C
   JOIN   stringarray s ON C.CustomerID = s.nstr
   WHERE  s.spid = @@spid
   DELETE stringarray WHERE spid = @@spid      -- Владелец.
   go
   EXEC get_company_names_spid 'ALFKI, BONAP, CACTU, FRANK'

Ещё одной альтернативой может быть создание временной таблицы в самой процедуре. Пользователь для получения результата может в этом случае воспользоваться конструкцией INSERT EXEC. Более подробно с такой методикой, а также ее недостатками, можно ознакомиться в моей статье How to share data between stored procedures.

12.2. Экстравагантная процедура для списка integer значений

Методику, предложенную в предыдущем разделе, можно с успехом применить и для списка integer элементов. Но то, что предложено ниже, не является точной копией функции iter_intlist_to_table, т.к. во избежание ошибок преобразования типов, здесь делается проверка на то, являются ли элементы списка в действительности числами. И для пущей важности процедура различает и то, какого знака числа, например, +98 или -83. Если список содержит элементы не являющиеся числами, то процедура выводит предупреждение. Процедура заполняет временную таблицу, в которой имеется поле listpos . Значение в этом поле сообщит нам о пропущенных элементах списка, не прошедших проверку.


   CREATE PROCEDURE intlist_to_table_sp @list ntext AS

   DECLARE @pos      int,
           @textpos  int,
           @listpos  int,
           @chunklen smallint,
           @str      nvarchar(4000),
           @tmpstr   nvarchar(4000),
           @leftover nvarchar(4000)

   SET NOCOUNT ON

   SELECT @textpos = 1, @listpos = 1, @leftover = ''
   WHILE @textpos <= datalength(@list) / 2
   BEGIN
      SELECT @chunklen = 4000 - datalength(@leftover) / 2
      SELECT @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
      SELECT @textpos = @textpos + @chunklen

      SELECT @pos = charindex(' ', @tmpstr)
      WHILE @pos > 0
      BEGIN
         SELECT @str = rtrim(ltrim(substring(@tmpstr, 1, @pos - 1)))
         EXEC insert_str_to_number @str, @listpos
         SELECT @listpos = @listpos + 1
         SELECT @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
         SELECT @pos = charindex(' ', @tmpstr)
      END

      SELECT @leftover = @tmpstr
   END

   IF ltrim(rtrim(@leftover)) <> ''
      EXEC insert_str_to_number @leftover, @listpos
   go

   -- This is a sub-procedure to intlist_to_table_sp
   CREATE PROCEDURE insert_str_to_number @str     nvarchar(200),
                                         @listpos int AS

   DECLARE @number  int,
           @orgstr  nvarchar(200),
           @sign    smallint,
           @decimal decimal(10, 0)

   SELECT @orgstr = @str

   IF substring(@str, 1, 1) IN ('-', '+')
   BEGIN
      SELECT @sign = CASE substring(@str, 1, 1)
                         WHEN '-' THEN -1
                         WHEN '+' THEN 1
                     END
      SELECT @str  = substring(@str, 2, len(@str))
   END
   ELSE
      SELECT @sign = 1

   IF @str LIKE '%[0-9]%' AND @str NOT LIKE '%[^0-9]%'
   BEGIN
      IF len(@str) <= 9
         SELECT @number = convert(int, @str)
      ELSE IF len(@str) = 10
      BEGIN
         SELECT @decimal = convert(decimal(10, 0), @str)
         IF @decimal <= convert(int, 0x7FFFFFFF)
            SELECT @number = @decimal
      END
   END

   IF @number IS NOT NULL
      INSERT #numbers (listpos, number) VALUES (@listpos, @sign * @number)
   ELSE
      RAISERROR('Warning: at position %d, the string "%s" is not an legal integer',
                 10, -1, @listpos, @orgstr)
   go

Вот пример использования процедуры:


    CREATE PROCEDURE get_product_names_iterproc @ids varchar(50) AS
    CREATE TABLE #numbers (listpos int NOT NULL,
                           number  int NOT NULL)
    EXEC intlist_to_table_sp @ids
    SELECT P.ProductID, P.ProductName
    FROM   Northwind..Products P
    JOIN   #numbers n ON P.ProductID = n.number
    go
    EXEC get_product_names_iterproc '9 12 27 37'

Проверка элемента списка происходит в подпроцедуре insert_str_to_number. В большинстве случаев достаточно быдет проверять лишь следующие условия

@str NOT LIKE '%[^0-9]%' AND len(@str) BETWEEN 1 AND 9

т.е. проверять, что @str содержит только цифры и их количество не превышает 9 (т.к. 10-ти значные числа мы интерпретируем как числа со знаком).

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

И еще одно замечание о выводе предупреждения с помощью RAISERROR: в ADO это предупреждение очень трудно или вообще невозможно перехватить в клиентском приложении. Если вы поменяете severity с 10 на 11, то предупреждение будет считаться уже ошибкой, что вызовет соответсвующую реакцию в клиентском приложении.

13. SQL Server 6.5

При использовании SQL 6.5 Вы гораздо больше ограничены в выборе метода решения. Длина данных типа varchar ограничена 255 символами и функция substring не работает с text. Поэтому Вы не можете разбить входные данные на порции или извлечь элементы фиксированной длины. Вы все же можете использовать процедуры, приведенные для версии SQL 7. Но т.к. длина входной строки не может превышать 255 символов, то риск того, что рано или поздно Вы все же упретесь в этот предел, слишком велик, чтобы так просто сбрасывать его со счетов.

Поэтому, единственным методом, являющимся достаточно здравым решением для версии 6.5, остается динамический SQL. В этом случае, как показано в данном ниже примере, Вы можете использовать тип данных text для передачи длинного списка:


   CREATE PROCEDURE get_authors_exec @authors text AS
       EXEC('SELECT au_id, au_lname, au_fname
             FROM   pubs..authors
             WHERE  au_id IN (' + @authors + ')')
   go
   EXEC get_authors_exec '''172-32-1176'', ''427-17-2319'', ''724-08-9931'''

14. Результаты тестов на производительность

14.1. Общее предупреждение

Тестирование производительности СУБД не простая задача. Существует много факторов, влияющих на производительность. В своих тестах я проверял производительность разбора одной входной строки в таблицу (в методах, где это возможно) а также производительность извлечения данных из таблицы на основе переданного списка. Операция простого извлечения элементов из списка проверяет собственно сам метод, но при использовании метода для извлечения данных из другой таблицы(таблиц) большое влияние начинают оказывать свойства этой таблицы(таблиц), такие как индексы, статистика и тд. Возможно, Вы найдете, что методы, объявленные мной как быстрые, создают медленный план выполнения при использовании в запросе. Другая проблема заключается в том, каким образом Вы запускаете SQL запросы на выполнение. Для функций, оперирующих со строками конечной длины, я заметил, что существует значительная разница между RPC вызовами и командными пакетами(command-text batches). (Все результаты тестов были получены с использованием RPC.) И еще одна проблема заключается в том, как поведет себя метод при общей загруженности системы. Для исключения такого влияния я все тесты проводил на специально выделенных под это машинах.

Таким образом, воспринимайте эти результаты такими, какие они есть. Они, безусловно, служат для определения безнадежно плохих методов. Но при самостоятельном использовании Вы можете выяснить, что некоторые из плохих методов имеют прекрасные показатели производителности. Если Вас волнует вопрос производителности в контексте конкретной задачи, то Вы, возможно, проведете свое исследование производительности.

14.2. Соперники по тестам

При написании этой статьи я протестировал очень много методов, для того чтобы включить полученные результаты в данный текст. Некоторые методы имели лишь незначительные отличия друг от друга. Например, возвращали varchar вместо nvarchar. Но я не зaдавался целью запомнить полученные результаты для каждой вариации. В конечном счете, я получил данные тестов для 16 случаев. Из них я выбрал 9 и включил их в основной список результатов тестов. Еще несколько результатов дано в разделе специальных наблюдений где я подметил некоторые особенности. Полный перечень результатов для всех 16 методов, полученный на 3-х тестовых машинах, можно увидеть здесь.

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

Вот основные девять соперников:

ITER	Метод итераций: функции iter_intlist_to_table и iter_charlist_to_table.
TBLNUM	Метод со вспомогательной таблицей: функция text_split_me (правда в отличии от данной выше duo_text_splitme возвращает только поле varchar).
FIX$SINGLE	Список элементов с фиксированной длиной: функция fixstring_single (с использованием одного соединения с таблицей номеров).
FIX$MULTI	Список элементов с фиксированной длиной: функция fixstring_multi (с использованием соединения с таблицей номеров дважды для случая с неограниченной длиной входных данных).
XMLATTR	OPENXML с использованием attribute-centred XML.
EXEC$A	Динамический SQL(при каждом вызове использовался новый набор входных данных).
UNION	Фокус с использованием UNION: процедура unpack_with_union.
REALSLOW	Действительно медленный метод: с использованием функции charindex для поиска элемента списка.
SLOW$LIKE	Другой действительно медленный метод: с использованием LIKE для поиска элемента списка.

А вот оставшиеся семь соперников:

ITER$PROC	Метод итераций: процедуры iter_intlist_to_table_sp и iter_charlist_to_table_sp.
ITER$EXEC	Вариант процедуры iter_charlist_to_table_sp, который получает имя таблице в качестве входного параметра и использует динамический SQL для добавления в нее данных.
TBLNUM$IL	Метод с использованием вспомогательной таблицей: inline функция inline_split_me с ограничением длины входных данных.
FIX$ITER	Список элементов фиксированной длины: элементы извлекаются в цикле.
XMLELEM	OPENXML с использованием element-centred XML.
EXEC$B	Динамический SQL (для каждого входного набора вызов повторялся дважды).
INSERT	Фокус с использованием INSERT: процедура unpack_with_insert.

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

14.3. Каким образом проводилось тестирование

Тестируемая таблица

Данную таблицу я использовал во всех тестах.


   CREATE TABLE usrdictwords (wordno int          NOT NULL,
                              word   varchar(50)  NOT NULL,
                              guid   char(36)     NOT NULL)
   CREATE CLUSTERED INDEX wordno_ix ON usrdictwords(wordno)
   CREATE UNIQUE INDEX word_ix ON usrdictwords(word)

Таблица содержала 202985 записей, средняя длина поля word составляла 9.7 символа, размер самого длинного значения этого поля составлял 31 символ. Значения в поле wordno в действительности были уникальными. Отсутствие уникального индекса по этому полю есть результат моей невнимательности при создании таблицы. Ошибка была обнаружена после окончания тестирования. Порядок следования значений в поле wordno никак не связан с порядком значений в поле word . Поле guid служило целям простого увеличения размеров таблицы, а также моделировало наличие неидексированного поля.

Тестовые наборы

Для каждого теста из таблицы usrdictwords случайным образом формировался список с запятой в качестве разделителя. Для списка string и integer элементов использовались поля word и wordno соответственно. К каждому элементу списка случайным образом добавлось от 0 до 3 пробелов. Это делалось для того, чтобы тестирование проверяло также и то, что метод вообще возвращает правильные результаты. Скрипт формирования входного списка заполнял его до предварително заданного количества элементов. Таким образом, списки integer элементов хоть и имели меньшую общую длину, но содержали такое же число элементов, как и списки string элементов.

Я использовал списки пяти различных размеров:

Размер	Длина	Среднее количество элементов
Малый	237	19
Средний	2456	201
Большой	7950	653
Очень большой	23997	1978
Огромный	120000	9892

Все методы тестировались со списками малого и среднего размеров. Для списков большого размера и выше я исключил из тестирования UNION, INSERT, REALSLOW и SLOW$LIKE, первые 2 потому, что они не могут опрерировать входными данными такого размера, последние два потому, что они слишком медленны для того, чтобы я смог дождаться результатов. Для списка очень большого размера я исключил из тестирования TBLNUM$IL потому, что он не мог оперерировать входными данными такого размера. Для списка огромного размера я исключил из тестирования FIX$SINGLE потому, что он не мог опрерировать входными данными такого размера и ITER$EXEC, EXEC$A и EXEC$B потому, что они слишком медленны.

Замечу еще, что данные выше размеры списков были взяты из единожды сгененерированного тестового набора. Для многих методов входная строка приводилась к конкретному специфическому для метода формату, поэтому ее длина могла оказаться как меньше (при удалении вложенных пробелов) так и больше (XML или список элементов с фиксированной длиной). Но действительное число элементов для всех методов остовалось таким же.

Тестируемые операции

Я тестировал две операции со списками:

UNPACK

Unpacking the input list itself into a result set, without involving another table. This test cannot easily be implemented for all methods. You rarely have the need for this in real life, but the test isolates the performance of the method as such. Собственно операция извлечения элементов списка в набор данных без объединения с другой таблицей. Возможность такого тестирования различается для разных методов. В повседневных задачах такой результат нужен редко, но данный тест позволяет проверить собственно производительность метода.

JOIN

Using the input list to extract data from a table. With most methods this means a join operation, and I denote this operation as JOIN also for a method like dynamic SQL even if there is no actual join taking place. Использование входного списка для извлечения данных из таблицы. Для большинства методов это означает операцию объединения, поэтому я и обозначил ее как JOIN, не смотря на то, что в таких методах, как динамический SQL операции объединения как таковой нет.

Пример тестовой процедуры

Вот пример типичной процедуры операции UNPACK:


   CREATE PROCEDURE TBLNUM_Int_UNPACK_test @str    text,
                                           @tookms int OUTPUT AS

   DECLARE @start datetime
   SELECT @start = getdate()

   SELECT number = convert(int, Value)
   INTO   tmp
   FROM   text_split_me(@str)

   SELECT @tookms = datediff(ms, @start, getdate());

   SELECT number FROM tmp
   DROP TABLE tmp

Процедура запускает таймер, выполняет запрос, добавляет результат в таблицу тестовой базы, останавливает таймер и возвращает данные в тестовый скрипт, который осуществляет проверку правильности этих данных. Запрос SELECT INTO был выбран в качестве механизма для добавления данных в таблицу, т.к. в этом случае в лог пишется меньше информации, чем для запроса INSERT. Включение операции по возврату данных в общее время выполнения запроса означало бы, что пропускная способность сети могла бы сильно повлиять на результаты тестирования. Побочным эффектом использования SELECT INTO является перекомпиляция процедуры при каждомом запуске на строке с последним запросом SELECT, поэтому этот запрос также выполняется после остановки таймера. Я также привожу пример процедуры для тестирования операции JOIN, чтобы отметить одну важную деталь операции соединения таблиц:


   CREATE PROCEDURE ITER_Str_JOIN_test @str text,
                                       @tookms int OUTPUT AS

   DECLARE @start datetime
   SELECT @start = getdate()

   SELECT u.wordno, u.guid
   INTO   tmp
   FROM   usrdictwords u
   JOIN   iter_charlist_to_tbl(@str, DEFAULT) AS a on u.word = a.str

   SELECT @tookms = datediff(ms, @start, getdate());

   SELECT wordno FROM tmp
   DROP TABLE tmp

Отмечу здесь то, что при соединении таблиц по string полю также извлекается поле guid , по которому нет никакого индекса. Это вынуждает SQL Server обращаться непосредственно к страницам данных таблицы. (В противном случае в запросе использовался бы покрывающий индекс по полю word .) Это заставляет планировщик делать выбор между использованием некластерного индекса по полю word с bookmark lookups-ми и сканированием таблицы.

Для большинства методов использовалось четыре процедуры: UNPACK-Str, UNPACK-Int, JOIN-Str и JOIN-int.

Тексты всех процедур доступны для скачивания в виде zip файла.

Тестовый скрипт

Тестовый скрипт был написан на Perl, для соединения с SQL сервером использовалась DB-Library (потому что эту клиентскую библиотеку я знаю лучше всего). Использование DB-Library накладывает некоторые ограничения: в тестовой процедуре нельзя использовать параметры типа ntext и параметры типа varchar длиной больше 255 символов. Поэтому во всех процедурах в качестве входного параметра используется text. Скрипт выполняет установку параметров соединения с помощью набора SET команд аналогично тому, как это делается при коннекте через OLE DB или ODBC. Выполняется также SET NOCOUNT ON. Текст тестового скрипта доступен для скачивания в виде zip файла.

При тестировании тестовый скрипт создавал список string и integer элементов, как это описано выше и затем передавал этот список всем тестовым процедурам в текущей тестовой базе. Для генератора случайных чисел использовалось фиксированное еачальное число(seed), поэтому при повторных запусках скрипта генерировались такие же тестовые наборы. Это дало мне возможность тестировать каждый метод отдельно от других, в тоже время используя одинаковые тестовые наборы для всех методов. (Это удобно для выяснения преимуществ одного метода без повторных запусков остальных методов.) Для тестового набора одного размера тестирование проводилось 100 раз, за исключением методов REALSLOW и SLOW$LIKE, для которых проводилось только 2 теста по причине того, что эти методы чересчур медленные.

Оборудование

Тестирование проводилось на трех разных машинах:

JAMIE4K	Сервер с 4-мя процессорами Pentium III 550 GHz, 3GB RAM, дисковыми массивами. SQL 2000 SP3.
ABA0163	Оффисная машина с одним процессором Pentium III 996 MHz, 512MB RAM (SQL серверу было выделено около 120 MB), SCSI диски. SQL 2000 SP2.
KESAMETSA	Домашняя машина с одним процессором Pentium 4 2.266 GHz, 512 MB и IDE дисками. SQL2000 SP3.

Тестовый скрипт всегда запускался на KESAMETSA. К ABA0163 и JAMIE4K коннект осуществлялся через корпоративную VPN. Все машины во время тестирования не были в общем загружены другими задачами.

Для представления результатов тестов в самой статье использются результаты полученные только для JAMIE4K. Я считаю, что эти цифры более показательны по следующим двум причинам: 1) эта машина является единственным настоящим сервером и поэтому больше похожа на production машину. 2) Т.к. у JAMIE4K более медленные CPUs, то измерение времени выполнения тестов на ней более точное. (Время в SQL сервере вычисляется с точностью до 3.33 миллисекунд, поэтому время исполнения меньше 50мс невозможно измерить достоверно.)

Т.к. тестовая таблица достаточно мала, чтобы поместиться в кэш при выполненни теста, то это приводит к большему использованию CPU. Поэтому для большинства методов ABA0163 была в 2 раза быстрее чем JAMIE4K и в 4 раза быстрее чем KESAMETSA. Но есть одно исключение из этого правила, а именно то, что JAMIE4K привыполнении тестов могли использоваться все четыре процессора.

14.4. Результаты

Ниже я представляю среднее время выполнения в миллисекундах каждого из девяти основных соперников на машине JAMIE4K. Результаты всех 16 методов на каждой машине, включая минимальное, среднее и максимальное время выполнения, стандартное отклонение и коэффициет распределения, можно увидеть по следующим ссылкам JAMIE4K, ABA0163 и KESAMETSA.

Тестовый набор малого размера

Метод	UNPACK Str	UNPACK Int	JOIN Str	JOIN Int
ITER	51	50	51	51
TBLNUM	50	52	53	53
FIX$SINGLE	47	47	52	47
FIX$MULTI	49	46	65	67
XMLATTR	50	51	57	53
EXEC$A			115	70
UNION	62	61	67	66
REALSLOW			42233	7624
SLOW$LIKE			10843	7640

На основе эти данных можно сделать одно заключение - методы REALSLOW и SLOW$LIKE не пригодны для использования. (Причина 4-х кратного превосходства SLOW$LIKE над REALSLOW в тесте JOIN-Str кроется в том, что по некоторым причинам SQL сервер не мог использовать все четыре процессора для метода REALSLOW в этом тесте. Сравните результаты для ABA0163 и KESAMETSA.)

Для других семи методов результаты очень похожи между собой, хотя метод EXEC$A немного выделяется. Таким образом, для работы с входной строкой малого размера все эти методы пригодны. Я еще раз позволю себе подчеркнуть, что точность типа данных datetime в SQL сервере для таких промежутков времени не позволяет достоверно измерить время выполнения. На быстрых машинах ABA0163 и KESAMETSA для всех методов, кроме REALSLOW и SLOW$LIKE, измеренная продолжительность как минимум одного теста равнялась 0 мс.

Тестовый набор среднего размера

Метод	UNPACK Str	UNPACK Int	JOIN Str	JOIN Int
ITER	88	83	116	94
TBLNUM	75	66	100	75
FIX$SINGLE	46	47	76	55
FIX$MULTI	53	54	101	81
XMLATTR	96	93	122	102
EXEC$A			492	249
UNION	242	209	277	219
REALSLOW			404641	67823
SLOW$LIKE			103609	68166

Результаты методов REALSLOW и SLOW$LIKE выглядят еще более смехотворными. Теперь Вы понимаете, почему я не тестировал эти методы для строк большого размера.

Оставшиеся семь методов все еще показывают результаты, которые большинство людей сочло бы вполне приемлимыми. Однако, можно увидеть, что методы EXEC$A и UNION начинают отставать от других пяти. Метод UNION имеет ограничение на размер входного параметра и поэтому не может быть проверен для строк большого размера, если мы не будем разбивать их на порции. Но такой целью я как раз и не задавался и именно по причине результатов тестов. Глядя на эти цифры, не верится, что метод UNION сможет составить серьезную конкуренцию другим методам.

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

Тестовый набор большого размера

Метод	UNPACK Str	UNPACK Int	JOIN Str	JOIN Int
ITER	191	193	269	222
TBLNUM	127	112	204	136
FIX$SINGLE	58	56	156	83
FIX$MULTI	79	81	174	115
XMLATTR	265	262	341	286
EXEC$A			2489	1508

Результаты уже начинают различаться, что дает нам возможность более достоверно оценить производительность того или иного метода. При операции разбора строки метод FIX$SINGLE по-прежнему также быстр, как и для тестового набора малой длины. Но следует прежде всего обратить внимание на метод с использованием динамического SQL, т.е. на EXEC$A. 2? секунды на извлечение string элементов против 350 мс у других методов делают данный метод неприемлимым для использования.

[В начало]

String Size X-Large

Тестовый набор очень большого размера

Метод	UNPACK Str	UNPACK Int	JOIN Str	JOIN Int
ITER	477	478	722	550
TBLNUM	293	234	542	310
FIX$SINGLE	92	79	378	164
FIX$MULTI	156	156	411	230
XMLATTR	764	759	1004	831
EXEC$A			14235	12054

При тестовом наборе такого размера диамический SQL начинает все больше выходить из под контроля.

Т.к. данный тестовый набор является максимально возможным для метода FIX$SINGLE (из-за того, что в таблице Numbers, только 8000 записей, мы не сможем оперировать набор огромной длины), то давайте подведем некоторые промежуточные итоги тестирования производительости. Очень четко можно увидеть, что использование элементов фиксированного размера и их извлечение с помощью вспомогательной таблицы дают наименьшее время выполнения. Так же можно заметить, что более простой метод fixstring_single быстрее более сложного fixstring_multi. Последний правда позволяет использовать теоритически неограниченные по размеру входные данны. Но с другой стороны, Вы можете добавить записи в таблицу Numbers , чтобы увеличть количество обрабатываемых функцией fixstring_single элементов списка. Правда, строка из 8000 элементов это достаточно большой набор.

Существуют, однако, две проблемы, которые могут существенно понизить производительность метода с использованием элементов фиксированной длины. Во-первых, это зависит от того, как именно был использован этот метод. В своих тестах я осуществлял вызов всех процедур через RPC, что является стандартным способом для приложений. Выяснилось, что если вместо этого использовать пакетное выполнение с помощью команды EXEC, то при привышении длины входного параметра некоторого значения производительность этих методов (и только их) падала. FIX$SINGLE был все еще быстрее всех , но TBLNUM обгонял FIX$MULTI и выходил на 2-ое место.

Вторая проблема становится видна, если мы проанализируем разницу времени выполнения операций JOIN и UNPACK:

Метод	Str	Int
ITER	245	72
TBLNUM	249	76
FIX$SINGLE	286	85
FIX$MULTI	255	74
XMLATTR	240	72

О чем говорят эти цифры, я точно судить не возьмусь. Они могут говорить о том, что в FIX$SINGLE время собственно извлечения данных из списка больше. И значит , что при работе с какими-то другими таблицами какие-то другие методы могут оказаться быстрее. (Но причиной этого тогда будет другой план выполнения запроса.) С другой стороны, хоть XML и является в данном случае самым медленным методом, но у него и наименьшая разница между временем выолнения разных операций. Это может означать, что операции по извлечению элементов списка и по извлечению данных из таблицы перекрываются и мы, таким образом, можем вообще для себя не разделять эти операции. Если производительность является для Вас ключевым вопросом, то Вы должны самостоятельно провести дополнительные исследования в своей конкретной задаче. Об этом я упомянул в Общем предупреждении.

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

Тестовый набор огромного размера

Метод	UNPACK Str	UNPACK Int	JOIN Str	JOIN Int
ITER	2211	2175	3329	2573
TBLNUM	1311	994	2434	1406
FIX$MULTI	572	532	1673	918
XMLATTR	3889	3793	4850	4199

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

Анализ тенденций

В данной ниже таблице показано, как возрастает время выполнения в зависимости от размера тестового набора. Приведены наименьшие и наибольшие значения для каждого метода:

Метод/ Размер	Средний/ Малый	Большой/ Малый	Оч.большой/ Большой	Огромный/ Оч.большой
Отношение размеров	10.4	3.2	3.0	5.0
ITER	1.7 - 2.3	2.2 - 2.4	2.5 - 2.7	4.6 - 4.7
TBLNUM	1.3 - 1.9	1.7 - 2.0	2.1 - 2.7	4.2 - 4.5
FIX$SINGLE	1.0 - 1.5	1.2 - 2.1	1.4 - 2.4
FIX$MULTI	1.1 - 1.6	1.4 - 1.7	1.9 - 2.4	3.4 - 4.1
XMLATTR	1.8 - 2.1	2.8 - 2.8	2.9 - 2.9	4.8 - 5.1
EXEC$A	3.6 - 4.3	5.1 - 6.1	5.7 - 8.0
UNION	3.3 - 4.1
REALSLOW	8.9 - 9.6
SLOW$LIKE	8.9 - 9.6

Разумно предположить, что время выполнения для строки некоторой длины определяется линейны уравнением:

t(s) = y + k·s

Где s - это размер строки, y - некоторая начальная задержка и k - постоянная, значение которой близко к 1. Величины y и k конечно же различаются для разных методов.

На основе этой таблицы можно сделать два вывода: 1) похоже, что чем быстрее метод, тем меньше коэффициент (k). Т.е. даже при возрастании размера входных данных более быстрый метод сохраняет свою позицию. 2) динамический SQL не подтверждает предположение о том, что значение k близко к 1, похоже, что оно скорее ближе к 2 (если мы считаем уравнение по-прежнему линейным). Это означает что при 2-хкратном увеличении размера входных данных, время выполнения динамического SQL возрастет в 4 раза. Помнится я говорил что-то подобное выше, не так ли?

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

14.5. Особые наблюдения

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

Процедура или Функция

В тестировании участвовали также процедурные версии для метода итераций. Я не включил эти результаты в основной список, потому что тестируемые процедуры сильно отличались друг от друга:

ITER$PROC	Str	iter_charlist_to_table_sp - просто преобразованная в процедуру функция iter_charlist_to_table.
ITER$PROC	Int	iter_intlist_to_table_sp - осуществляет дополнительную проверку элементов и поэтому ее выполнение занимает больше времени, чем выполнение функции iter_intlist_to_table.
ITER$EXEC	Str	Вариант iter_charlist_to_table_sp, который получает имя временной таблицы в виде параметра и с помощью динамического SQL добавляет в нее данные.

Сначала посмотрим на результаты работы процедур со списком string элементов:

	ITER			ITER$PROC			ITER$EXEC
Размер тестового набора	UNPACK	JOIN	Delta	UNPACK	JOIN	Delta	UNPACK	JOIN	Delta
малый	51	51	0	58	64	6	88	89	1
Средний	88	116	28	89	123	34	414	443	29
Большой	191	269	78	182	284	102	1223	1321	98
Оч.большой	477	722	245	431	731	300	3570	3868	298
Огромный	2211	3329	1118	1943	3019	1076

Можно видеть, что процедуры в действительности быстрее функций, если мы используем их только для разбора строки. Это удивляет, особенно в свете того, что процедуры перекомпилируются при каждом вызове. Я вижу две возможных причины этого - или временные таблицы в общем быстрее чем таблицы-переменные или процедуры в общем быстрее multi-step функций. Но если мы попытаемся использовать возвращаемые результаты для извлечения данных из временной таблицы, то процедуры начинают проигрывать функциям при большом и очень большом размере тестового набора. Это наводит на мысль, что соединение со временной таблицей есть более дорогая операция чем соединение с таблицей-переменной. Этим может объясняться и разница в планах выполнения (вспомните о наличии в этом случае выбора между операциями table scan и index seek + bookmark lookup). В любом случае для тестового набора огромного размера использование временной таблицы дает выигрыш а также наименьшую разницу между операциями UNPACK и JOIN.

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

И пару слов об ITER$EXEC... Идея заключалась в передаче имени таблицы, в которую динамическим запросом добавлялись элементы из списка:


  SELECT @sql = 'SET QUOTED_IDENTIFIER OFF INSERT ' + @tbl +
                '(str) VALUES( "' + replace(@tmpval, '"', '""') + '")'
  EXEC (@sql)

Такой способ выглядит элегантно, но взгляните на данную выше таблицу с результатами и увидите, что это дорогостоящий способ. По-моему слишком дорогостоящий, чтобы отстаивать возможность его применения. Причина этого в том, что для каждого элемента списка SQL Server должен проверить INSERT запрос и построить план его выполнения. Это еще один пример того, что неправильное использование динамического SQL приводит к потере производительности. (Верно и обратное - т.е. правильное использование динамического SQL ведет к повышению производительности.) Но все-таки в отличии EXEC$A при использовании ITER$EXEC коеффициент (k) близок к 1 в нашем уравнении.

А что у нас получается для экстравагантной процедуры для списка integer элементов?

	ITER			ITER$PROC
Размер тестового набора	UNPACK	JOIN	Delta	UNPACK	JOIN	Delta
Малый	50	51	1	60	62	2
Средний	83	94	11	110	119	9
Большой	193	222	29	243	528	285
Оч.большой	478	550	72	637	958	321
Огромный	2175	2573	398	2852	3300	448

Не удивительно, что наша попытка использовать нечто феерическое отразилась на производительности. Однако все не так трагично, как например для маленького динамического SQL в ITER$EXEC. И если Вы вернетесь к основным результатам тестов, то увидите, что эта процедура быстрее, чем XML.

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

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

Inline или Multi-step функция

В тестировании участвовали две функцииn по извлечению элемента из строки с разделителем с помощью вспомогательной таблицы: multi-step функция TBLNUM и inline функця TBLNUM$IL(с ограничением длины строки в 7998 символов). Вот результаты тестов для входной строки большого размера:

Метод	UNPACK Str	UNPACK Int	JOIN Str	JOIN Int
TBLNUM$IL	134	102	224	140
TBLNUM	127	112	204	136

Может сложится мнение, что multi-step функции в действительности быстрее inline функции, но на самом деле это частично обман зрения. Multi-step функция использует тип данных nvarchar, а inline функция - varchar (по-другому не получается оперировать тестовым набором нужного размера). Если обе функции используют тип данных varchar, то inline функция быстрее приблизительно на 5%. Но т.к. для тестового набора такого размера время выполнения в любом случае среднее, то эти рассуждения носят скорее общепозновательный характер.

Хотел отметить, что разница(или если угодно совпадение) времени выполнения inline и multi-step функций зависит от входных данных. Я не проводил отдельного исследования для элементов фиксированной длины, для которых выводы могут получиться другими.

Загадочный метод EXEC$B

EXEC$B не является на самом деле методом. EXEC$B есть повторное выполнение точно такого же динамического SQL. Его поведение при тестировании было вызывающим. На этот раз, я представляю результаты тестов на всех машинах:

	JAMIE4K		ABA0163		KESAMETSA
Размер тестового набора	Str	Int	Str	Int	Str	Int
Малый	51	68	4	6	4	6
Средний	73	54	17	96	167	109
Большой	141	83	358	18	178	15
Оч.большой	330	147	386	48	193	30

При сравнении этих результатов выполнения на JAMIE4K с результатаим других методов можно заметить, что только FIX$SINGLE может сравниться по производительности с EXEC$B, да и то невсегда. Для тестового набора большого размера EXEC$B опережает его для списка string элементов и идет вровень с методом FIX$SINGLE для списка integer элементов. Но т.к. все равно EXEC$B остается дорогостоящим методом (т.к. требует выполнение начального запроса EXEC$A для размещения плана выполнения в кэше), то EXEC$B не может составить действительной конкуренции другим методам. Для того, чтобы "обогнать" метод XML для тестового набора очень большого размера Вы должны запустить EXEC$B с одним и тем же SQL запросом раз 20. Или больше, если хотите "обогнать" ITER или TBLNUM.

Теперь взглянем на результаты работы со списком integer элементов на ABA0163 и KESAMETSA и конкретно на результаты для тествого набора среднего размера. Неужели Земля стала вращаться в обратную сторону ??? Нет, просто попробуйте повторить запуск раз 100 и Вы еще и не такие результаты увидите. Т.к. у нас есть кластерный индекс по полю типа integer, то не трудно догадаться, что используется другой план выполнения. ...Но почему же планировщику не приходит в голову мысль использовать и некластерный индекс, когда это возможно?

14.6. Домашнее задание

Если Вы хотите провести самостоятельное тестирование(возможно даже и своих собственных методов), то Вы можете скачать файл arraylist.zip, который содержит тестовый скрипт, тестовые процедуры и функции для различных методов и таблицу с номерами (размер файла около 30 KB). В файле README.HTML можно найти инструкции по применению. Результаты тестов можно получить в отдельном файле usrdictwords.zip (его размер около 6 MB).