Вставки с последовательным ключом GUID в кластеризованный индекс не значительно быстрее

ProgramBox

Вставки с последовательным ключом GUID в кластеризованный индекс не значительно быстрее

Post author:admin
Запись опубликована:3 мая, 2023
Post category:Вопросы по программированию

#sql #sql-server #indexing #io #clustered-index

#sql #sql-сервер #индексирование #io #кластеризованный индекс

Вопрос:

В SQL Server 2008 я попытался воспроизвести результаты экспериментов с кластеризованным индексом по последовательным и непоследовательным ключам GUID, рассмотренным здесьhttp://sqlblog.com/blogs/denis_gobo/archive/2009/02/05/11743.aspx но я не испытываю значительного ускорения при вставках, которое я ожидал (и испытывает автор). Использование страницы явно улучшилось с последовательным идентификатором GUID, но по некоторым причинам вставка 10000 строк выполняется всего на 100 мс быстрее (из 10 300 мс).

Я использую следующий код:

 CREATE TABLE TestGuid1 (Id UNIQUEIDENTIFIER not null DEFAULT newid(),
SomeDate DATETIME, batchNumber BIGINT)

CREATE TABLE TestGuid2 (Id UNIQUEIDENTIFIER not null DEFAULT newsequentialid(),
SomeDate DATETIME, batchNumber BIGINT)

CREATE CLUSTERED INDEX ix_id1 ON TestGuid1(id)
CREATE CLUSTERED INDEX ix_id2 ON TestGuid2(id)

go

SET NOCOUNT ON
INSERT TestGuid1 (SomeDate,batchNumber) VALUES (GETDATE(),3)
go 10000

SET NOCOUNT ON
INSERT TestGuid2 (SomeDate,batchNumber) VALUES (GETDATE(),3)
go 10000

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

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate))
FROM TestGuid1
GROUP BY batchNumber

SELECT batchNumber,DATEDIFF(ms,MIN(SomeDate),MAX(SomeDate))
FROM TestGuid2
GROUP BY batchNumber

Кто-нибудь может объяснить, почему я не испытываю более значительного ускорения при вставках в TestGuid2?

