July 14, 2017 at 4:48 pm
I have searched for the last 4 hours looking for how to implement a simple queue system in SQL Server. Here is the scenario:
* each order is made of n products
* as part of order making process, each product is run through a "sub" routine to calculate data about it.
I would like to be able to put the product id's in to a queue table an automatic process work through the queue table to run the process outside of the order taking process.
I have looked at queues, service broker and agent. Agent would work but then the service is checking every 1 second even when there is no activity. I can't find a complete guide on how to implement a queue. Once the product id is in teh queue, I don't care to find out any data/result back from the process.
Any ideas would be much appreciated.
Thank you,
Mike
July 14, 2017 at 4:56 pm
mike 57299 - Friday, July 14, 2017 4:48 PMI have searched for the last 4 hours looking for how to implement a simple queue system in SQL Server. Here is the scenario:
* each order is made of n products
* as part of order making process, each product is run through a "sub" routine to calculate data about it.I would like to be able to put the product id's in to a queue table an automatic process work through the queue table to run the process outside of the order taking process.
I have looked at queues, service broker and agent. Agent would work but then the service is checking every 1 second even when there is no activity. I can't find a complete guide on how to implement a queue. Once the product id is in teh queue, I don't care to find out any data/result back from the process.
Any ideas would be much appreciated.
Thank you,
Mike
SSB could be workable here, but is a bit cumbersome and likely overkill.
Why do you care if SQL Agent is checking a queue every whatever time? As long as it isn't doing a table scan of a lot of rows it shouldn't matter to anything, right?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 15, 2017 at 4:39 pm
if I have 30 items and agent runs every second - it will still take 30 seconds. If I have 100 items - it will take 1.5 minutes. If each process on average only takes 1/2 second - a queue would do it in 15 seconds.
That is why i need a self running queue that can process faster than every second.
Thanks,
Mike
July 16, 2017 at 9:07 am
mike 57299 - Saturday, July 15, 2017 4:39 PMif I have 30 items and agent runs every second - it will still take 30 seconds. If I have 100 items - it will take 1.5 minutes. If each process on average only takes 1/2 second - a queue would do it in 15 seconds.That is why i need a self running queue that can process faster than every second.
Thanks,
Mike
You are not thinking clearly about how your job/process should work. Doing ONE queue item per job run is just, as you pointed out, silly, inefficient and unworkable. You have a sproc that processes All current items in the queue when it is fired by the agent job. That is standard queue mechanics from my experience. And there are a variety of approaches to solve this need in SQL Server depending on what your exact requirements are.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 16, 2017 at 10:51 am
I am trying to figure out queues. Doing a batch of items doesn't solve the issue. If my queue was a list of incoming orders and I have limited stock, then the customer will expect that the first one to buy it gets it. If I do a batch approach, the 1st one in the second batch might get the product before the 8th one in the 1st batch. Thus, I have to do it in order and I need a way to do it faster than every minute. Agent only allows running every minute unless I am missing something.
Mike
July 16, 2017 at 1:42 pm
The Agent allows running every 10 seconds as a maximum frequency.
At any rate, what you're seeing is basically the big dilemma with implementing such things in SQL Server.
On the one hand, if you use the Agent, you're going to be checking the queue very frequently, whether there are items in the queue or not, which can have its own performance implications.
On the other hand, if you use Service Broker, while you have a handy solution to the polling problem, you've now introduced quite a bit of complexity.
Trying to work around all of this is why you'll find people who think of implementing queues in SQL Server as something of an anti-pattern.
It's not a task with a trivial solution, in short.
You either implement them with a scheduled process that dequeues items, at the risk of either polling too frequently or not polling frequently enough for peak traffic, or use Service Broker, which gives you a way around that problem, but introduces a lot of complexity.
http://rusanu.com/2010/03/26/using-tables-as-queues/ is a nice high-level description of the pros/cons/hows of it all.
As mentioned by Kevin, unless checking the queue is incredibly expensive in your case for some reason or another, start with testing the non-Service Broker solution. It's the better solution more often than not, in my experience.
The issue you mention with order shouldn't be a problem with a proper implementation; implementing FIFO style queues is fairly typical.
Cheers!
July 16, 2017 at 5:06 pm
mike 57299 - Sunday, July 16, 2017 10:51 AMI am trying to figure out queues. Doing a batch of items doesn't solve the issue. If my queue was a list of incoming orders and I have limited stock, then the customer will expect that the first one to buy it gets it. If I do a batch approach, the 1st one in the second batch might get the product before the 8th one in the 1st batch. Thus, I have to do it in order and I need a way to do it faster than every minute. Agent only allows running every minute unless I am missing something.Mike
Nope. You use a sequence number mechanism that guarantees FIFO processing. No sales to someone that make a purchase attempt after a person that happened to buy the last one in stock. So your sproc grabs all currently un-processed queue items, and processes them in order. Meanwhile additional items can drop in the queue and will be processed the next time the processing sproc (or another sproc processing call is made, perhaps by 5 or 50 different agent jobs or even external methods such as server-level services, etc).
PLEASE stop thinking of batch or one. Think of "available to be processed entries". Your queue will have those, and others that are in a Completed, In-Process, Error, or other possible states. When your processing sproc is run you grab all "available to be processed" items and mark them as "in-process". Now you process them (in order if necessary) and mark them in the appropriate state as you do (if necessary). You can do individual transactions for each, one big transaction for all items, etc. LOTS of flexibility here to suit your requirements.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply