Я переношу очередь на диске в память SQL Server 2016, чтобы реализовать очередь.
Это мой формат очереди:
CREATE TABLE dbo.SimpleQueue
(
MsgId BIGINT NOT NULL PRIMARY KEY NONCLUSTERED IDENTITY(1, 1),
Payload VARCHAR(7500) NOT NULL,
IsDeleted BIT NOT NULL
) WITH (MEMORY_OPTIMIZED=ON)
GO
Это моя Enqueue
родная хранимая процедура SQL Server:
CREATE PROCEDURE dbo.Enqueue(@Payload VARCHAR(7500), @IsDeleted BIT)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'english')
INSERT INTO dbo.SimpleQueue (Payload, IsDeleted) VALUES (@Payload, @IsDeleted);
END
GO
Я пытаюсь записать собственную хранимую процедуру Dequeue
SQL Server, но у меня возникают некоторые трудности с тем, как реализовать UPDATE
с использованием результатов SELECT или таблицы переменных.
До сих пор я пробовал:
CREATE PROCEDURE dbo.Dequeue(@BatchSize INT = 1)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
( TRANSACTION ISOLATION LEVEL = SNAPSHOT,LANGUAGE = 'english' )
UPDATE dbo.SimpleQueue
SET IsDeleted=1
WHERE MsgId = (
SELECT TOP(@BatchSize) MsgId, Payload
FROM dbo.SimpleQueue
WHERE IsDeleted = 0)
END
GO
Но я получаю эту ошибку:
Подзапросы (запросы, вложенные в другой запрос) поддерживаются только в инструкциях SELECT с модулями, скомпилированными в собственном коде.
Поэтому я попробовал другой подход, используя переменную для хранения результата.
Сначала я создал тип таблицы:
CREATE TYPE dbo.SimpleDequeue
AS TABLE
(
MsgId BIGINT NOT NULL PRIMARY KEY NONCLUSTERED,
Payload INT NOT NULL
)
WITH (MEMORY_OPTIMIZED=ON)
GO
Пока все хорошо, затем я попытался использовать его:
CREATE PROCEDURE dbo.Dequeue(@BatchSize INT = 1)
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
( TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = 'english')
DECLARE @result dbo.SimpleDequeue;
INSERT @result
SELECT TOP(@BatchSize) MsgId, Payload FROM dbo.SimpleQueue
WHERE IsDeleted = 0
UPDATE dbo.SimpleQueue
SET IsDeleted = 1
WHERE
@result.MsgId = dbo.SimpleQueue.MsgId
SELECT MsgId, Payload FROM @result
END
GO
Я получаю эту ошибку:
Необходимо объявить скалярную переменную "@result".
(только при использовании @result
на WHERE @result.MsgId = dbo.SimpleQueue.MsgId
)
Вот старый процесс удаления из очереди с использованием дисковых таблиц SQL Server:
CREATE PROCEDURE dbo.DequeueInDisk
@BatchSize INT = 1
AS
BEGIN
SET NOCOUNT ON;
WITH
cte AS (
SELECT TOP(@BatchSize) Payload
FROM dbo.SimpleQueue WITH (ROWLOCK, READPAST)
ORDER BY MsgId
)
DELETE FROM cte OUTPUT deleted.Payload;
END
Как я могу сделать это UPDATE и OUTPUT обновленными значениями (с высокой производительностью, так как это критично)?