4271.aspx

A simple queue using tables in Microsoft SQL Server

I needed a fast and reliable queue in SQL when running some distributed tests but I did not find anything that suited my needs so I put together the simple system below. My main requirements were:

• support hundreds of parallel readers and writers


• each request in the queue must only be returned to one reader


• make it easy to monitor the performance of my readers and writers.

It consists of two stored procedures:

• usp_Request_push adds one requst to the queue (RequestsQueue table)


• usp_Request_pop returns one request from the queue and moves it to the RequestsCompleted table

The RequestsCompleted table is optional but I added it for several reasons:

• Track all requests to verify that my client does not "loose" messages. Any record in RequestsCompleted with a "RemovedDate" but a NULL "CompletedDate" got lost somewhere.


• To calculate statistics like average time before the message was removed from the queue and average time before the request was completed by the client.

Tables

CREATE TABLE [dbo].[RequestsQueue](

      [id] [uniqueidentifier] NOT NULL

CONSTRAINT [DF_RequestsQueue_id] 

DEFAULT (newid()),

      [request] [varchar](512) NOT NULL,

      [InsertedDate] [datetime] NOT NULL

CONSTRAINT [DF_RequestsQueue_InsertedDate] 

DEFAULT (getdate()),

 CONSTRAINT [PK_RequestsQueue] PRIMARY KEY NONCLUSTERED

(

      [id] ASC

)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

 

CREATE TABLE [dbo].[RequestsCompleted](

      [id] [uniqueidentifier] NOT NULL

CONSTRAINT [DF_RequestsCompleted_id] 

DEFAULT (newid()),

      [request] [varchar](512) NOT NULL,

      [InsertedDate] [datetime] NOT NULL

CONSTRAINT [DF_RequestsCompleted_InsertedDate] 

DEFAULT (getdate()),

      [RemovedDate] [datetime] NOT NULL

CONSTRAINT [DF_RequestsCompleted_RemovedDate] 

DEFAULT (getdate()),

      [CompletedDate] [datetime] NULL

) ON [PRIMARY

 

Stored procedures

CREATE PROCEDURE [dbo].[usp_Request_Pop] 

AS

BEGIN

declare

@request varchar(512),

@id uniqueidentifier,

@InsertedDate datetime

 

    /*

    The queue works like this:

    - Get the first record in the queue     

    - "Move" the recor to the RequestCompleted table

    - return the request to the caller

 

    We use a locking cursor so the same record cannot be

    returned to two different clients    

    */

     

      SET NOCOUNT ON;

 

      set @id=null

begin transaction

      DECLARE Stack_Cursor CURSOR FOR

            SELECT TOP 1 id, request, InsertedDate

            FROM RequestsQueue with (ROWLOCK, FASTFIRSTROW, XLOCK)           

 

      OPEN Stack_Cursor;

 

      FETCH NEXT FROM Stack_Cursor INTO @id, @request, @InsertedDate

      IF @@FETCH_STATUS = 0

      BEGIN

            delete RequestsQueue where current of Stack_Cursor         

      END;

 

      CLOSE Stack_Cursor;

      DEALLOCATE Stack_Cursor;

commit transaction     

 

if not @id is null

begin

    -- OPTIONAL store to the Completed table.

      insert into RequestsCompleted (id, request, insertedDate)

            values (@id, @request, @InsertedDate)

   

      -- return data to caller

      select @id as id, @request as request, @InsertedDate as InsertedDate

end

 

END

 

CREATE PROCEDURE [dbo].[usp_Request_Push]

@request varchar(512)

AS

BEGIN

   SET NOCOUNT ON;

   insert into RequestsQueue(request) values (@request)

END

 

Note: The queue is not guaranteed to be FIFO as the select statement does not sort the records in any way. It is normally FIFO as SQL Server appends records in tables that do not have a clustered index.