February 2, 2016 at 9:09 pm
Comments posted to this topic are about the item Queues in Databases
February 3, 2016 at 1:02 am
When architecting an application, you should always start by specifying needs for persistence rather than any particular technology. Once fully specified, implementation becomes easy to detail. For many, a SQL Server solution will work just fine.
February 3, 2016 at 2:38 am
I think is SOMETHING of an anti pattern - sometimes though the reverse is true and breaking out another technology is premature optimisation, the SQL based solution is more than adequate for the job. Particularly say a queue where you don't need to do so much inserting, updating and deleting, where your queue is a pointer to another record and the pointers are just added and deleted as required, without a whole lot of throughput. It all depends really.
February 3, 2016 at 3:31 am
premature optimization - I like that. I code for clarity, find performance bottlenecks and optimize them until they scream. I leave everything else alone.
February 3, 2016 at 5:45 am
I have both rolled my own queue in SQL Server and used message queue systems like MSMQ and AMQP. The article sited seems to have some good points but I am not sure it rises to the point of "anti-pattern".
-be aware of the strengths and weaknesses of your proposed solution
-look at the functional and non-functional requirements with an eye to the fit of your solution
-be willing to research other solutions
-have the luxury of the time to decide
If you know how to use a hammer and you need to get the job done and you are sure the hammer will work fine based on experience, hammer away.
February 3, 2016 at 7:34 am
When all I had was msmq I used a simple DB design to give a persistent ordered queue.
It worked, was efficient and didn't increase the technology footprint.
These days tech such as Kafka does everything that I needed without the licencing requirements for SQL Server.
It really does depend on the whole solution not just an isolated part of it. Do you try to pick the best individual technologies or choose a smaller technology set that produces the results you need today?
February 3, 2016 at 8:49 am
Here's an example: I built a FoxPro database queue solution to update a telephone switch. There was a FoxPro communication library that was able to connect to it. It made it pretty seamless. Once the telephone switch update was done, we updated the database and if a customer service person was looking, they could see what time it was updated.
I also had a FoxPro database that had a schedule for jobs.
February 3, 2016 at 9:13 am
Coincidentally, just last week I was just searching the web for a good pattern for implementing a queue in SQL Server. How well this works depends on dequeue volume and frequency, the number of readers, how strictly you need to maintain FIFO order, and whether you choose to dequeue records using an update or delete.
This article describes how to minimize locking, skip past locked rows using the READPAST hint, and usage of the OUTPUT clause of the UPDATE or DELETE statement to efficiently select and write (dequeue) a single row.
Using tables as Queues: Remus Rusanu
http://rusanu.com/2010/03/26/using-tables-as-queues/
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
February 3, 2016 at 10:12 am
I frequently use SQL Server for queuing operations, and if I'm not mistaken, it is the backend persisted storage for MSMQ. I have found that by using triggers to kick off processes for new items rather than polling, and partitioning based on the item status rather than relying solely on indexes, operations and performance are on par, and sometimes better than most messaging systems for small to mid-size queues, likely because some processing can be done in local memory rather than over a network socket. Latency always seems to be the biggest performance killer for me. Once you get to something the size of Twitter, then there are likely better choices, but in most enterprise application use cases I have found SQL Server to be a great platform for message queuing.
Joshua Perry
http://www.greenarrow.net
February 3, 2016 at 11:02 am
That is a good point, it likely that there are database optimizations baked into Windows Server. At least that is what my testing shows, like performance spikes in the connection pool that I didn't do anything to get and can't figure out how to shut off. You gurus probably know.
February 3, 2016 at 8:03 pm
Joshua M Perry (2/3/2016)
I frequently use SQL Server for queuing operations, and if I'm not mistaken, it is the backend persisted storage for MSMQ.
I don't believe SQL is used by MSMQ. I think this is strictly a .NET queueing service with its own internal database.
February 4, 2016 at 1:25 am
While the article initially reads as plausible, a little critical though will raise questions.
Presenting a couple of downsides for a particular use case doesn't make and anti-pattern.
Some statements seem more indicative of a basic lack of appreciation around database capabilities like triggers, logging and audit than 'hammer and nail' scenarios.
"If you don’t clear the table down at intervals, it will keep growing until it starts to hit the performances"
"sharing a database between applications (or services) is a bad thing"
On reflection I'll not be recommending it.
oh and yes, I have used both DB and messaging.
February 4, 2016 at 4:47 am
Definitely an interesting editorial, and a question well worth asking. My answer is look at the requirements and see whether it will be better to use a database (not neccessarily a relational database) or to just use one of the available messaging systems. Of course not all queues are for messaging, but I get the impreeion both from the editorial and from comments here that messaging is really what the editorial was addressing.
call.copse (2/3/2016)
I think is SOMETHING of an anti pattern - sometimes though the reverse is true and breaking out another technology is premature optimisation, the SQL based solution is more than adequate for the job. Particularly say a queue where you don't need to do so much inserting, updating and deleting, where your queue is a pointer to another record and the pointers are just added and deleted as required, without a whole lot of throughput. It all depends really.
Oh, it's actually much worse than premature optimisation - it's choosing the soluion before studying the requirements.
I've had queues in a database, in cases where the requirements could probably not have been met by a messaging system. Requirements like "A, B, and C must either all get into the queue or none of them" which are trivial to meet using SQL server are nigh on impossible with most messaging systems (or atleast they were a few years back, I guess I'm out of date now). As soon as you add to that a requirement like "message B must not be transmitted to its recipients until message A has been acknowledged by at least wo of its recipients" you are pretty well stuck with building some nice persistent data-structure that will be manipulated in transactions, so is you don't se a DMS you will have to reinvent that particular wheel. Distinguishing messages that require acknowlegement (by a human responding) from those that merely require confirmation of delievery (by the destination mailbox, whatever that might be) and from those that should be deleted at the source as soon as they are transmitted (ie acknowledged by the first relay in the messaging chain) is useful. Specifying earliest permitted transmiaaion time for a message may be required for some messages, or for all messages, or not required at all. Target time for transmission and priority may be required. All of polling, direct or trigger-based activation, and activation based on queue length are all possible requirements.
So a simple fairly brain-dead messaging system may not meet the requirements at all, and trying not to use a DBMS may cost a vast amount of design and development effort. On the other hand, if the requirements are simple and don't require anything other than high and sufficiently reliable message throughput there's probably no point in involving a DBMS.
Tom
February 12, 2016 at 4:53 am
Steve Jones - SSC Editor (2/3/2016)
Joshua M Perry (2/3/2016)
I frequently use SQL Server for queuing operations, and if I'm not mistaken, it is the backend persisted storage for MSMQ.I don't believe SQL is used by MSMQ. I think this is strictly a .NET queueing service with its own internal database.
MSMQ predates .NET and it's original interface was COM with all the examples and samples written in C++. There may have been a C based SDK even before the COM implementation but that is stretching my memory too far to be certain.
It certainly didn't use SQL Server as its storage mechanism, however, I cannot guarantee that this hasn't changed.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
February 12, 2016 at 4:58 am
TomThomson (2/4/2016)
Definitely an interesting editorial, and a question well worth asking...it's choosing the soluion before studying the requirements...
Absolutely. As a developer who uses SQL Server (as opposed to someone whose career is based on SQL Server) I gain no benefit in promoting SQL Server for something it shouldn't do nor any for not promoting SQL Server for something it should do. Editorial summed it up: it depends.
Full disclosure: I have chosen both options and have been both right and wrong for each of the scenarios. Sometimes it is difficult to be certain in advance. Particularly with shifting requirements.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply