Почему последовательные ключи GUID работают быстрее, чем последовательные клавиши INT в моем тестовом случае?

После запроса этого вопрос, сравнивающий последовательные и непоследовательные GUID, я попытался сравнить производительность INSERT с 1) таблицей с первичным ключом GUID, инициализированным последовательно с помощью newsequentialid() и 2) таблицей с первичным ключом INT инициализируется последовательно с идентификатором identity(1,1). Я ожидал бы, что последний будет самым быстрым из-за меньшей ширины целых чисел, и также кажется более простым генерировать последовательное целое число, чем последовательный GUID. Но, к моему удивлению, INSERT на таблице с целым ключом были значительно медленнее, чем последовательная таблица GUID.

Это показывает среднее время использования (мс) для тестовых прогонов:

NEWSEQUENTIALID()  1977
IDENTITY()         2223

Кто-нибудь может это объяснить?

Был использован следующий эксперимент:

SET NOCOUNT ON

CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

CREATE TABLE TestInt (Id Int NOT NULL identity(1,1) PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

DECLARE @BatchCounter INT = 1
DECLARE @Numrows INT = 100000


WHILE (@BatchCounter <= 20)
BEGIN 
BEGIN TRAN

DECLARE @LocalCounter INT = 0

    WHILE (@LocalCounter <= @NumRows)
    BEGIN
    INSERT TestGuid2 (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
    SET @LocalCounter +=1
    END

SET @LocalCounter = 0

    WHILE (@LocalCounter <= @NumRows)
    BEGIN
    INSERT TestInt (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
    SET @LocalCounter +=1
    END

SET @BatchCounter +=1
COMMIT 
END

DBCC showcontig ('TestGuid2')  WITH tableresults
DBCC showcontig ('TestInt')  WITH tableresults

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate)) AS [NEWSEQUENTIALID()]
FROM TestGuid2
GROUP BY batchNumber

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate)) AS [IDENTITY()]
FROM TestInt
GROUP BY batchNumber

DROP TABLE TestGuid2
DROP TABLE TestInt

UPDATE: Изменив скрипт для выполнения вставок на основе таблицы TEMP, как в примерах Фила Сандлера, Митча Пшеница и Мартина ниже, я также обнаружил, что IDENTITY быстрее, как и должно быть. Но это не обычный способ вставки строк, и я до сих пор не понимаю, почему эксперимент поступил неправильно: даже если я опускаю GETDATE () из моего исходного примера, IDENTITY () все еще медленнее. Таким образом, кажется, что единственный способ заставить IDENTITY () превосходить NEWSEQUENTIALID () - подготовить строки для вставки во временную таблицу и выполнить множество вставок в виде пакетной вставки, используя эту временную таблицу. В общем, я не думаю, что мы нашли объяснение этому феномену, и IDENTITY () все еще кажется более медленным для большинства практических занятий. Кто-нибудь может это объяснить?

37 голосов | спросил someName 14 Mayam11 2011, 02:02:46

6 ответов


18

Я изменил код @Phil Sandler, чтобы удалить эффект вызова GETDATE () (могут быть задействованы аппаратные эффекты /прерывания?) и сделал строки одинаковой длины.

[С момента появления SQL Server 2000 было несколько статей, связанных с проблемами синхронизации и таймерами с высоким разрешением, поэтому я хотел свести к минимуму этот эффект.]

В простой модели восстановления с данными и лог-файлом, размер которых зависит от того, что требуется, приведены тайминги (в секундах): (Обновлено с новыми результатами на основе точного кода ниже)

       Identity(s)  Guid(s)
       ---------    -----
       2.876        4.060    
       2.570        4.116    
       2.513        3.786   
       2.517        4.173    
       2.410        3.610    
       2.566        3.726
       2.376        3.740
       2.333        3.833
       2.416        3.700
       2.413        3.603
       2.910        4.126
       2.403        3.973
       2.423        3.653
    -----------------------
Avg    2.650        3.857
StdDev 0.227        0.204

Используемый код:

SET NOCOUNT ON

CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(88))

