ОБНОВЛЕНИЕ из таблицы в SQL Собственная хранимая процедура (Hekaton)

Question

Я перемещаю очередь на диске в память 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

Как я могу сделать это ОБНОВЛЕНИЕ и ВЫХОД обновленных значений (с высокой производительностью, так как это важно)?

Answer 1

Я думаю, что ваш подход имеет смысл с точки зрения разработки SQL - вы должны думать в наборах, а не в построчном подходе. Но похоже, что Microsoft считает, что вам нужен другой подход для нативных скомпилированных процедур, более обязательный и действительно построчный (см. " Реализация UPDATE с FROM или подзапросами" или " Реализация функциональности MERGE в нативно скомпилированной хранимой процедуре"). Таким образом, ваша процедура может выглядеть следующим образом этот:

create or alter 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;

    declare
        @MsgId int,
        @Payload varchar(7500),
        @i int = 0;

    while @i < @BatchSize
    begin
        select top (1)
            @MsgId = s.MsgId,
            @Payload = s.Payload
        from dbo.SimpleQueue as s
        where
            s.IsDeleted = 0
        order by
            s.MsgId;

        if @@rowcount = 0
        begin
            set @i = @BatchSize;
        end
        else
        begin
            update dbo.SimpleQueue set IsDeleted = 1 where MsgId = @MsgId;

            insert into @result (MsgId, Payload)
            select @MsgId, @Payload;

            set @i += 1;
        end;
    end;

    select MsgId, Payload from @result;
END

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

Answer 2

В своей старой рутине вы используете TOP(@BatchSize) с ORDER BY MsgId, Новый подход, кажется, не имеет этого ORDER BY... Вы получите случайный результат...

Ваш

WHERE MsgId = (
            SELECT TOP(@BatchSize) MsgId, Payload
                FROM dbo.SimpleQueue
                WHERE IsDeleted = 0
                /*added this!*/ ORDER BY MsgId  )

вернусь с двумя столбцами и - возможно - несколькими рядами. Вы не можете сравнить это с "=".

То, что вы можете попробовать:

WHERE MsgId IN (
            SELECT TOP(@BatchSize) MsgId
                FROM dbo.SimpleQueue
                WHERE IsDeleted = 0
                ORDER BY MsgId)

Или вы можете попробовать использовать INNER JOIN, что-то вроде этого:

 UPDATE dbo.SimpleQueue
        SET IsDeleted=1
   FROM dbo.SimpleQeueu
   INNER JOIN dbo.SimpleQueue AS sq ON dbo.SimpleQeueu.MsgId=sq.MsgId
                                       AND sq.IsDeleted=0
                                       --this is missing the TOP-clause

Что еще: вы можете попробовать INNER JOIN (SELECT TOP ... ) AS InnerSimpleQueue ON .. или, может быть, ПРИМЕНЕНИЕ КРЕСТА.

РЕДАКТИРОВАТЬ: еще один подход с CTE:

WITH myCTE AS
(
    SELECT TOP(@BatchSize) MsgId
    FROM dbo.SimpleQueue
    WHERE IsDeleted = 0
    ORDER BY MsgId
)   
UPDATE dbo.SimpleQueue
        SET IsDeleted=1
FROM dbo.SimpleQeueu
INNER JOIN myCTE ON myCTE.MsgId=dbo.SimpleQueue.MsgId