По материалам статьи Erland
Sommarskog (SQL Server MVP) Arrays
and Lists in 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'
(Здесь вы можете увидеть текст процедуры charlist_to_table_spid.)
Ещё одной альтернативой может быть создание временной
таблицы в самой процедуре. Пользователь для получения
результата может в этом случае воспользоваться конструкцией
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 для поиска элемента
списка. |
А вот оставшиеся семь соперников:
В
следующей главе я описываю, каким образом производилось
тестирование, и какие допущения были сделаны. Если Вас не
интересуют эти подробности, то Вы можете сразу перейти к
результатам
тестов.
[В
начало]
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.
[В
начало]
Тестовый набор
малого размера
На основе эти данных можно сделать одно заключение - методы
REALSLOW и SLOW$LIKE не пригодны для использования. (Причина
4-х кратного превосходства SLOW$LIKE над REALSLOW в тесте
JOIN-Str кроется в том, что по некоторым причинам SQL сервер
не мог использовать все четыре процессора для метода REALSLOW
в этом тесте. Сравните результаты для ABA0163
и KESAMETSA.)
Для других семи методов результаты очень похожи между
собой, хотя метод EXEC$A немного выделяется. Таким образом,
для работы с входной строкой малого размера все эти методы
пригодны. Я еще раз позволю себе подчеркнуть, что точность
типа данных datetime в SQL сервере для таких промежутков
времени не позволяет достоверно измерить время выполнения. На
быстрых машинах ABA0163 и KESAMETSA для всех методов, кроме
REALSLOW и SLOW$LIKE, измеренная продолжительность как минимум
одного теста равнялась 0 мс.
[В
начало]
Тестовый набор
среднего размера
Результаты методов REALSLOW и SLOW$LIKE выглядят еще более
смехотворными. Теперь Вы понимаете, почему я не тестировал эти
методы для строк большого размера.
Оставшиеся семь методов все еще показывают результаты,
которые большинство людей сочло бы вполне приемлимыми. Однако,
можно увидеть, что методы EXEC$A и UNION начинают отставать от
других пяти. Метод UNION имеет ограничение на размер входного
параметра и поэтому не может быть проверен для строк большого
размера, если мы не будем разбивать их на порции. Но такой
целью я как раз и не задавался и именно по причине результатов
тестов. Глядя на эти цифры, не верится, что метод UNION сможет
составить серьезную конкуренцию другим методам.
Опять замечу, что на двух других машинах для некоторых
методов по-прежнему имело место нулевое время выполнения
тестов при нескольких запусках. Это говорит о том, что данные
все еще слишком малы для проведения достоверных измерений.
[В
начало]
Тестовый набор
большого размера
Результаты уже начинают различаться, что дает нам
возможность более достоверно оценить производительность того
или иного метода. При операции разбора строки метод FIX$SINGLE
по-прежнему также быстр, как и для тестового набора малой
длины. Но следует прежде всего обратить внимание на метод с
использованием динамического SQL, т.е. на EXEC$A. 2? секунды
на извлечение string элементов против 350 мс у других
методов делают данный метод неприемлимым для
использования.
[В
начало]
String Size
X-Large
Тестовый набор очень
большого размера
При тестовом наборе такого размера диамический SQL начинает
все больше выходить из под контроля. К чему это может
привести, можно увидеть в анализе
тенденций.
Т.к. данный тестовый набор является максимально возможным
для метода FIX$SINGLE (из-за того, что в таблице Numbers,
только 8000 записей, мы не сможем оперировать набор огромной
длины), то давайте подведем некоторые промежуточные итоги
тестирования производительости. Очень четко можно увидеть, что
использование элементов фиксированного размера и их извлечение
с помощью вспомогательной таблицы дают наименьшее время
выполнения. Так же можно заметить, что более простой метод fixstring_single
быстрее более сложного fixstring_multi.
Последний правда позволяет использовать теоритически
неограниченные по размеру входные данны. Но с другой стороны,
Вы можете добавить записи в таблицу Numbers,
чтобы увеличть количество обрабатываемых функцией
fixstring_single элементов списка. Правда, строка из 8000
элементов это достаточно большой набор.
Существуют, однако, две проблемы, которые могут существенно
понизить производительность метода с использованием элементов
фиксированной длины. Во-первых, это зависит от того, как
именно был использован этот метод. В своих тестах я
осуществлял вызов всех процедур через RPC, что является
стандартным способом для приложений. Выяснилось, что если
вместо этого использовать пакетное выполнение с помощью
команды EXEC, то при привышении длины входного параметра
некоторого значения производительность этих методов (и только
их) падала. FIX$SINGLE был все еще быстрее всех , но TBLNUM
обгонял FIX$MULTI и выходил на 2-ое место.
Вторая проблема становится видна, если мы проанализируем
разницу времени выполнения операций JOIN и UNPACK:
О чем говорят эти цифры, я точно судить не возьмусь. Они
могут говорить о том, что в 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. Особые наблюдения
В этой главе я обсуждаю некоторые наблюдения для методов,
не вошедших в список основных
соперников.
[В
начало]
Процедура
или Функция
В тестировании участвовали также процедурные
версии для метода
итераций. Я не включил эти результаты в основной список,
потому что тестируемые процедуры сильно отличались друг от
друга:
Сначала посмотрим на результаты работы процедур со списком
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).
[В
начало]
15.
Благодарности и обратная связь
Я не являюсь первооткрывателем всех методов. Эта статья
есть результат работы многих людей в SQL сообществе. Среди них
были и те, кто предлагал оригинальные идеи, и те, кто доводил
до ума интерфейс и тп. Я даже не могу упомянуть их, потому что
попросту не знаю их всех.
Вот список людей, которые вольно или невольно, зная или не
зная об этом, внесли вклад в написание этой статьи: SQL Server
MVPs Steve Kass, Linda Wierzbicki, Itzik Ben-Gan, Fernando
Guerrero, Umachandar Jaychandran, Narayana Vyas Kondreddi, Tom
Moreau, Bryant Likes, Bharathi Veeramac, Anith Sen, Ken
Henderson, Ivan Arjentinski и Joe Celko.
Если Вы можете предложить другие методы решения или указать
на особенности описанных в статье методов, которые, как Вы
считаете, выпали из поля моего зрения или у Вас есть другие
предложения и вопросы, то пишите мне по адресу sommar@algonet.se.
От переводчика
Со своей стороны хотел бы выразить благодарность Алексу
Сибилеву за помощь в переводе статьи. Жду Ваши предложения и
замечания по переводу по этому адресу.
Дата последнего обновления 13-03-05
21:16
На домашнюю
страничку Erland Sommarskog-а .
[В
начало]