CREATE TABLE TestInt (Id Int NOT NULL identity(1,1) PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

DECLARE @Numrows INT = 1000000

CREATE TABLE #temp (Id int NOT NULL Identity(1,1) PRIMARY KEY, rowNum int, adate datetime)

DECLARE @LocalCounter INT = 0

--put rows into temp table
WHILE (@LocalCounter < @NumRows)
BEGIN
    INSERT INTO #temp(rowNum, adate) VALUES (@LocalCounter, GETDATE())
    SET @LocalCounter += 1
END

--Do inserts using GUIDs
DECLARE @GUIDTimeStart DateTime = GETDATE()
INSERT INTO TestGuid2 (SomeDate, batchNumber) 
SELECT adate, rowNum FROM #temp
DECLARE @GUIDTimeEnd  DateTime = GETDATE()

--Do inserts using IDENTITY
DECLARE @IdTimeStart DateTime = GETDATE()
INSERT INTO TestInt (SomeDate, batchNumber) 
SELECT adate, rowNum FROM #temp
DECLARE @IdTimeEnd DateTime = GETDATE()

SELECT DATEDIFF(ms, @IdTimeStart, @IdTimeEnd) AS IdTime, DATEDIFF(ms, @GUIDTimeStart, @GUIDTimeEnd) AS GuidTime

DROP TABLE TestGuid2
DROP TABLE TestInt
DROP TABLE #temp
GO

После прочтения исследования Мартина я снова начал использовать предлагаемый TOP (@num) в обоих случаях, то есть

...
--Do inserts using GUIDs
DECLARE @num INT = 2147483647; 
DECLARE @GUIDTimeStart DATETIME = GETDATE(); 
INSERT INTO TestGuid2 (SomeDate, batchNumber) 
SELECT TOP(@num) adate, rowNum FROM #temp; 
DECLARE @GUIDTimeEnd DATETIME = GETDATE();

--Do inserts using IDENTITY
DECLARE @IdTimeStart DateTime = GETDATE()
INSERT INTO TestInt (SomeDate, batchNumber) 
SELECT TOP(@num) adate, rowNum FROM #temp;
DECLARE @IdTimeEnd DateTime = GETDATE()
...

и вот результаты синхронизации:

       Identity(s)  Guid(s)
       ---------    -----
       2.436        2.656
       2.940        2.716
       2.506        2.633
       2.380        2.643
       2.476        2.656
       2.846        2.670
       2.940        2.913
       2.453        2.653
       2.446        2.616
       2.986        2.683
       2.406        2.640
       2.460        2.650
       2.416        2.720

    -----------------------
Avg    2.426        2.688
StdDev 0.010        0.032

Мне не удалось получить фактический план выполнения, так как запрос никогда не возвращался! Похоже, ошибка. (Запуск Microsoft SQL Server 2008 R2 (окончательная первоначальная версия) - 10.50.1600.1 (X64))

ответил Mitch Wheat 14 Mayam11 2011, 05:48:44
18

В новой базе данных в простой модели восстановления с файлом данных размером 1 ГБ и файлом журнала на 3 ГБ (ноутбук, оба файла на одном диске) и интервал восстановления, установленный на 100 минут (чтобы избежать контрольной точки, искажающей результаты ) Я вижу похожие результаты для вас с помощью одной строки inserts.

Я проверил три случая: для каждого случая я сделал 20 партий вставки 100 000 строк в отдельные таблицы. Полные скрипты можно найти в истории изменений этого ответа .

CREATE TABLE TestGuid
  (
     Id          UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
     SomeDate    DATETIME, batchNumber BIGINT, FILLER CHAR(100)
  )

CREATE TABLE TestId
  (
     Id          Int NOT NULL identity(1, 1) PRIMARY KEY,
     SomeDate    DATETIME, batchNumber BIGINT, FILLER CHAR(100)
  )

CREATE TABLE TestInt
  (
     Id          Int NOT NULL PRIMARY KEY,
     SomeDate    DATETIME, batchNumber BIGINT, FILLER  CHAR(100)
  )  

В третьей таблице тест вставил строки с инкрементирующим значением Id, но это было рассчитано автоматически, увеличивая значение переменной в цикле.

Усреднение времени, проведенного по 20 партиям, дало следующие результаты.

NEWSEQUENTIALID() IDENTITY()  INT
----------------- ----------- -----------
1999              2633        1878

Заключение

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

Когда я вставляю код вставки, описанный выше, в хранимые процедуры и просматриваю sys.dm_exec_procedure_stats, он дает следующие результаты

proc_name      execution_count      total_worker_time    last_worker_time     min_worker_time      max_worker_time      total_elapsed_time   last_elapsed_time    min_elapsed_time     max_elapsed_time     total_physical_reads last_physical_reads  min_physical_reads   max_physical_reads   total_logical_writes last_logical_writes  min_logical_writes   max_logical_writes   total_logical_reads  last_logical_reads   min_logical_reads    max_logical_reads
-------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
IdentityInsert 20                   45060360             2231067              2094063              2645079              45119362             2234067              2094063              2660080              0                    0                    0                    0                    32505                1626                 1621                 1626                 6268917              315377               276833               315381
GuidInsert     20                   34829052             1742052              1696051              1833055              34900053             1744052              1698051              1838055              0                    0                    0                    0                    35408                1771                 1768                 1772                 6316837              316766               298386               316774

Таким образом, в этих результатах total_worker_time примерно на 30% выше. Это означает

  

Общее количествоВремя процессора, в   микросекунды, потребляемые   выполнение этой хранимой процедуры   поскольку он был скомпилирован.

Таким образом, он просто выглядит так, как если бы код, генерирующий значение IDENTITY, был более интенсивным, чем процессор, который генерирует NEWSEQUENTIALID() (разница между двумя цифрами равна 10231308, который в среднем составляет около 5 мкс на вставку.), И что для определения этой таблицы эта фиксированная стоимость ЦП была достаточно высокой, чтобы перевешивать дополнительные логические считывания и записи, вызванные большей шириной ключа. (NB: Ицик Бен Ган сделал аналогичное тестирование здесь и нашел штраф в размере 2 мкс за вставку)

Итак, почему IDENTITY больше ЦП, чем UuidCreateSequential?

Я считаю, что это объясняется в этой статье . Для каждого генерируемого значения десятой identity SQL Server должен записать изменения в системные таблицы на диске

Что можно сказать о вставках MultiRow?

Когда 100 000 строк вставляются в один оператор, я обнаружил, что разница исчезла, несмотря на незначительное преимущество в случае с кодом GUID, но нигде рядом с результатами четкого сокращения. В среднем по 20 партий в моем тесте было

NEWSEQUENTIALID() IDENTITY()
----------------- -----------
1016              1088

Причина того, что в коде Фила не имеет никакого штрафа, и первый набор результатов Митча - это потому, что так получилось, что код, который я использовал для добавления многострочной строки, использовал SELECT TOP (@NumRows)

Это, по-видимому, полезно, так как есть определенная точка опроса, в которой он добавит дополнительную операцию сортировки для (предположительно последовательного!) GUID s.

GUID Sort

Эта операция сортировки не требуется из пояснительного текста в BOL .

  

Создает идентификатор GUID, который больше, чем   любой GUID, ранее сгенерированный этим   на определенном компьютере с   Windows была запущена. После перезапуска   Windows, GUID может начать снова с   более низкий диапазон, но по-прежнему глобально   уникальный.

Таким образом, мне показалось, что ошибка или отсутствие оптимизации SQL Server не распознает, что результат вычисления скаляра уже будет предварительно отсортирован, как это, по-видимому, уже используется для столбца identity. ( Изменить Я сообщил об этом, и ненужная проблема сортировки теперь исправлена ​​в Denali )

ответил Martin Smith 14 Mayam11 2011, 06:18:00
7

Довольно просто: с GUID дешевле сгенерировать следующий номер в строке, чем для IDENTITY (текущее значение GUID не нужно хранить, IDENTITY должен быть). Это верно даже для NEWSEQUENTIALGUID.

Вы можете сделать тест более справедливым и использовать SEQUENCER с большим CACHE - который дешевле, чем IDENTITY.

Но, как говорит М.Р., для GUID есть некоторые основные преимущества. На самом деле они намного более масштабируемы, чем столбцы IDENTITY (но только если они НЕ последовательны).

Смотрите: http: //blog.kejser. орг /2011/10 /05 /повышение вставление-скорость по-генераторная масштабируемый клавиш /

ответил Thomas Kejser 5 Jam1000000amSun, 05 Jan 2014 04:55:37 +040014 2014, 04:55:37
3

Я очарован этим типом вопроса. Почему вы должны были опубликовать его в пятницу вечером? :)

