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)?
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With