Category: Uncategorized

As SQLServerCentral grew, we evolved through a few email sending solutions to meet the demand. We started with a manual process, then went to a homegrown automated one, then moved through 2 purchased software solutions before coming back to a (new) homegrown one.

When we used to try and send 100,000 emails a night, scale became an issue. We had to complete the sending overnight so that when the load on the servers increased during the US workday, we would not be overloaded. However we also had to ensure that we didn’t mail multiple times to a person, so we had to build a solid, stable, reliable system.

We chose to implement email as a Queue system using a database table. This was prior to SQL Server 2005, though I’m not sure we would have used Service Broker if it had been later.

We stored all emails in a table like this:

create table Emails(

mailingid int identity(1,1),

datecreated datetime,

datesent datetime,

emailid int,

priority tinyint

recipient varchar(200),

sender varchar(50)

)

We would load this table with 100,000+ rows, one for each person receiving the email. We stored the actual email text in another table and joined on emailid to get that data.

Our sending process had a series of client machines that would query this table for a batch of rows, send a mail to each one, and then update this table. We built this for scalability as we could easily (and cheaply) add new client machines for sending mail. Now we just needed to handle concurrency issues.

Our first idea was to read the table, update some records, and repeat. So we’d do this

select top 100

emailid, recipient

from emails

where sender is null

and dateSent is null

set rowcount 100

update emails

set sender = ‘Client1’

where sender is null

and dateSent is null

set rowcount 0

— processing on the client here

update emails

set datesent = getdate()

where emailid = x

This assumes that Client1 was connecting. Client2 would use that name in the update.

If you read this and have any experience with T-SQL, you’d quickly realize there’s an issue here. Between the SELECT and the UPDATE, another client could read those same rows. So we enclosed it in a transaction, which means you could UDDATE then SELECT, or reverse that.

However that causes a concurrency issue. With 2 clients it took a little time, but we did some testing and found that there was repeated blocking. That wasn’t an issue at the time, but it could easily have become a bigger issue as we added more machines and increased the volume of sending.

So we went back to the drawing board and came up with a new approach.

We changed our code to this:

set rowcount 100

update emails

set sender = ‘Client1’

where sender is null

and dateSent is null

set rowcount 0

select

emailid, recipient

from emails

where sender =’Client1’

and dateSent is null

Not much different but two significant changes in the SELECT. First, but doing the update, we essentially removed those 100 rows from the queue. The update is quick, and as soon as it’s finished the next machines can begin reading their own rows. SQL Server provides the locking which prevents any machine from overwriting any other machine’s rows, so the “marking” of them by name keeps them from being updated again.

The SELECT statement is now quicker, since the number of rows scanned is small. We index those fields, and the number set to the sender is low, so this is a less resource intensive query.

Note that I’m not sure if we’re doing things the same way now. When Red Gate bought the site, they took over development and I know they kept some pieces of this, but potentially they upgraded or changed some of this process. However for the 7 years I ran the site and worked on it’s development, this proved to be the best solution for us.

Recently there was also an article on SQLServerCentral on dealing with queues that takes a slightly different approach.

My T-SQL Tuesday post for April is based on Aaron Nelson’s subject of reporting. If you’re unsure of what T-SQL Tuesday is, you can read about it’s origins here.

The Reporting Table

Years ago, actually well over a decade in my past, I was working for a small company and building an order entry/CRM/inventory/etc. system for them. We had upgraded their old DOS/Foxpro system to VFP/SQL Server 6.0, and we we in the process of adding some new enhancements. Most of these were actually more sophisticated reports that we designed to help them handle larger volumes of business needs.

One of the reports I was in charge of building was based on a spreadsheet that one of the supply people had built. It used a look back at orders for the past few months and then the inventory we currently had and give the person a guess about what quantity of the various products to order.

So I ported it, encapsulated the same logic that was in the XLS, and deployed it. A day later I was coming back from lunch when the supply guy, and the owner of the company stopped me to say that the report didn’t work and that I needed to fix it ASAP.

I put my tie back on and got to work, digging in with the supply guy. The first thing he showed me was two copies of the report from that morning. They had different data, but the same parameters (I was very glad I’d printed the parameters on each report). I was puzzled at first, but then noticed that the reports had been run a couple hours apart. Since the report was based on sales up to the current day, and the current inventory levels, it’s entirely possible that if the report was run at two times, the data might be different.

“That’s not right.”

That’s what I heard, and I started to show the guy how I could structure queries as of the time of each report in Query Analyzer and get the data he saw. However he didn’t want to hear it and I realized quickly that I needed another solution.

So I inquired how they used the report in more detail. He told me that they’d run the report and then share the data between a few people to discuss what to order. I told him he could “photo copy” the report (we printed things back then), and pass out copies, but his head started shaking before I got much past “copy machine” and so I stopped.

Back to the drawing board, and with a vacation scheduled, I needed solution quickly. My investigations led me to understand that the reason this was never a problem before was that the supply guy manually got data from other reports and assembled it into a spreadsheet by hand. That took him most of a day, and he’d email the report to everyone that afternoon and the next day as they went through their meeting, they would adjust factors on the XLS to decide how much to order.

I’d built in factors as report parameters, but now this was embedded inthe application and so you’d have 2-3 people playing with parameters on reports with their laptops, and comparing numbers. A static printed report wouldn’t work.

What I came up with was a report table. I built a table that duplicated all the columns of the report. So for a report that had productID, Jan Sales, Feb Sales, Mar Sales, Last Order, Current Inventory, etc., I had this table.

CREATE TABLE OrderingReport (

productID int,

JanSales numeric(10, 4),

FebSales numeric(10, 4),

MarSales numeric(10, 4),

LastOrder numeric( 10, 4),

CurrInv numeric( 10, 4)

…

)

I then scheduled a job that would “populate this table” every night around 2am, a time that was unlikely to impact anyone’s work schedule at this company. I reworked the report to query this table, using filters if they limited it by product, and return the same information all day.

I deployed it and the supply group was thrilled. Then could easily run a report, and it would compare to the data that a co-worker had. They could run reports, change factors, re-run them, change back parameters, and still end up with consistent reporting for the day.

Of course, I added one last enhancement to the application before I left. I had to give them a “reload” menu item to reload the entire table is some significant sale occurred, but this satisfied their need for consistent reporting in 1995.

I ordered a few recently from NewEgg. One for a desktop, one for a laptop. We’ll see how they do, but I was excited enough to snag this picture when they arrived.

 

$200 for 80GB.

After my editorial “The Missing Certification” I was amazed at the debate that took place. As of the time I’m writing this, this thread has over 7,000 views and 600 replies!

It’s incredible to see people debating certification, but what’s evolved is kind of a working thread that looks at creating a new certification that’s in between the MCITP and the MCM. I think those certifications are too easy, and too hard, respectively, for the average person.

In my mind, we ought to have a stepping stone for certification that includes some harder tests along with a narrower focus that can better assess if an individual has a skill. So far the debate has been a general focus, but I hope that they work towards a narrow focus that creates tests in specific areas.

As an example, in my mind, I’d like to have a series of T-SQL tests that examine how well someone can solve common problems, and implement well performing solutions. I’d think there would be 2-3 tests here, and that some level of scoring for each one that could be presented as a part of your resume, but also would show that you understand how to use the language. I’d anticipate tests that covered:

• Basic ANSI SQL
• T-SQL Enhancements (from 2005/2008)
• High performing T-SQL – Covering improvements that avoid RBAR type solutions and write code that performs extremely well.

We’ll see what happens, and while I know certification is no guarantee, I think that we can get a better process that has a good chance of actually assessing a person’s skills.