Целочисленные, символьные и GUID-ключи в MS SQL

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

Обновление от 2012-11-01. Более развернутый тест для SQL Server 2012 с учетом параметров хранения.

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

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

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

@ParentCount определяет количество записей в главной таблице
@ChildCount определяет количество записей в связанной таблице на каждую запись в главной
@StagesCount количество циклов повторения этапа выполнения запросов для получения усредненных результатов
@MaxNum максимальное значение для диапазона значений поля Num (0, @MaxNum]

В первой части теста выполняется создание и заполнение таблиц данными. Ключи генерируются последовательным образом, однако для символьных полей порядковый номер добавляемой записи преобразуется в "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 Мб.

Параметры:

Параметр Значение на прогоне
Прогон 1 Прогон 2 Прогон 3
@ParentsCount 10 000 10 000 10 000
@ChildsCount 30 40 50
@StagesCount 10 10 10
@MaxNum 1 000 1 000 1 000

Таким образом, на каждом прогоне в группу таблиц заносится: 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 раз.
Тест и этап Результаты
Таблицы с целочисленными ключами, мсек Таблицы с символьными ключами, мсек Таблицы с GUID-ключами, мсек Соотношение времени (2):(3):(4), %
(1) (2) (3) (4) (5)
Прогон 1 Прогон 2 Прогон 3(1) Прогон 1 Прогон 2 Прогон 3 Прогон 1

 

Прогон 2 Прогон 3 Прогон 1 Прогон 2 Прогон 3
MS SQL 2000 (конфигурация 1)                        
Заполнение данными 321003 396870 504393 355280 459153 665026 н/д н/д н/д 90 : 100 86 : 100 76 : 100
Выборка эквисоединением 7123 9555 10352 10042 13147 14743 н/д н/д н/д 70 : 100 73 : 100 70 : 100
Поиск по ключу 1475 1921 1899 1298 1546 1414 н/д н/д н/д 100 : 88 100 : 80 100 : 75
       
MS SQL 2005 (конфигурация 2)                        
Заполнение данными 64080 86670 100860 70233 102923 125313 61623 96703 107343 91 : 100 : 88 84 : 100 : 94 80 : 100 : 86
Выборка эквисоединением 821 996 1445 2312 2860 4388 1053 1135 1274 36 : 100 : 46 35 : 100 : 40 33 : 100 : 29
Поиск по ключу 244 265 263 241 198 213 214 180 202 100 : 99 : 88 100 : 75 : 68 100 : 81 : 77

С некластерными индексами
(конфигурация 2)

                       
Заполнение данными 67313 90470 108280 75906 113436 124986 72483 98063 133280 89 : 100 : 95 80 : 100 : 86 81 : 94 : 100
Выборка эквисоединением 1397 1882 3554 2199 2744 6086 970 1169 1855 64 : 100 : 44 69 : 100 : 43 58 : 100 : 30
Поиск по ключу 244 293 274 197 237 239 191 199 152 100 : 81 : 78 100 : 81 : 68 100 : 87 : 55

Примечание:

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

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

set nocount on
go
 
-- таблицы с целочисленными ключами
create table T1
(
	T1ID int not null,
	Num int not null,
	Title varchar(100) null
	constraint PK_T1 primary key (T1ID),
)
go
create index IX_T1 on T1(Num)
go
 
create table T11
(
	T11ID int not null,
	T1ID int not null,
	Title varchar(100) null,
	constraint PK_T11 primary key (T11ID),
	foreign key(T1ID) references T1(T1ID)
)
go
create index IX_T11 on T11(T1ID)
go
 
-- таблицы с символьными ключами
create table T2
(
	T2ID char(15) not null,
	Num int not null,
	Title varchar(100) null
	constraint PK_T2 primary key (T2ID),
)
go
create index IX_T2 on T2(Num)
go
 
create table T21
(
	T21ID char(15) not null,
	T2ID char(15) not null,
	Title varchar(100) null,
	constraint PK_T21 primary key (T21ID),
	foreign key(T2ID) references T2(T2ID)
)
go
create index IX_T21 on T21(T2ID)
go
 
-- служебные процедуры и функции
create procedure GetRandomString
	@Length int = 0,
	@Result varchar(255) output
as
begin
	if @Length > 255
		set @Length = 255
 
	declare @i int
	declare @ch char(1)
 
	set @i = 0
	set @Result = ''
	while (@i < @Length) begin
		set @ch = char(convert(int, (rand() * (128 - 33)) ) + 33)
		set @Result = @Result + @ch
		set @i = @i + 1
	end
end
go
 
 
create procedure GetCharKey
	@Key int,
	@Result char(15) output
as
begin
	declare @s varchar(15)
	declare @i int
	set @s = cast(@Key as varchar(15))
	set @i = 15 - len(@s)
	set @Result = @s + 'KEY' + replicate('0', @i - 3)
end
go
 
-- заполнение таблиц данными
declare @ParentCount	int		-- общее количество записей в главной таблице
declare @ChildCount int			-- количество дочерних записей для каждой родительской
declare @StagesCount int		-- оличество циклов повторения этапа выполнения запросов для получения усредненных результатов
declare @MaxNum int					-- максимальное значение для диапазона значений поля Num (0, @MaxNum]
declare @i int, @j int, @k int, @Stage int
declare @Title varchar(100)
declare @ParentKey char(15), @ChildKey char(15)
declare @StartTime datetime
declare @Time1 int, @Time2 int
 
