Рассылка закрыта
При закрытии подписчики были переданы в рассылку "Вопросы и ответы по MS SQL Server" на которую и рекомендуем вам подписаться.
Вы можете найти рассылки сходной тематики в Каталоге рассылок.
MS SQL Server - дело тонкое...
| Информационный Канал Subscribe.Ru |
| #145<< #146 |
|
СОДЕРЖАНИЕ
Массивы и Списки в SQL Server
По материалам статьи Erland Sommarskog (SQL Server MVP) Arrays and Lists in SQL Server. Перевод: Вячеслава Брылёва, aka Glory.
Предисловие. Часто в форумах можно видеть вопросы вида Как я могу использовать массивы
в SQL сервере? или Почему не работает запрос Это достаточно длинная статья, поэтому если Вы хотите просто узнать, как оперировать списком значений, разделенных запятыми, то выберите одну из 3-х ссылок, в зависимости от версии SQL сервера, которые вы используете: SQL2000, SQL7, SQL6.5. Если предложенные там решения не подходят Вам, тогда вернитесь назаад и начните читать сначала. :-) Если Вы впали в прострацию, увидев размер этой статьи, то Вы с облегчением узнаете, что это одна из тех статей, которые не обязательно читать целиком и по порядку. Если Вы относите себя к "чистым" SQL программистам, который хочет знать ответ на вопрос "Каким образом я могу ....?", то Вы можете найти, что раздел, посвященный производительности, содержит слишком много подробностей. С другой стороны истинные "зубры" SQL, которым интересны показатели производительности, могут найти описания методов немного утомительными и могут предпочесть просмотреть их вскользь. Имееются несколько значений для ключевого поля, определяющие некоторое количество записей таблицы. И необходимо выбрать эти записи. Если запрос составляется в клиентском приложении, то программный код может выглядеть приблизительно так:
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 Вы можете "распаковать" в таблицу. Большинство методов, описанных в данной статье, используют как раз такой метод. Существует несколько возможных решений задачи, которые могут быть применены в большинстве случаев. В статье предствалены только известные мне методы решения. Вот список этих методов, разделенный на две группы: Хорошие методы:
Методы, которые лучше не использовать:
Вы также можете ознакомиться с кратким обзором методов решения от Anith Sen по адресу http://www.bizdatasolutions.com/tsql/sqlarrays.asp. Хотя его оценки некоторых методов не всегда совпадают с моими. Основные соображения по интерфейсу Прежде чем приступить к рассмотрению методов решения, я хотел бы осветить некотрые общие вопросы. Некоторым эти вопросы могут показаться тривиальными, но я хотел бы донести до Вас очень важный вопрос о производительности, так что потерпите. Большинство представленных в статье методов могут быть оформлены в виде функций, возвращающих таблицу(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). Если все, что Вам нужно, это готовое решение, чтобы продолжить создание своего программного кода, то данный метод как раз для Вас. Хотя есть более быстрые методы, чем данный, но для практического подтверждения этого вам понадобятся входные данные действительно очень большого размера. Так что главный козырь этого метода - его простота. Всего делов-то - загружаете предложенные примеры функций и вперед и выше. А более быстрые методы требуют для работы наличия вспомогательной таблицы с правильными данными. Это конечно не такая чтобы уж большая проблема, но все же требует наличия еще одного объекта. Ко всему прочему программный код очень легок для понимания, что немаловажно, если у Вас есть опыт работы на C или Visual Basic. Не последнюю роль играет и то, что Вы можете адаптировать предложенный код для использования со входной строкой произвольного формата. Вот пример пользовательской функции в 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. Вот простая функция, которая возвращает таблицу со 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" т.е. элементы списка заключены в кавычки и разделитель может также являться частью элемента. Приведенная выше функция не воспринимает подобный формат, но можно легко переписать ее и для работы с таким форматом входных данных. Метод с использованием вспомогательной таблицы Самым быстрым методом извлечения элементов из спсика с разделителем является метод с использованием вспомогательной таблицы с числами. Это очень простая таблица, состоящая из одно поля типа 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 В первоначальном варианте функции для демонстрации алгоритма работы базовой функции ограничим длину входных данных 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 варианта при тестировании. Входные данные неограниченного размера Функция 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'
Список элементов как поле таблицы До сих пор в статье подразумевалось, что входной параметер представляет собой переданные из клиентского приложения текстовые данные. Но иногда такой список с разделителями может содержаться в поле(полях) таблицы. Например в таблице:
CREATE TABLE jackets (model varchar(30),
sizes varchar(200),
colours varchar(200))
поля sizes и colours могут содержать данные о размерах и цветах курток в виде списка элементов разделенных запятыми. Такая схема данных правда противоречит первой нормальной форме, что в большинстве случаев является результатом ошибок при проектировании. Но, оставляя эту тему в стороне, допустим, что нам нужно извлечить эти списки в отдельные таблицы. Вы, конечно, можете использовать любую из рассмотренных уже функций, но в любом случае Вы должны будете обработать каждую запись исходной таблицы в цикле, потому что невозможно передать поле таблицы в пользовательскую функцию в виде параметра. Возможно Вы уже знаете, что построчная обработка таблицы может быть значительно медленнее чем обработка таблицы как набора. Поэтому идея соединить исходную таблицу непосредственно с таблицей Numbers выглядит более лучшим решением. Я не привожу примера такой обработки непосредственно здесь, но Вы уже видели такой способ в функции duo_text_split_me, где мы применили его к таблице @slices. Массив с элементами фиксированного размера Этот метод, предложенный 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. Массив элементов фиксированной длины и Метод итераций Т.к. для метода итераций в этом случае изменяется лишь формат входных данных, а не сам алгоритм, то можно без проблем использовать метод итераций. Если метод со вспомогательной таблицей засталяет Вас нервничать, то тогда вот Вам выход. (Например, если ваша вспомогательная таблица не содержит необходимого числа записей, то Вы получите неправильные результаты.) Однако, выигрыш в производительности метода итераций при использовании входной строки с элементами фиксированной длины и при использовании разделителя не такой уж выдающийся. И он все равно хуже чем у метода со вспомогательной таблицей и входным списком с разделителем. Я не привожу здесь варианта функции для метода итераций с использованием списка элементов фиксированной длины, но в результатах тестирования вы можете увидеть ее оценку. Возможные проблемы с производительностью и форматом данных Если Вы взгляните на результаты тестов, то заметите этот метод опередил все другие. Но существует несколько потенциальных проблем, о которых Вы должны всегда помнить, особенно про самую последнюю. Потенциальная проблема номер 1: Строка с элементами фиксированной длины наиболее чувствителена к формату. Один лишний символ в произвольном месте строки может привести к тому, что часть строки справа от этого символа будет "поломана". Если Вы формируете строку в клиентском приложение, то эта проблема не выглядит серьезной, т.к. решается один только раз. Но, однако, если смотреть шире, то мы переходим к
Потенциальная проблема номер 2: Стоимость программирования составления
строки с элементами фиксированной длины больше чем строки с разделителем. Я не учитывал
этот фактор при тестировании производительности, т.к. это сделало бы сам процесс
тестирования более сложным. Однако, учитывайте и эти 2 фактора: Потенциальная проблема номер 3: Длина передаваемой из клиентского приложения строки больше в случае использования списка с элементами фиксированной длины. Т.е. имеет место больший сетевой траффик. Вот это действительно может быть проблемой для низкоскоростных соединений или перегруженных сетей. Сама по себе проблема проблема не выглядит такой уж серьезной, однако, рассмотрение объема передаваемой информации приводит нас к 4-ая и самая важная потенциальная проблема: Важное значение имеет то, как Вы осуществялете вызов процедуры. Я в своих тестах использовал RPC (remote procedure call) и рекомендую Вам этот способ вызова процедур из клиентского приложения. (Например, если в ADO Вы используете adCmdStoredProcedure для свойства CommandType объекта Command object, то Вы используете RPC.) Другой способ вызвать хранимую процедуру - это пакетное выполнение команд с помощью EXEC, т.е. так, как Вы делеает это в Query Analyzer. Я выяснил, что начинаная с некоторой длины входных данных этот метод становится несколько медленнее чем использование списка с разделителем и вспомогательной таблицей(см. функцию duo_text_split_me). Фактически при вызове через RPC альтернативная версия функции fixstring_multi оказалась несколько быстрее, чем приведенная чуть выше функция. Но при использовании пакетного выполнения команд, время отклика было несколько секунд, что очень далеко от приемлимого. Почему так происходит - я не знаю. Но размер входной строки определенно играет какую-то роль. Я не проверял точно, при какой длине входных данных эффект имеет место быть, но по приблизительным данным где-то в районе 8000, т.е. в районе максимально размера типа данных varchar. В последние годы на роль стандарта для обмена данными был выдвинут 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, т.к. разница между ними лишь в разборе элементов списка, но никак не в производительности. Мне попадался на глаза способ решения нашей задачи, в котором передаваемая в процедуру строка с разделителем с помощью функции replace преобразовывалась в XML строку для того, чтобы дальше использоваться в OPENXML. По-моему, это очень плохая идея. И вот почему:
Для списка 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'''
Вложенные кавычки делают вызов процедуры на основе этого метода сложноватым. Ну так каковы же недостатки этого метода?
Более подробно о динамическом SQL можно прочитать в моей статье The Curse and Blessings of Dynamic SQL. Часто, когда люди в форумах спрашивают о методах работы со списком с разделителем, то динамический SQL предлагается в качестве решения. Однако лично меня такое топорное решение раздражает, особенно когда существуют другие красивые и простые методы по превращению списка в таблицу. Поэтому если кто-то советует Вам использовать этот метод, не следуйте этому совету. А если Вы сами имеете привычку давать такие советы, то, пожалуйста, прекратите это делать. Есть одно исключение: в версии SQL Server 6.5 все другие методы могут оперировать только списком типа varchar(255), поэтому динамический SQL в этой версии есть возможно единственное жизнеспособное решение. 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 запросом. В тестах на производительность он вел себя даже хуже последнего. Действительно медленные методы В разделе Вопрос/Ответ одного из 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 раз медленнее самого быстрого метода. ПРОДОЛЖЕНИЕ СЛЕДУЕТ Статьи на русском языке
Ядро OLAP системы
Новые и обновлённые технические статьи Microsoft
XADM: MDAC 2.6 Breaks ADO MoveNext() Function with Exchange 2000
Importing And Analyzing Event Logs
Самые популярные темы недели
Ваше мнение об упражнениях SELECT на http://sql.ipps.ru
Queued Transactional Replication Рассылка: Вопросы и ответы по Microsoft SQL Server
Автор рассылки: Сергей Кошкин
Выпуск No. 15 от 2003-05-30
|
| #145<< #146 |
| http://subscribe.ru/
E-mail: ask@subscribe.ru |
Отписаться
Убрать рекламу |
| В избранное | ||
