Software needs to run quickly. Whether it's to get a response to a shopper so they don't get bored and click on to the next site, or performing calculations on some data that is urgently needed downstream. Efficiency is important!

To that end, most developers attempt to write code that runs quickly. Sometimes, the code needs to run more quickly than conventional means will allow. In those cases, smart developers will figure out how to game the system to get the computer/network/disks/etc. to get things done more quickly than the usual methodologies permit. For example, you might try to cut network overhead by stuffing multiple small requests into one buffer to take advantage of the leftover space created by network packet sizes.

Villa Wunderkind Name

Of course, if you're going to do something sophisticated, it's common courtesy to document what you're doing, and why, so the next person who has to maintain your code will know how to proceed.

Unfortunately, not all developers are smart, or considerate.

Ralph worked on a mature Java/C++ project that needed to replace an even more decrepit mature reporting system. After several half-hearted starts, his company bought a competitor that had a pretty decent reporting system that was written and supported by The Wunderkind.

The Wunderkind convinced management that the reasons that he was so successful were that:

  • He was using the latest Microsoft technologies (C#, .NET, MSRS, etc)
  • He was using "agile" development techniques

Management decreed that a new team, headed by The Wunderkind, would be formed and tasked with creating a brand new, ultra-modern, full of bells and whistles reporting system.

While they went off into the bright new future, Ralph was content to continue slogging away in other areas using the prehistoric technologies of Java and C++...

Fast forward 2 years, and the reporting project had blown several deadlines. It been scaled back to a shadow of what it was supposed to be. Almost all of the feedback from product management or their UI designers had been deemed technically impossible to implement.

Apparently, management had been asleep at the switch...

Finally, with yet another deadline looming, The Wunderkind resigned, and Ralph (who had not been involved in this project in any way since its inception) was called in to figure out exactly how much trouble they were in.

He got a cursory introduction to the "architecture" in a series of meetings, figured out the hot-spots that required immediate attention, and got to work.

One of the first things that jumped out at him was that there was no DBA working on the team. Apparently, The Wunderkind had done all of the SQL work at his previous company, so there was no need for a DBA. Besides, DBAs weren't "agile" enough for a team that was moving so quickly...

As Ralph spelunked into the bowels of the system, one of the first things that jumped out at him was a strange looking routine that converted a list of ID objects into a comma-separated string. There are no comments ANYWHERE in this code (apparently comments are not "agile"), but this functionality seemed kind of odd given the circumstances. When he saw this long list of IDs being passed as a parameter to String.Format() (the C# equivalent of sprintf) and passed into the DB layer, the klaxons in his mind really started blaring:

  StringBuilder idBuilder = new StringBuilder();
  foreach (object id in idList) {
    idBuilder.Append(id.ToString() + ",");
    if (idBuilder.Length >7500) {
       command = String.Format(queryBase, 
                               new object[] { TableName, 
                                              workSetId, 
                                              idBuilder.ToString(), 
                                              userId
                                            }
                              );
       [snip-- execute the string as a SQL command]
       idBuilder = new StringBuilder();
    }
  }

  command = String.Format(queryBase, 
                          new object[]{ TableName, 
                                        workSetId, 
                                        idBuilder.ToString(), 
                                        userId
                                      }
                         );
  [snip-- execute the string as a SQL command]

Fearing the worst, he chased down the definition of queryBase to find this:

private const string queryBase= @"
DECLARE @ids varchar(8000)
SET @ids = '{2}'
DECLARE @commapos int

CREATE TABLE #t( i int )

WHILE( 1 = 1 )
  BEGIN
    SET @commapos = CHARINDEX( ',', @ids )
    IF @commapos = 0 BREAK
    INSERT INTO #t (i) VALUES(LEFT( @ids, @commapos - 1 ))
    SET @ids = STUFF( @ids, 1, @commapos, null )
  END

insert into {0} 
select {1},i,{3}, getDate() from #t

drop table #t

Let's see if we can figure this out. The Wunderkind turned a list of ids into a comma separated string, in chunks of about 7500 characters. Then he parses each of those strings into a temp table of ids. Then inserts the temp table into the target database table.

Don't get me wrong, I've turned thousands of database calls, each to insert a single row, into a single structure of arrays of records (each representing a single row), passed the whole thing to the database server and let it grind through it in a single call. This eliminated thousands of round trips to the database and substantially sped things up. Of course, it also drastically complicated both the Java and stored procedure code and I had to document it to-the-extreme to allow others to support it. But, I only did things in a round-about way when it was absolutely necessary. And there were documents - with pictures - explaining what was going on.

When Ralph showed this code to one of their DBAs to make sure it was doing what he though it was doing, the DBAs' only comment was Please tell me this wasn't found in our product...

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