Продолжение: Как и было запрошено в приведенной ниже теме, я расширил тест: результаты теста имеют тенденцию значительно меняться с течением времени, поэтому теперь эксперименты повторяются N раз, и сообщается общее и среднее время использования. Я также добавил третий тест, а именно для первичных ключей в последовательных столбцах целых чисел. Это должен быть самый быстрый и компактный из всех трех методов, поскольку целочисленный тип меньше, а идентификатор (1,1) выполняется (или, по крайней мере, должен быть) быстрым. По крайней мере, по моей интуиции. Среднее время выполнения теперь в пользу последовательного GUID, но, к удивлению, вставки в третьем эксперименте (с последовательными целочисленными ключами) медленнее, чем последовательные GUID. У меня нет объяснения этому. Вот код для новых экспериментов:

 SET NOCOUNT ON

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

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 TestGuid1 (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
    SET @LocalCounter  =1
    END

SET @LocalCounter = 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 ('TestGuid1') WITH tableresults
DBCC showcontig ('TestGuid2')  WITH tableresults
DBCC showcontig ('TestInt')  WITH tableresults

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

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 TestGuid1
DROP TABLE TestGuid2
DROP TABLE TestInt

И среднее время выполнения:

 NEWID()            3064
NEWSEQUENTIALID()  1977
IDENTITY()         2223

Использование страницы выглядит следующим образом:

 Table          Pages  AveragePageDensity
----------------------------------------
TestGuid1      50871  68,4
TestGuid2      35089  99,2
TestInt        32259  98,7

Я не понимаю, почему эта статистика страницы (которая лучше всего подходит для testInt) не подразумевает, что третий эксперимент самый быстрый.

1. Вы пробовали запускать вставки внутри явной транзакции? На данный момент эффект всех отдельных коммитов, скорее всего, будет доминировать в результатах (кстати, важно поставить BEGIN TRAN перед GO , чтобы в конечном итоге не выполнить это 10000 раз, что потребовало бы равного количества, commits чтобы свести @@TRANCOUNT к нулю.)

2. Скорее всего, размер вашей выборки слишком мал, всего 10000 строк, переупорядочение таблицы занимает у sql server такое короткое время, что вы мало что увидите при какой-либо одной вставке. Попробуйте увеличить его примерно до 100 000 или 1 000 000. При работе с таблицами такого размера выбор кластеризованного индекса является гораздо более осмысленным выбором.

3. Кимберли Трипп красиво (и подробно!) показывает в своем блоге, что Дисковое пространство дешево — НЕ в этом суть!! насколько плохой GUID — случайный или последовательный — сравнивается с использованием INT IDENTITY в качестве индекса кластеризации в вашей таблице

4. Инкапсулирование всего в транзакцию (использование цикла WHILE вместо «перейти к 10000»), вставка 100 000 строк вместо 10 000 и добавление заполняющего столбца типа char (100) не изменяет картину использования времени в пользу последовательных идентификаторов GUID. На самом деле наоборот: с вышеуказанными изменениями вставки выполняются за 4800 против 2276 мс (последовательные идентификаторы GUID на удивление самые медленные …)

5. Сколько тестов вы выполнили? Результаты в моем ответе показывают, что каждый отдельный запуск может иметь довольно широкие вариации, но когда я беру среднее значение, NEWSEQUENTIALID выходит вперед.

Ответ №1:

Можете ли вы попробовать этот модифицированный скрипт и опубликовать свои результаты?

     SET NOCOUNT ON

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

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

    DECLARE @BatchCounter INT = 1

    WHILE (@BatchCounter <= 20)
    BEGIN 
    BEGIN TRAN

    DECLARE @LocalCounter INT = 0

        WHILE (@LocalCounter <= 100000)
        BEGIN
        INSERT TestGuid1 (SomeDate,batchNumber) VALUES (GETDATE(),@BatchCounter)
        SET @LocalCounter  =1
        END

    SET @LocalCounter = 0

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

    SET @BatchCounter  =1
    COMMIT 
    END

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

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

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

DROP TABLE TestGuid1
DROP TABLE TestGuid2

Я вижу довольно сильно отличающиеся результаты между отдельными запусками (на моем ноутбуке, а не на сервере!), Но определенная тенденция к ускорению последовательного выполнения.

NEWID() Среднее значение 5168,9

 batchNumber          NEWID()
-------------------- -----------
1                    4270
2                    2480
3                    2706
4                    3333
5                    7480
6                    5346
7                    4306
8                    7713
9                    7313
10                   4760
11                   4680
12                   4113
13                   3433
14                   2686
15                   4963
16                   8040
17                   5313
18                   8160
19                   9533
20                   2750

NEWSEQUENTIALID() Среднее значение 3000.85

 batchNumber          NEWSEQUENTIALID()
-------------------- -----------------
1                    2016
2                    1820
3                    1886
4                    1870
5                    4873
6                    3473
7                    3730
8                    3690
9                    1983
10                   2020
11                   1906
12                   5596
13                   2100
14                   1950
15                   2096
16                   1876
17                   5196
18                   2110
19                   2113
20                   7713

1. 1 — Я работал над чем-то подобным, но вы получили это первым и лучше 🙂

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

3. @SQLMenace — True это может объяснить некоторые значительные различия во времени, но также это может быть объяснено другими приложениями, которые у меня запущены.

4. Конечно, когда я писал это сообщение в блоге, оно выполнялось с использованием sql 2005. В любом случае, все же лучше использовать NEWSEQUENTIALID(), потому что чтение будет быстрее, а таблицы будут занимать меньше места

Ответ №2:

Поскольку я написал тот оригинальный пост в блоге, я решил запустить ваш код, вот что я получаю

 3   8726  -- newid()
3   12550 -- newsequantialID

Помните, что я запускаю это на сервере с 32 ГБ оперативной памяти и 8 процессорами, а не на ноутбуке

на моем локальном компьютере я почти не вижу разницы между ними

Помните, что помимо вставок, чтение будет намного медленнее, потому что таблица фрагментирована

Вот что я получаю при запуске скрипта Мартина на сервере

 batchNumber NEWID()
17  1696
19  1706
14  1680
16  1706
5   1660
6   1890
7   1650
8   1663
13  1673
15  1683
2   1656
9   1673
20  1750
1   2033
3   1673
10  1673
12  1670
4   1650
11  1690
18  1696

batchNumber NEWSEQUENTIALID()
2   1276
9   1260
20  1290
13  1266
15  1280
17  1266
19  1266
5   1260
6   1266
7   1260
8   1260
1   1243
3   1256
10  1270
12  1263
14  1266
16  1276
4   1256
11  1270
18  1270

Вот что происходит на моем рабочем столе, файлы не имеют размера, кстати

 batchNumber NEWID()
1   9470
2   4446
3   5996
4   3860
5   4170
6   2403
7   3283
8   3573
9   1883
10  3980
11  2580
12  2780
13  1643
14  2836
15  3250
16  4303
17  3250
18  3376
19  8723
20  2616

batchNumber NEWSEQUENTIALID()
1   2566
2   1336
3   1256
4   3123
5   3023
6   1166
7   2396
8   1180
9   2386
10  3896
11  3790
12  3066
13  1396
14  2010
15  1183
16  3110
17  4060
18  4260
19  1896
20  2013

1. Несколько удивительно! Вы получите тот же результат, если попробуете код из моего ответа? newid() Стабильно ли быстрее для вас на вашем сервере?

2. Теперь я повторил эксперименты, и результаты теперь (почти) соответствуют теории. Смотрите мое расширение исходной формулировки вопроса. Однако я также расширил тест, чтобы охватить еще более компактный и, следовательно, (теоретически) более быстрый метод: последовательные целочисленные ключи. Но, к моему удивлению, это медленнее, чем последовательные идентификаторы GUID. У меня нет объяснения этому.