Month: June 2005

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.

There are other tabbed browsing add-ons for IE, but when I saw that the MSN toolbar had added a tabbed browsing enhancement, I decided to check it out. After all, I still know people at MSN I can yell at if there’s something wrong. And, actually, yeah, there’s a few things I would change.

First things: I can’t stress how glad I am to have tabs rather than the damned taskbar group (multiple browsers collapsed into one toolbar button with a number on it). There’s no good way to do blogging and newsreading with toolbar groups. Tabs are a hell of a lot more usable. I also appreciate that the toolbar supports the standard CTRL-T keyboard shortcut for creating a new tab.

But there are quite a few missing features from the MSN implementation. For one thing, there’s no option to make new links open automatically as a tab in an existing browser window. So if you click a link from email or another application, it still spawns a new browser window. And links defined to open in new windows still do; there’s no way to override that behavior to make the new window open in a tab instead, as there is with Firefox or with Safari. Also, there is no “open in new tab” on the right-click context menu, which renders the tab feature a lot less useful.

Verdict: on a scale of 1 to 5, a 2. The new tab support is better than having no tabs at all, but to call it half baked is too generous. It feels like the team focused on tabs as a feature, rather than looking at the customer problem, which is window clutter and impaired productivity, and thinking about what is required to address that in a tabbed browser implementation. Microsoft is traditionally good at thinking through user scenarios; I look forward to the next version.