UPDATE from a table in SQL Native stored procedure (Hekaton)

Question

I'm migrating a queue in disk to in memory SQL Server 2016 to implement a queue.

This is my queue format:

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

This is my Enqueue native SQL Server stored procedure:

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

I'm trying to write down the Dequeue native SQL Server stored procedure, but I'm having some difficulties on how to implement an UPDATE using results of a SELECT or a variable table.

So far I tried:

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

But I get this error:

Subqueries (queries nested inside another query) is only supported in SELECT statements with natively compiled modules.

So I tried a different approach by using a variable to store the result.

First I created a Table type:

CREATE TYPE dbo.SimpleDequeue
  AS TABLE 
   (
    MsgId BIGINT NOT NULL PRIMARY KEY NONCLUSTERED, 
    Payload INT NOT NULL
   )
   WITH (MEMORY_OPTIMIZED=ON)
GO

So far so good, then I tried to use it:

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

I get this error:

Must declare the scalar variable "@result".

(only when is using @result on WHERE @result.MsgId = dbo.SimpleQueue.MsgId)

Here is the old dequeue process using in disk SQL Server tables:

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

How can I make that UPDATE and OUTPUT the updated values (with high performance, since this is critical)?

Answer 1

I think your approach makes perfect sense from SQL development point of view - you have to think in sets rather than in row-by-row approach. But it looks like Microsoft thinks that you require different approach for native compiled procedures, more imperative and really row-by-row (see Implementing UPDATE with FROM or Subqueries or Implementing MERGE Functionality in a Natively Compiled Stored Procedure. So your procedure can look like this:

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

I've not tested how fast it will work, but I'll definitely will test it with some real numbers, cause we have a couple of these table-queues implemented and I wonder if we can get some performance boost with Hekaton.

Answer 2

In your old routine you use the TOP(@BatchSize) with an ORDER BY MsgId. The new approach seems not to have this ORDER BY... You'll get random result...

Your

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

will come back with two columns and - probably - several rows. You cannot compare this with an "=".

What you can try:

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

Or you could try to use an INNER JOIN, something like this:

 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

What else: You could try an INNER JOIN (SELECT TOP ... ) AS InnerSimpleQueue ON .. or maybe a CROSS APPLY.

EDIT: One more approach with a 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