How to Implement a Queue System in SQL

Intermediate

2019-03-15

What is a Queue system ?

A Queue system is a system that stores the client requests until they are processed in the future. This system is useful when you have a large number of requests that each one would take some time to process. if you don't use a queuing system then the client will have to wait until the system resources are free and all other processes are processed and the turn comes for his request to be fetched and processed.

This operation might take a huge amount of time especially of the requests are time-consuming.

Customers Waiting

When to use a queue system ?

A Typical example of a queuing system usage is when you have to integrate with different services, each one requires a separate call and does a heavy processing ( eg. file processing )

Each call will result in a processing for the request.

Without a queuing system, the system will hang while it waits for all the requests to finish.

A queuing system will solve this problem and defers the services call to a background jobs that pulls from the queue and processes while appropriate. Each new request will be enqueued to this queue table.

Dustomers Waiting Non Blocking

How to implement a SQL-Based Queue system ?

For the previously mentioned problem we will design a SQL-based solution that follows this pattern:

  • A new request comes in it's enqueued in the queuing table.
  • The SQL table will maintain a set of status flags to indicate the processing progress.
  • A Dequeue function will pull the top process from the queue and start calling the API the status will then be updated
  • When the API call finishes the result is stored and processing status flag is set to ready allowing for another dequeuing function to process the result.
  • The result processing dequeue function will pull the records that are ready to be processed and start processing them.
  • When the processing is complete a final success status flag is set.

We will apply this solution on SQL-Server.

SQL Queue system schema

The table that maintains the queuing process has the following schema.


  • APIType The application will have to call several APIs, each one has a unique type represented by an integer value
  • APIUrl The end point to which the API call will occur.
  • APIInput Which input we called the API with.
  • APICallResult The result for API call.
  • Status This is the most important column, this is the column on which the dequeue function will decide to process the record or not.
  • APICallStartDate the date on which this record was dequeued
  • APICallEndDate processing result date

Dequeuing Function


The dequeue function updates the top (n) records available for processing and returns the result, to the application layer.

The function selects based on a column status, then updates this status so that it moves to next state.

Dequeue batch also has a custom where condition which can be used to query based on custom filtration , e.g selecting a specific API Type.

Using the Dequeue Batch Function from C# application

In order to execute this function we will execute the stored procedure from entity framework SqlQuery method


Alternatives to SQL-based solution

There are several solutions for building a queuing system for example : MSMQ is the Microsoft queuing system which is fully integrated with .Net system, ZeroMQ and cloud queuing systems like Amazon SQS , these all have some pros and cons as the case with any software solution. we will discuss them later.

What's next

In this tutorial we implemented a simple queuing system based on SQL tables and implemented a stored procedure to dequeue the records form the table in a safe way to process it in the background.

The next step is to implement a background system that has functions to run periodically where this dequeue batch function will work based on a time interval.