JM played a small role on a gargantuan project: contractor working on large SMS sending and routing system. Of course, JM wasn't the only outsider on the project. The company brought in a whole host of vendors and divided them up into Consultants and Contractors.

Consultants had minimal supervision and an hourly billing rate that could only be expressed using three digits. Contractors, on the other hand, billed much less per hour and were assigned to work for either a Consultant or an Employee. Aside from that, and the fact that Consultants all had business cards with incredibly ambiguous job title such "Solution Developer," "Solution Specialist," and "Doer of Things", there was little difference.

JM's team was responsible for interfacing a web-based user interface with an SMS broadcasting service developed by one of the consultant's teams. The web UI would send the service a message with a list of recipients and the service would convert it to individually-addressed SMS messages and then send the messages to the appropriate queue.

Problems kept cropping up with the message broadcast service: clients were complaining about stability and that only a portion of the expected messages were being sent to the appropriate gateway queues. Naturally, JM's team was to blame: they were the Contractors and not the Consultants. They were the ones tasked with coming in during off-peak hours (5:00AM Sunday morning) to debug the live service.

The first thing they noticed when they came in was that the service was not running as a Service at all -- it was running as an Interactive Windows Application. I'm talking the GUI kind of application; the ones that don't automatically restart when the computer is rebooted. That explained stability problem number one.

The main problem was with how the Consultants populated the queues from an individual message: they did it row by row. Now, keep in mind that the consultant's service had to handle the constraints of bulk-sending, often times tens of thousands of individual SMS messages per transaction. Yes, it's one of those systems used by those "Text 782537 Your $1.99 Joke-A-Day Subscription" services.

A "smallish" broadcast of 2,000 messages took about three hours to process. From a technical perspective, this involved inserting 2,000 rows in a "Queue" table in their Microsoft SQL Server database. Obviously, the users were not to thrilled about this either, especially when they wanted to broadcast news to their subscribers.

Although the Consultants were very protective of "their" source code, JM was able to get hold of it for debugging purposes. He found out exactly what was causing the "service" to take so long: 

//SQL Query to retreive all unbroadcast messages
string sqlCmd = 
  "SELECT o.*, r.*, ..., c.*, u.* " +
  "  FROM Orgs o, Recipients r, Contacts C, " +
  "       ... " +
  "       Groups g, Users u " +
  " WHERE m.Group_Id = g._Group_Id " +
  "   AND u.User_Id = gu.User_Id " +
  "   AND ... " + 
  " ORDER BY o.Priority_Code, o.Delivery_Num, ...";

//Execute SqlCommand
DataTable dt = DataHelper.ExecSqlToTable(sqlCmd);

//For each row retreived, insert a row into the Queue table
for(int i=0; i<dt.Rows.Count; i++)
{
  //Insert Sql command
  SqlCommand insCmd = DataHelper.CreateCmd(
    "INSERT INTO Queue (Sender_Id, Recipient_Id, ...) " + 
    " VALUES (@Sender_Id, @Recipient_Id, ...))";
    
  //Add Parameters to command
  AppendParam( insCmd, "@Sender_Id", dt.Rows[i]["Sender_Id"]);
  AppendParam( insCmd, "@Recipient_Id", dt.Rows[i]["@Recipient_Id"]);
  ...
 
  //execute command
  insCmd.ExecuteNonQuery();
}

In essence, this process would select a giant set of results from the database, loop through each row, and then insert a new row into another table. JM knew he could do better and rewrote the procedure to use a single query to handle both the INSERT and the SELECT. Something like, 

INSERT INTO Queue (...) SELECT ... FROM Messages m, Recipients r, ...

It ran in less than five minutes instead of several hours. Sadly, since he was "just a Contractor," his improvement never saw the light of day. The client instead bought a much faster and clustered database server.

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!