set @ParentCount = 10000
set @MaxNum = @ParentCount / 10
set @ChildCount = 30
 
-- таблицы с целочисленными ключами
set @i = 1
set @k = 1
select @StartTime = getdate()
while (@i <= @ParentCount) begin
-- вызов формирования строки из случайного набора символов закомментарен для быстроты прохождения этапа заливки данных
-- вместо этого используем строку, включающую ключи и фиксированный текст
--	exec GetRandomString 100, @Title output
	exec GetCharKey @i, @ParentKey output  -- для уравнивания количества требуемых операция при заливке
	set @Title = 'Title T1: ' + cast(@i as varchar)
	insert into T1 (T1ID, Num, Title) values (@i, cast((rand() * @MaxNum) as int)+ 1, @Title)
	set @j = 1
	while (@j <= @ChildCount) begin
--		exec GetRandomString 100, @Title output
		set @Title = 'Title T11: ' + cast(@k as varchar) + ' ' + cast(@i as varchar)
		exec GetCharKey @k, @ChildKey output
		insert into T11 (T11ID, T1ID, Title) values (@k, @i, @Title)
		set @j = @j + 1
		set @k = @k + 1
	end
	set @i = @i + 1
end
print N'Закачка данных (таблицы с целочисленными ключами) завершена за ' + cast(datediff(ms, @StartTime, getdate()) as varchar) + N' мсек'
 
 
-- таблицы с символьными ключами
set @i = 1
set @k = 1
select @StartTime = getdate()
while (@i <= @ParentCount) begin
--	exec GetRandomString 100, @Title output
	set @Title = 'Title T2: ' + cast(@i as varchar)
	exec GetCharKey @i, @ParentKey output
	insert into T2 (T2ID, Num, Title) values (@ParentKey, cast((rand() * @MaxNum) as int)+ 1, @Title)
	set @j = 1
	while (@j <= @ChildCount) begin
--		exec GetRandomString 100, @Title output
		set @Title = 'Title T21: ' + cast(@k as varchar) + ' ' + cast(@i as varchar)
		exec GetCharKey @k, @ChildKey output
		insert into T21 (T21ID, T2ID, Title) values (@ChildKey, @ParentKey, @Title)
		set @j = @j + 1
		set @k = @k + 1
	end
	set @i = @i + 1
end
print N'Закачка данных (таблицы с символьными ключами) завершена за ' + cast(datediff(ms, @StartTime, getdate()) as varchar) + N' мсек'
 
 
set @StagesCount = 10
set @Stage = 1
set @Time1 = 0
set @Time2 = 0
while (@Stage <= @StagesCount) begin
	-- выполнение запросов со связью по ключу
	set @i = 1
	set @j = 1
	select @StartTime = getdate()
	while (@i <= @ParentCount) begin
		set @k = cast((@MaxNum * rand()) as int) + 1
		select @j = count(*)
			from T1 join T11 on T1.T1ID = T11.T1ID
			where T1.Num = @k
		set @i = @i + 1
	end
	set @Time1 = @Time1 + datediff(ms, @StartTime, getdate())
 
	set @i = 1
	select @StartTime = getdate()
	while (@i <= @ParentCount) begin
		set @k = cast((@MaxNum * rand()) as int) + 1
		select @j = count(*)
			from T2 join T21 on T2.T2ID = T21.T2ID
			where T2.Num = @k
		set @i = @i + 1
	end
	set @Time2 = @Time2 + datediff(ms, @StartTime, getdate())
	set @Stage = @Stage + 1
end
 
print N'Проведено циклов: ' + cast(@StagesCount as varchar)
print N'Запросы выполнены (таблицы с целочисленными ключами) за ' + cast((@Time1/@StagesCount) as varchar) + N' мсек'
print N'Запросы выполнены (таблицы с символьными ключами) за ' + cast((@Time2/@StagesCount) as varchar) + N' мсек'
 
set @Stage = 1
set @Time1 = 0
set @Time2 = 0
while (@Stage <= @StagesCount) begin
	-- выполнение запросов со связью по ключу
	set @i = 1
	set @j = 1
	select @StartTime = getdate()
	while (@i <= @ParentCount) begin
		set @k = cast((@ChildCount * rand()) as int) + 1
		exec GetCharKey @k, @ParentKey output
		select @j = count(*)
			from T11
			where T11.T1ID = @k
		set @i = @i + 1
	end
	set @Time1 = @Time1 + datediff(ms, @StartTime, getdate())
 
	set @i = 1
	select @StartTime = getdate()
	while (@i <= @ParentCount) begin
		set @k = cast((@ChildCount * rand()) as int) + 1
		exec GetCharKey @k, @ParentKey output
		select @j = count(*)
			from T21
			where T21.T21ID = @ParentKey
		set @i = @i + 1
	end
	set @Time2 = @Time2 + datediff(ms, @StartTime, getdate())
	set @Stage = @Stage + 1
end
 
print N'Проведено циклов: ' + cast(@StagesCount as varchar)
print N'Запросы выполнены (таблицы с целочисленными ключами) за ' + cast((@Time1/@StagesCount) as varchar) + N' мсек'
print N'Запросы выполнены (таблицы с символьными ключами) за ' + cast((@Time2/@StagesCount) as varchar) + N' мсек'
go
 
-- очистка БД после теста
drop table T11
drop table T1
drop table T21
drop table T2
 
drop procedure GetRandomString
drop procedure GetCharKey
go
 
set nocount off
go