Рассылка закрыта
При закрытии подписчики были переданы в рассылку "Вопросы и ответы по MS SQL Server" на которую и рекомендуем вам подписаться.
Вы можете найти рассылки сходной тематики в Каталоге рассылок.
MS SQL Server - дело тонкое...
| Информационный Канал Subscribe.Ru |
| #146<< #147 |
|
СОДЕРЖАНИЕ
SQL Server 2000 service pack 3a (SP3a)
· Добавлена возможность обновления SQL Server 2000 Evaluation Edition. SP3a включает следующие файлы:
Sql2ksp3.exe Обновление компонент базы данных. 56453 KB. Массивы и Списки в SQL Server
По материалам статьи Erland Sommarskog (SQL Server MVP) Arrays and Lists in SQL Server. Перевод: Вячеслава Брылёва, aka Glory.
Предисловие. Если Вы работаете с SQL Server 7, то у Вас нет возможности использовать пользовательские функции или XML. Поэтому в процедуре Вы можете использовать метод итераций или вспомогательную таблицу, для списка с разделителем или списка элементов фиксированной длины. Здесь я привожу пример для метода итераций. Вы можете легко адаптирвать его к двум другим методам. Процедура для работы со списком 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. Экстравагантная процедура для списка 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, то предупреждение будет считаться уже ошибкой, что вызовет соответсвующую реакцию в клиентском приложении. При использовании 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'''
Результаты тестов на производительность Тестирование производительности СУБД не простая задача. Существует много факторов, влияющих на производительность. В своих тестах я проверял производительность разбора одной входной строки в таблицу (в методах, где это возможно) а также производительность извлечения данных из таблицы на основе переданного списка. Операция простого извлечения элементов из списка проверяет собственно сам метод, но при использовании метода для извлечения данных из другой таблицы(таблиц) большое влияние начинают оказывать свойства этой таблицы(таблиц), такие как индексы, статистика и тд. Возможно, Вы найдете, что методы, объявленные мной как быстрые, создают медленный план выполнения при использовании в запросе. Другая проблема заключается в том, каким образом Вы запускаете SQL запросы на выполнение. Для функций, оперирующих со строками конечной длины, я заметил, что существует значительная разница между RPC вызовами и командными пакетами(command-text batches). (Все результаты тестов были получены с использованием RPC.) И еще одна проблема заключается в том, как поведет себя метод при общей загруженности системы. Для исключения такого влияния я все тесты проводил на специально выделенных под это машинах. Таким образом, воспринимайте эти результаты такими, какие они есть. Они, безусловно, служат для определения безнадежно плохих методов. Но при самостоятельном использовании Вы можете выяснить, что некоторые из плохих методов имеют прекрасные показатели производителности. Если Вас волнует вопрос производителности в контексте конкретной задачи, то Вы, возможно, проведете свое исследование производительности. При написании этой статьи я протестировал очень много методов, для того чтобы включить полученные результаты в данный текст. Некоторые методы имели лишь незначительные отличия друг от друга. Например, возвращали varchar вместо nvarchar. Но я не зaдавался целью запомнить полученные результаты для каждой вариации. В конечном счете, я получил данные тестов для 16 случаев. Из них я выбрал 9 и включил их в основной список результатов тестов. Еще несколько результатов дано в разделе специальных наблюдений где я подметил некоторые особенности. Полный перечень результатов для всех 16 методов, полученный на 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 элементов. Я использовал списки пяти различных размеров:
Все методы тестировались со списками малого и среднего размеров. Для списков большого размера и выше я исключил из тестирования UNION, INSERT, REALSLOW и SLOW$LIKE, первые 2 потому, что они не могут опрерировать входными данными такого размера, последние два потому, что они слишком медленны для того, чтобы я смог дождаться результатов. Для списка очень большого размера я исключил из тестирования TBLNUM$IL потому, что он не мог оперерировать входными данными такого размера. Для списка огромного размера я исключил из тестирования FIX$SINGLE потому, что он не мог опрерировать входными данными такого размера и ITER$EXEC, EXEC$A и EXEC$B потому, что они слишком медленны. Замечу еще, что данные выше размеры списков были взяты из единожды сгененерированного тестового набора. Для многих методов входная строка приводилась к конкретному специфическому для метода формату, поэтому ее длина могла оказаться как меньше (при удалении вложенных пробелов) так и больше (XML или список элементов с фиксированной длиной). Но действительное число элементов для всех методов остовалось таким же. Я тестировал две операции со списками:
Вот пример типичной процедуры операции 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 теста по причине того, что эти методы чересчур медленные. Тестирование проводилось на трех разных машинах:
Тестовый скрипт всегда запускался на KESAMETSA. К ABA0163 и JAMIE4K коннект осуществлялся через корпоративную VPN. Все машины во время тестирования не были в общем загружены другими задачами. Для представления результатов тестов в самой статье использются результаты полученные только для JAMIE4K. Я считаю, что эти цифры более показательны по следующим двум причинам: 1) эта машина является единственным настоящим сервером и поэтому больше похожа на production машину. 2) Т.к. у JAMIE4K более медленные CPUs, то измерение времени выполнения тестов на ней более точное. (Время в SQL сервере вычисляется с точностью до 3.33 миллисекунд, поэтому время исполнения меньше 50мс невозможно измерить достоверно.) Т.к. тестовая таблица достаточно мала, чтобы поместиться в кэш при выполненни теста, то это приводит к большему использованию CPU. Поэтому для большинства методов ABA0163 была в 2 раза быстрее чем JAMIE4K и в 4 раза быстрее чем KESAMETSA. Но есть одно исключение из этого правила, а именно то, что JAMIE4K привыполнении тестов могли использоваться все четыре процессора. Ниже я представляю среднее время выполнения в миллисекундах каждого из девяти основных соперников на машине 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 мс у других методов делают данный метод неприемлимым для использования. Тестовый набор очень большого размера
При тестовом наборе такого размера диамический 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 символов не заставляют тревожиться о производительности. Тестовый набор огромного размера
Для входных данных такого размера трудно ожидать, что на таком оборудовании время выполнения будет измеряться долями секунды. В данной ниже таблице показано, как возрастает время выполнения в зависимости от размера тестового набора. Приведены наименьшие и наибольшие значения для каждого метода:
Разумно предположить, что время выполнения для строки некоторой длины определяется линейны уравнением: t(s) = y + k·s Где s - это размер строки, y - некоторая начальная задержка и k - постоянная, значение которой близко к 1. Величины y и k конечно же различаются для разных методов. На основе этой таблицы можно сделать два вывода: 1) похоже, что чем быстрее метод, тем меньше коэффициент (k). Т.е. даже при возрастании размера входных данных более быстрый метод сохраняет свою позицию. 2) динамический SQL не подтверждает предположение о том, что значение k близко к 1, похоже, что оно скорее ближе к 2 (если мы считаем уравнение по-прежнему линейным). Это означает что при 2-хкратном увеличении размера входных данных, время выполнения динамического SQL возрастет в 4 раза. Помнится я говорил что-то подобное выше, не так ли? Примечание: данное линейное уравнение справедливо до тех пор, пока нет ограничения на используемые ресурсы. Как я заметил раньше, для метода с использованием элементов с фиксированной длиной замечено падение производительности при достижении длины входных данных определенной величины при запуске в пакетном режиме. В этой главе я обсуждаю некоторые наблюдения для методов, не вошедших в список основных соперников. В тестировании участвовали также процедурные версии для метода итераций. Я не включил эти результаты в основной список, потому что тестируемые процедуры сильно отличались друг от друга:
Сначала посмотрим на результаты работы процедур со списком string элементов:
Можно видеть, что процедуры в действительности быстрее функций, если мы используем их только для разбора строки. Это удивляет, особенно в свете того, что процедуры перекомпилируются при каждом вызове. Я вижу две возможных причины этого - или временные таблицы в общем быстрее чем таблицы-переменные или процедуры в общем быстрее 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 элементов?
Не удивительно, что наша попытка использовать нечто феерическое отразилась на производительности. Однако все не так трагично, как например для маленького динамического SQL в ITER$EXEC. И если Вы вернетесь к основным результатам тестов, то увидите, что эта процедура быстрее, чем XML. Но что более примечательно, так эта разница значений delta для тестовых наборов большого и очень большого размеров. Эта разница не может быть объяснена только экстравагантностью метода. Возможная причина опять же может заключаться в разных планах выполнения. Т.к. тестовая таблица имеет кластерный индекс по полю типа integer, то можно предположить, что нет альтернативы в вопросе выбора стратегии выполнения запроса. Но у SQL сервера остается еще выбор одного из трех способов соединения таблиц. Я не исследовал, как именно происходит выполнение, но об этом предупредил заранее. Вы можете самостоятельно получить более точные данные. Хотел бы подчеркнуть, что имеющаяся разница между процедурой и функцией относится к методу итераций, который выполняет большее количество запросов INSERT, чем какой-либо другой метод. Я бы не рискнул сделать такие же предположения и для других методов. В тестировании участвовали две функцииn по извлечению элемента из строки с разделителем с помощью вспомогательной таблицы: multi-step функция TBLNUM и inline функця TBLNUM$IL(с ограничением длины строки в 7998 символов). Вот результаты тестов для входной строки большого размера:
Может сложится мнение, что multi-step функции в действительности быстрее inline функции, но на самом деле это частично обман зрения. Multi-step функция использует тип данных nvarchar, а inline функция - varchar (по-другому не получается оперировать тестовым набором нужного размера). Если обе функции используют тип данных varchar, то inline функция быстрее приблизительно на 5%. Но т.к. для тестового набора такого размера время выполнения в любом случае среднее, то эти рассуждения носят скорее общепозновательный характер. Хотел отметить, что разница(или если угодно совпадение) времени выполнения inline и multi-step функций зависит от входных данных. Я не проводил отдельного исследования для элементов фиксированной длины, для которых выводы могут получиться другими. EXEC$B не является на самом деле методом. EXEC$B есть повторное выполнение точно такого же динамического SQL. Его поведение при тестировании было вызывающим. На этот раз, я представляю результаты тестов на всех машинах:
При сравнении этих результатов выполнения на 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, то не трудно догадаться, что используется другой план выполнения. ...Но почему же планировщику не приходит в голову мысль использовать и некластерный индекс, когда это возможно? Если Вы хотите провести самостоятельное тестирование(возможно даже и своих собственных методов), то Вы можете скачать файл arraylist.zip, который содержит тестовый скрипт, тестовые процедуры и функции для различных методов и таблицу с номерами (размер файла около 30 KB). В файле README.HTML можно найти инструкции по применению. Результаты тестов можно получить в отдельном файле usrdictwords.zip (его размер около 6 MB). Благодарности и обратная связь Я не являюсь первооткрывателем всех методов. Эта статья есть результат работы многих людей в 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.
От переводчика Со своей стороны хотел бы выразить благодарность Алексу Сибилеву за помощь в переводе статьи. Жду Ваши предложения и замечания по переводу по этому адресу. Дата последнего обновления 03-03-30 21:08 На домашнюю страничку Erland Sommarskog-а . Статьи на русском языке
Генерация отчетов по вашим моделям ERwin
Новые и обновлённые технические статьи Microsoft
Apparent Memory Leak in SQL Server When You Run Commerce Server DTS Packages
The Curse and Blessings of Dynamic SQL
Самые популярные темы недели
Ваше мнение об упражнениях SELECT на http://sql.ipps.ru
OLE и Image Рассылка: Вопросы и ответы по Microsoft SQL Server
Автор рассылки: Сергей Кошкин
Выпуск No. 16 от 2003-06-06
Сделав заказ коробочного продукта с одним из языков, входящих в среду разработки Visual Studio .NET 2003, вы
можете получить от Microsoft Russia в подарок одну из книг на выбор:
ДЛЯ ПОЛУЧЕНИЯ ПОДАРКА НЕОБХОДИМО:
Подробнее о получении книг Вы можете узнать на сайте Microsoft
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| http://subscribe.ru/
E-mail: ask@subscribe.ru |
Отписаться
Убрать рекламу |
| В избранное | ||
