SQL.RU
 client/server technologies
 Главная | Документация | Статьи | Книги | Форум | Блоги | Опросы | Гостевая | Рассылка | Работа | Поиск | FAQ |
   
#146<<  #147  >>#148

СОДЕРЖАНИЕ

1.БЕЗОПАСНОСТЬ
1.1.SQL Server 2000 service pack 3a
2.СОВЕТЫ
2.1.Массивы и Списки в SQL Server (Окончание)

БЕЗОПАСНОСТЬ

SQL Server 2000 service pack 3a (SP3a)

· Добавлена возможность обновления SQL Server 2000 Evaluation Edition.
· Включает новую версию Microsoft Data Access Components 2.71a (MDAC). Эта версия MDAC устраняет утечки памяти, описанные в статьях 814572 и 814410 Microsoft Knowledge Base.
· Отключает прослушивание порта 1434, когда сеть скрыта за защитным экраном.
· Улучшено мультисерверное администрирование.
· Добавлена новая диагностическая функция Transact-SQL. Администратор баз данных может использовать новую функцию fn_get_SQL для диагностики процессов.
· Расширены возможности SQL Server AGENT. Для запуска SQL Server AGENT теперь нет необходимость быть администратором.

SP3a включает следующие файлы:

Sql2ksp3.exe Обновление компонент базы данных. 56453 KB.
S ql2kasp3.exe Обновление компонент Analysis Services. 44617 KB.
Sql2kdesksp3.exe. Обновление Microsoft SQL Server 2000 Desktop Engine (2000 MSDE). 70805 KB.

[В начало]

СОВЕТЫ

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

По материалам статьи Erland Sommarskog (SQL Server MVP) Arrays and Lists in SQL Server.

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

Предисловие.
   Постановка задачи.
   Обзор методов решения.
   Основные соображения по интерфейсу.
   Метод итераций.
      Список integer элементов (List-of-integers).
      Список string элементов (List-of-strings).
   Метод с использованием вспомогательной таблицы
      Базовая функция.
      Входные данные неограниченного размера.
      Список элементов в поле таблицы.
   Массив элементов фиксированного размера.
      Массив элементов фиксированного размера и метод итераций.
      Возможные проблемы с производительностью и форматом данных.
   OPENXML
      Когда OPENXML НЕ нужен.
   Динамический SQL.
   Фокус с использованием UNION.
   Действительно медленные методы.
   SQL Server 7
      Процедура для списка string элементов.
      Экстравагантная процедура для списка integer значений.
   SQL Server 6.5
   Результаты тестов производительности.
      Общее предупреждение.
      Соперники по тестам.
      Каким образом проводилось тестирование.
      Результаты.
      Особые наблюдения.
      Домашнее задание.
   Благодарности и обратная связь.

[В начало]

SQL Server 7

Если Вы работаете с 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 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'''

[В начало]

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

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

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

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

[В начало]

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

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

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

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

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

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

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

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

[В начало]

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

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

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


   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 привыполнении тестов могли использоваться все четыре процессора.

[В начало]

Результаты

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

[В начало]

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

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

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

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

[В начало]

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

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

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

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

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

[В начало]

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

Метод UNPACK Str UNPACK Int JOIN Str JOIN Int
ITER 191 193 269 222
TBLNUM 127 112 204 136
FIX$SINGLE 58 56 156 83

FIX$MULTI

79 81 174 115
XMLATTR 265 262 341 286
EXEC$A     2489 1508

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

[В начало]

String Size X-Large

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

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

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

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

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

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

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

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

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

[В начало]

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

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

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

[В начало]

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

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

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

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

  t(s) = y + k·s

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

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

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

[В начало]

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

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

[В начало]

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

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

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

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

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

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

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

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


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

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

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

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

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

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

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

[В начало]

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

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

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

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

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

[В начало]

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

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

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

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

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

[В начало]

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

Если Вы хотите провести самостоятельное тестирование(возможно даже и своих собственных методов), то Вы можете скачать файл 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-а .

[В начало]

#146<<  #147  >>#148
Rambler's Top100 Рейтинг@Mail.ru  Administrator: Обратная связь 
Copyright: SQL.Ru 2000-2013