Re: AQ vs. PL/SQL for implementing a messaging queue

stevengarcia_at_yahoo.com (Steven Garcia) wrote in message news:<7f430eb1.0410162056.45237b67_at_posting.google.com>...
>Has anyone tried to simulate a messaging queue using SQL?

Yes, I was a project where we implemented a message queue in PL/SQL using a method similar do what you described. Two systems have been in production for about 4 years now.

>What kind of results did you have?

Good, the queue is certainly not the bottleneck. It processes about a million messages a day (almost all during business hours). During peak load the queue gets a little backed up (because the consumer is the system's bottleneck) but it's no problem.

Keep in mind though that an implementation like this will be synchronous, not asynchronous. For example, for certain messages we needed to send out an email and we had to send it out ASAP - at the time the message was put in the queue. Most of the time that wasn't a problem, but sometimes the mail server was slow to respond and so the whole queue got locked up. We had to change a few things so we could send email asynchronously.

I have never used AQ so I can't comment on if that would have worked better.

No matter which approach you choose I highly recommend you test with the expected load. And don't just test on an empty database, test on a database similar in size to what a fully loaded production database will be. Received on Sun Oct 17 2004 - 12:38:32 CDT