Я думаю, что даже если ваш тест ТОЛЬКО предназначен для измерения производительности INSERT, вы (возможно) ввели ряд факторов, которые могут вводить в заблуждение (цикл, длительная транзакция и т. д.).

Я не полностью убежден, что моя версия ничего доказывает, но личность работает лучше, чем GUID в ней (3,2 секунды против 6,8 секунд на домашнем ПК):

SET NOCOUNT ON

CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER NOT NULL DEFAULT NEWSEQUENTIALID() PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

CREATE TABLE TestInt (Id Int NOT NULL identity(1,1) PRIMARY KEY,
SomeDate DATETIME, batchNumber BIGINT, FILLER CHAR(100))

DECLARE @Numrows INT = 1000000

CREATE TABLE #temp (Id int NOT NULL Identity(1,1) PRIMARY KEY, rowNum int)

DECLARE @LocalCounter INT = 0

--put rows into temp table
WHILE (@LocalCounter < @NumRows)
BEGIN
    INSERT INTO #temp(rowNum) VALUES (@LocalCounter)
    SET @LocalCounter += 1
END

--Do inserts using GUIDs
DECLARE @GUIDTimeStart DateTime = GETDATE()
INSERT INTO TestGuid2 (SomeDate, batchNumber) 
SELECT GETDATE(), rowNum FROM #temp
DECLARE @GUIDTimeEnd  DateTime = GETDATE()

