Сравнение производительности некоторых SQL-операций при использовании целочисленных, символьных и GUID-ключей. Тесты проводились на Microsoft Server 2000 и 2005.

Содержание теста

Тест создает в базе данных 6 таблиц. Таблицы Т1 и Т11 имеют целочисленные первичные ключи типа int, Т1 связана с Т11 в отношении "один-ко-многим". Имеются аналогичные таблицы Т2 и Т21 с символьными ключами типа char(15). В каждой таблице имеется индекс на первичный ключ. Таблицы Т11 и Т21 имеют также индексы на внешние ключи. Для GUID-ключей были добавлены таблицы T3 и T31. Этот тест проводился только на MS SQL 2005.

В тесте определятся параметры-переменные:

В первой части теста выполняется создание и заполнение таблиц данными. Ключи генерируются последовательным образом, однако для символьных полей порядковый номер добавляемой записи преобразуется в "NKEY00000...", где N - порядковый номер, KEY0000 - символьное выражение, дополняющее длину преобразованного в строку номера до 15 (Это выполняет процедура GetCharKey).

Для полей Num в таблицах T1 и Т2 генерируются случайные значения в диапазоне (0, @MaxNum]. Значение @MaxNum следует выбирать меньшим в разы, чем количество записей в главной таблице.

Поля Title всех таблиц заполняются фиксированными значениями вида: "'Title T: KEY'", где Т - название таблицы, KEY - значение ее ключа, преобразованного в строку. Имеется возможность заполнять строки случайным образом, однако это резко увеличивает время, требуемое для заливки первоначальных данных. Тем не менее, для включения этого режима достаточно удалить комментарии в тексте вызов процедуры GetRandomString и установить комментарии на строку присвоения переменной @Title фиксированного значения.

Во второй части теста производятся запросы к обеим таблицам, вида:

SELECT @j = count(*)
   FROM T1 JOIN T11 ON T1.T1ID = T11.T1ID
   WHERE T1.Num = @k

Значение @k выбирается случайным образом из диапазона (0, @MaxNum]. Запрос представляет собой подсчет количества записей, попавших в запрос после эквисоединения двух таблиц по ключу с условием фильтрации по случайно выбираемым значением поля Num. При первоначально выбранных значениях @MaxNum меньших, чем @ParentsCount обеспечивается выборка более чем одной записи из главной таблицы и их соединение с записями из связанной таблицы. Поле, по которому происходит фильтрация также имеет индекс, избирательность которого напрямую зависит от соотношения @ParentsCount к @MaxNum. К каждой групп таблиц производится @ParentsCount запросов.

В третьей части теста производятся простые поисковые запросы в связанных таблицах по ключу, значение которого выбирается случайным образом из диапазона (0..@ParentsCount]. К каждой групп таблиц также производится @ParentsCount запросов.

Исходный текст теста

Текст SQL-скрипта теста вы можете скачать по ссылке на файл mssql_keys_test.sql в конце страницы

Условия проведения теста

В разное время для проведения теста использовались две конфигурации.

Конфигурация 1: MS SQL Server 2000 Evaluation, установленный на ПК Pentium-II-300, RAM 256, HDD 20 Gb UltraATA-33. Операционная система: Windows NT Workstation 4 SP5.

Конфигурация 2: MS SQL Server 2005 Developer, установленный на ПК AMD Athlon 2400, RAM 1 Gb, HDD WesternDigital 240 Gb Serial ATA 300. Операционная система: Windows XP Pro SP2.

Под SQL Server в обоих случаях выделялось не более 192 Мб физической памяти. Размер тестовой БД - 500 Мб (исключает динамическое увеличение), журнала - 50 Мб, Размер временной БД (tempdb) - 300 Мб.

Параметры:

Таким образом, на каждом прогоне в группу таблиц заносится: 10 000 записей в главную и 300 000 (400 000 и 500 000) - в связанную. Выполняется 10 циклов выполнения запросов, по 10 000 к каждой группе таблиц в цикле (всего 100 000 запросов к каждой группе). Так как соотношение @ParentsCount к @MaxNum составляет на всех прогонах 10, то в среднем за каждый запрос с эквисоединением выбирается 10*@ChildsCount записей (300, 400 и 500, соответственно).

Результаты

Наиболее интересны несколько выявленных наблюдений:

• Поиск по ключу символьного поля производится примерно так же быстро или даже быстрее, чем по целочисленному
• MS SQL 2005 производит соединение по целочисленным ключам примерно в 2 раза быстрее своего предшественника (относительно скорости соединения по символьным ключам)
• При использование GUID-ключей быстродействие сравнимо или даже лучше такового при целочисленных ключах
• Мощность компьютера возросла: в 8 раз по частоте процессора, в 10 раз по частоте шины контролера диска (ОЗУ в 4 раза можно не учитывать, т.к. объемы данных не изменились). При этом скорость обработки запросов выросла только в 5-6 раз.

Примечание:

¹ - для конфигурации с некластерными индексами доступная физическая память для SQL Server была увеличена до 256 Мб

Сергей Тарасов, июнь 2001. С дополнениями, февраль-апрель 2007