Jarrett House North

Rogers Cadenhead posts about something that has been on my mind recently, the ending of support for Visual Basic 6. A while ago Scoble and Dan Appleman blogged the opposing viewpoint: making customers move their apps off VB6 is the right thing to do because VB.NET is a far superior language.

Let me illustrate a real world perspective on the debate, rather than the strawman that Appleman provides. Say you are a small software company that makes a product that is written in a variety of languages, and a core part of the application suite having to do with automatically sending e-mails is written as a Visual Basic 6 application.

Now why would someone write an email notification process in VB6? I don’t know. Why would someone write the standard procurement system for the Department of Defense in PowerBuilder? This happens all the time: mission critical apps get written in less than mission critical languages, and then get maintained, more or less, forever.

So fast forward about eight years from the creation of this app. The mail landscape has changed, and the architecture of the mailing process, which used to leverage client apps for sending mails, needs to change too. Email viruses and spammers have made old approaches to writing mail functionality painful; organizations are abandoning POP/SMTP based mail and retreating to MAPI. Meanwhile your VB app, which relied on a once attractive piece of third party code to provide MAPI support, is stagnating, and has started to show issues with age (like memory leaks, and interop issues with the security measures added across the Microsoft mail stack). In other words, it’s time to freshen the app. So the natural thing to do is to look at Visual Basic.NET, the successor to VB6, right?

Um, wrong. VB.NET famously no longer supports the programming interfaces used by VB6. And the VB6 to VB.NET migration tools bite; in fact, the tool blows up prior to successfully migrating the VB6 project. And no one in your development team has expertise in the .NET framework. In the years since VB, the team has moved on to C++ and Java. So which language will be the natural choice for migrating the mail app? Not VB.NET. What might have been a no-brainer to move up the ladder to the next version of VB has turned into a major nightmare for our small software company, and the only clear loser is Microsoft.

I lost my copy of the classic programmer’s cookbook Refactoring several years ago, alas, but its philosophy of careful replacement of smelly code with clean code to improve the performance and maintainability of a software program is one that has stayed with me long after I ceased being an active programmer. One regret I had about the book was that it primarily addressed refactoring for object oriented languages (all the examples were in Java). But one of the biggest opportunities for cleaning up code is in non-object oriented languages such as Transact SQL. Recently I had an opportunity to clean up some legacy stored procedure code that I was adapting for an integration project I was working on, and one particular refactoring struck me as especially useful.

A common mistake when writing stored procedures is to overuse cursors. The cursor provides a way to operate on one row of data at a time, which is logical to a programmer used to thinking about working with arrays by looping through them. But SQL is fundamentally a language that is about set operations, and you can realize tremendous gains if you can stop operating on one row at a time and instead operate on a bunch of them at once. Microsoft provided a way to do that in SQL Server 2000 by introducing the table datatype.

Table variables help us by giving us an entire rowset in memory that acts precisely like a table in the database. An article by Alex Grinberg on SQL Server Central illustrates some of the applications of the table variable. I used one of the ideas to replace a cursor that was being used to import data into a table in our system using a counter table.

In fact, the counter table probably caused the use of the cursor in the first place, because a developer had helpfully written a stored procedure to get the next ID value from the table (a common construct for database structures that need to be cross-platform and therefore can’t use features like ). Such a function is a good idea in a client application because it enforces a consistent method for creating IDs for new records, but because it enforces getting only one ID at a time it leads inexorably to row-at-a-time inserts and other abuses of SQL.

So the first step in converting this insert cursor into a sensible insert was to create a new procedure that allowed me to get a bunch of IDs out at once. Where the original procedure had as inputs the table for which the counter was being incremented and the new ID as output, this one also took the number of keys, the starting key value, and the ending key value. Thus even for a very large number of rows, I was only performing a single transaction to get a block of keys to work with.

Now the cursor replacement. The original logic of the stored procedure declared a select cursor against the staging table containing the data to be imported, then opened the cursor, got a new id, and inserted the contents of the cursor plus the ID into the destination table. So for each row of data to be imported we performed two transactions, an update on the counter table and an insert to the destination table. For 500 rows, this was taking about 25 seconds—not huge, but definitely a place where there could be a big improvement. Here are the steps I used to replace the insert cursor:

1. Declare a table variable, @tbl, with the same columns as the cursor, plus
   an ID column defined with the IDENTITY property.
2. Populate @tbl with an INSERT INTO…SELECT FROM statement that draws data from the staging table. Now each row in @tbl has the values from the staging
   table plus a temporary ID, ranging from 1 to the number of rows. (We didn’t specify a seed for the ID field; more on that in a second.
3. Count the number of rows in @tbl (select count(*) from
@tbl).
4. Call the updated counter procedure with the number of rows in @tbl, getting
   back the first ID in the range (call it @startid.
5. Finally, insert into the destination table, selecting from @tbl and adding
   @startid to the identity column in @tbl. Since the IDENTITY column
   started at 1, we want to subtract 1 from @startid for each row as well, or else
   we’ll exceed the range of keys reserved.

Here’s some sample code:

DECLARE @tbl TABLE (col1 int, col2 int, col3 int, temp_id IDENTITY (1,1))

INSERT INTO @tbl (col1, col2, col3)
SELECT stagecol1, stagecol2, stagecol3 FROM staging_table WHERE ...

DECLARE @startid int
DECLARE @total int

SELECT @total = COUNT (*) FROM @tbl

exec p_increment_counter_multi 'dest_table', @total, @startid OUTPUT

INSERT INTO dest_table ( dest_id, destcol1, destcol2, destcol3 )
SELECT temp_id + @startid - 1, col1, col2, col3 FROM @tbl

So instead of two transactions for each row in the staging table, we end up with four transactions total, one of which is a select to populate the table variable and one which just gets the total number of rows. The performance benefits can be substantial: In my particular procedure, for 500 source rows, the time to execute the procedure went from 25 seconds to 6 seconds.

Of course, you could have done the same thing using a temporary table, though it appears that table variables are slightly more efficient in memory. But the basic principle is the same: an application-level counter column is no reason to insert one row at a time.

The same principle, with a twist, can be applied to update cursors as well; more on that in a while.