--Do inserts using IDENTITY
DECLARE @IdTimeStart DateTime = GETDATE()
INSERT INTO TestInt (SomeDate, batchNumber) 
SELECT GETDATE(), rowNum FROM #temp
DECLARE @IdTimeEnd DateTime = GETDATE()

SELECT DATEDIFF(ms, @IdTimeStart, @IdTimeEnd) AS IdTime
SELECT DATEDIFF(ms, @GUIDTimeStart, @GUIDTimeEnd) AS GuidTime

DROP TABLE TestGuid2
DROP TABLE TestInt
DROP TABLE #temp
ответил Phil Sandler 14 Mayam11 2011, 05:15:41
2

Я несколько раз запускал ваш образец сценария, создавая несколько настроек для подсчета и размера партии (и большое спасибо за ее предоставление).

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

Мои результаты были в целом похожи на ваши. Тем не менее, я бы сказал, что разница в INSERT между GUID и IDENTITY (int) немного больше с GUID, чем с GUID - может быть +/- 10% между прогонами. Пакеты, которые использовали IDENTITY, менялись менее чем на 2 - 3% каждый раз.

Также следует отметить, что мой тестовый ящик явно менее мощный, чем ваш, поэтому мне пришлось использовать меньшее количество строк.

ответил Yuck 14 Mayam11 2011, 03:57:59
0

Я собираюсь вернуться к другому методу convoverflow для этой же темы - https://stackoverflow.com/questions/170346/what-are-the-performance-improvement-of-sequential-guid-over-standard-guid р >

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

Мой личный опыт заключается в том, что, когда вы внедряете большой байт с высоким трафиком, лучше использовать GUID, потому что он делает его гораздо более масштабируемым для интеграции с другими системами. Это касается репликации, в частности, и ограничений int /bigint .... не то, что у вас закончились bigints, но в конце концов вы это сделаете и вернитесь назад.

ответил M.R. 14 Mayam11 2011, 05:12:39

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132