UPDATE d'une table dans une procédure stockée SQL native (Hekaton)

Je migre une queue dans le disque vers la memory SQL Server 2016 pour implémenter une queue.

C'est le format de ma queue:

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

Voici ma procédure stockée SQL Server natif Enqueue :

 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

J'essaie d'écrire la procédure stockée SQL Server native de Dequeue , mais j'ai quelques difficultés sur la façon d'implémenter un UPDATE utilisant les résultats d'un SELECT ou d'une table de variables.

Jusqu'ici j'ai essayé:

 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

Mais j'ai cette erreur:

Les sous-requêtes (requêtes nestedes dans une autre requête) sont uniquement sockets en charge dans les instructions SELECT avec des modules compilés de manière native.

J'ai donc essayé une approche différente en utilisant une variable pour stocker le résultat.

J'ai d'abord créé un type de table:

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

Jusqu'ici tout va bien, alors j'ai essayé de l'utiliser:

 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

J'ai cette erreur:

Doit déclarer la variable scalaire "@result".

(seulement quand @result utilise WHERE @result.MsgId = dbo.SimpleQueue.MsgId )

Voici l'ancien process dequeue utilisant des tables SQL Server sur disque:

 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

Comment puis-je faire cette mise à jour et sortie les valeurs mises à jour (avec des performances élevées, car cela est essentiel)?

Dans votre ancienne routine, vous utilisez le TOP(@BatchSize) avec un ORDER BY MsgId . La nouvelle approche semble ne pas avoir cette command par … Vous obtiendrez un résultat random …

Votre

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

reviendra avec deux colonnes et – probablement – plusieurs rangées. Vous ne pouvez pas comparer cela avec un "=".

Ce que vous pouvez essayer:

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

Ou vous pourriez essayer d'utiliser un INNER JOIN, quelque chose comme ceci:

  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

Quoi d'autre: Vous pouvez essayer un INNER JOIN (SELECT TOP ... ) AS InnerSimpleQueue ON .. ou peut-être un CROSS APPLY.

EDIT: Une approche de plus avec un 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