SQL-Ex blog

Давайте рассмотрим объект SEQUENCE, введенный в SQL Server 2012.

Как я могу получить ваш номер?

Иногда вам просто нужен уникальный номер, генерируемый более-менее изолированно. Возможно, это требуется для системы поддержки, чтобы вы могли присвоить клиенту идентификатор договора, прежде чем оформить его. Или вам требуется записать некоторые данные по нескольким таблицам, связанных по этому ID. До версии SQL Server 2012, вы могли сделать это одним из двух следующих способов (совершенно диких).

Первый способ

CREATE TABLE SequenceHolder (

		id INT identity(1, 1)

		,BecauseINeedSomething BIT DEFAULT 0

		);

CREATE OR ALTER PROCEDURE GetNextNumberTable

AS

BEGIN

	DECLARE @newid INT;

	INSERT INTO SequenceHolder (BecauseINeedSomething)

	VALUES (0);

	SET @newid = SCOPE_IDENTITY();

	DELETE

	FROM SequenceHolder

	WHERE id = @newid;

	SELECT @newid;

END

Вы работаете с таблицей, которая больше никогда не используется. Поэтому вы можете просто удалить запись после получения номера. Но это увеличивает расходы на ввод/вывод, не так ли? Что если кто-то создаст первичный ключ и, возможно, даже другой индекс также на этой таблице? Это еще больше увеличит число логических чтений, которые вы будете выполнять. Перенести эти удаления на ночное окно обслуживания? Можно, но вы не можете применить для этого TRUNCATE, поскольку будет сброшен счетчик (если вы не сохранили свой последний использованный ID, после чего выполнили DBCC RESEED).

Второй способ

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

Тестировали вы это решение при большой нагрузке с множеством конкурирующих запросов? Стали ли эти таблицы горячими точками и узким местом в производительности? Насколько безопасно их использовать, имея в виду откат транзакции?

Переходим к последовательности

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

И его намного проще использовать! Вы просто запрашиваете следующий номер в последовательности!

CREATE sequence ABetterWay start

	WITH 1 increment BY 1;

SELECT NEXT value

FOR ABetterWay;

Сравнение

Установка

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

USE master;

DROP DATABASE IF EXISTS SequenceTesting;

GO

CREATE DATABASE SequenceTesting;

GO

USE SequenceTesting;

GO

DROP TABLE IF EXISTS sequenceholder;

CREATE TABLE SequenceHolder (

	id INT identity(1, 1)

	,BecauseINeedSomething BIT DEFAULT 0

	);

ALTER TABLE SequenceHolder ADD CONSTRAINT PK_SequenceHolder PRIMARY KEY NONCLUSTERED (id);

CREATE CLUSTERED INDEX IX_SeqHolder ON SequenceHolder (id);

GO

CREATE OR ALTER PROCEDURE GetNextNumberTable  @newid int OUTPUT

AS

BEGIN

	INSERT INTO SequenceHolder (BecauseINeedSomething)

	VALUES (0);

	SET @newid = SCOPE_IDENTITY();

	DELETE

	FROM SequenceHolder

	WHERE id = @newid;

END

GO

CREATE sequence ABetterWay start WITH 1 increment BY 1;

GO

CREATE OR ALTER PROCEDURE GetNextNumberSeq @newid int OUTPUT

AS

BEGIN

	set @newid = NEXT value	FOR ABetterWay;

END

GO

Тестирование

Я набросал скрипт PowerShell 7 для выполнения обеих процедур. Используя новый Foreach-Object -Parallel {}, я могу легко смоделировать многочисленные процессы, одновременно запрашивающие номера последовательности.

Import-Module dbatools;

$SqlInstance = 'localhost\sql17';

$MyDB = 'SequenceTesting';

$TableTime = Measure-Command {

    (1..100) | foreach-object -parallel {

        Invoke-DbaQuery -SqlInstance $sqlinstance -query "declare @newid int; exec GetNextNumberTable @newid" -Database $MyDB -As SingleValue | Out-Null;

    }

};

"Время для получения номеров из таблицы: $($TableTime.TotalSeconds)";

$SequenceTime = Measure-Command {

    (1..100) | foreach-object -parallel {

        Invoke-DbaQuery -SqlInstance $sqlinstance -query "declare @newid int; exec GetNextNumberSeq @newid" -Database $MyDB -As SingleValue | Out-Null;

    }

};

"Время для получения номеров из последовательности: $($SequenceTime.TotalSeconds)";

Результаты

На 100 итерациях получается примерно 16-процентное улучшение при использовании объекта SEQUENCE по сравнению с табличным методом.

Время для получения номеров из таблицы: 86.3985773

Время для получения номеров из последовательности: 72.7598077

Реализация

Вы можете подумать: "Но Энди, у меня так много мест в приложении, где вызывается хранимая процедура, которая запрашивает следующий номер в строке. Я не могу заменить это все запросом к SEQUENCE." И я, вероятно, соглашусь с вами. Поэтому давайте не будем этого делать.

Вместо этого давайте заменим сегодня тело хранимой процедуры, которую вызывает ваше приложение. Таким образом, новый/обновленный код может непосредственно использовать SEQUENCE (если хотите), но и унаследованный также может извлечь из этого преимущества.

Предостережения

Следует знать о двух вещах, одной - для всех реализаций, а другой - для замены унаследованного кода.

Помните, что выше мы говорили о том, что на SEQUENCE не влияют откаты транзакций? Если вы получаете номер из этого объекта, он не будет возвращен, если вы откатите транзакцию. Это подразумевает, что если вы должны иметь непрерывную цепочку без зазоров, SEQUENCE может не подойти вам.

Если вы заменяете унаследованный код (например, вышеупомянутый табличный метод), не забудьте указать значение STARTS AT, чтобы не получить дубликаты значений, уже полученных из таблицы. Я предлагаю немного округлить текущее значение identity, чтобы получить некоторый буфер. Но если вам требуется получить непрерывную последовательность, это потребует дополнительной работы при реализации. Вам потребуется проверить идентификаторы, прежде чем создать SEQUENCE.