T-SQL Tuesday #74–The Changes

It’s T-SQL Tuesday time, the monthly blog party on the second Tuesday of the month. This is the first T-SQL Tuesday of 2016, and all you have to do to participate is write a blog post. It’s supposed to be today, but write one anytime you can and look for the invitation next month.

I’ve got a list of topics here, but watch the #tsql2sday tag on Twitter.

This month’s topic comes from @SLQSoldier, Robert Davis. The topic is Be the Change, and it’s a good one.

Quick Changes

I’m going to write about SQLServerCentral here. Years ago we were updating our email system to send a high volume of email in two ways. At the time we’d considered purchasing software from others, but found the cost to be significant at our volumes (5-6 figures a year). Instead we needed to handle emails stored in our SQL Server database in two ways:

  • Thousands of bulk emails sent overnight, as quickly as possible
  • Quick, high priority emails sent in response to actions

These two conflicting requirements meant that a simple queue of emails to send wasn’t easy for us to design around. We also needed to deal with the issues of scaling, so we wanted to have mutliple separate machines that could help spread the load.  We were building a small .NET process that would run every minute and send a series of emails.

Our design process led us to the need to build in priority levels into our table. We couldn’t think of more priorities, but we allowed for them with a smallint. Our bulk emails were inserted with a priority of 2, and the registration emails, forum notes, etc, were stored with priority 1.

Once we had a separaton of emails, we needed a way to determine what was sent already. To do this, we used a NULL date for the sending date. This allowed each process to determine when new information had been inserted into the table, and needed to be processed.

This worked well for a single machine. The process would:

  • query for xx priority 1 emails
  • send priority 1 emails
  • update sent priority 1 emails with the sent date/time.
  • query for yy priority 2 emails
  • send priority 2 emails
  • update priority 2 emails with sent date/time.

The updates actually occurred for each email sent, so we could easily track the time/order of sends for troubleshooting purposes. We would query a few hundred emails each minute, let’s say 500, knowing that was the rate at which we could send emails. We wanted all priority 1 emails to go, so our value for yy would be  500 – xx.

As we worked to scale things out, we also needed to track what items were queried by which client. Our solution here was to add a machine name to the data, which was blank when emails were inserted, but would be updated by a client with its name as it queried rows. Since we were looking to determine which emails to send, we’d update xx rows with the name of a client process and then query back those rows. The query used the sent date of NULL with the client name to get the correct rows.

Using a combination of the date sent, the client name, and the priority, we could easily manage detecting and working with changes to this table and build a high volume queue that worked extremely well on SQL Server 2000, and all versions since.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.

5 Responses to T-SQL Tuesday #74–The Changes

  1. SQLWayne says:

    Well, the system didn’t do too good yesterday. Normally I always have the newsletter waiting in my Gmail account when I get in to work, which is my main account on SSC. I created a second account to get the newsletter in my work email, and that one arrived at 5:21am MST whereas the Gmail newsletter didn’t hit my inbox until 4:23pm. I don’t know what happened to cause an 11 hour delay, and it isn’t all that important, but it was rather odd and uncharacteristic.

    • way0utwest says:

      Strange. Not sure why. I’ll look and see what might have delayed things. Always possible I messed up the schedule and someone kicked it off in the UK late.

  2. sqlsoldier says:

    Thanks for participating, Steve. That was a unique solution for a common problem. We had a similar issue at a previous employer and built a C++ app that could send emails really fast.

    • way0utwest says:

      That’s what we used, a separate app. In our case, a C# service that we could deploy (scale out) to multiple boxes that let us grow as needed.

  3. Pingback: Queues in Databases – Voice of the DBA

Comments are closed.