david sidlinger

Over the years, I have been treated to a wide range of what I can only call perversions of technology. By far, the funniest I have seen is the Numbers Table. Why would you need a table of numbers, you ask? Because you need to count, of course.

The team of “developers” that came up with the Numbers table had heard that making a database call has overhead. Apparently, they had not heard about M.A. Jackson’s Rules of Optimization:

1. Don’t do it.
2. (for experts only) Don’t do it yet.

The system in question used pessimistic locking, another great story. There was a method to release multiple locks, which were stored in a database table. There was no way this performance critical code was going to call the LockRelease procedure for every lock. That would be insane! Presented here is the code that used the Numbers table. It’s reconstructed from memory, because I am too lazy to go digging through source control history. I have expanded a SQL function into the procedure definition and left out the irrelevant code.

First, the C# code:

public void ReleaseMultipleLocks(params Lock[] locks)
{
    StringBuilder builder = new StringBuilder();
    foreach(Lock lock in locks)
    {
        builder.Append(lock.LockId.ToString().PadLeft(11, "0"));
    }
    // Code to call the stored procedure with the (potentially) gigantic string
}

Now, the SQL:

CREATE PROCEDURE dbo.ReleaseMultipleLocks
(
    @LockIds nvarchar(4000)
)
AS

DECLARE @locks TABLE
(
    LockId bigint NOT NULL
)

INSERT
    @locks
SELECT
    CAST(SUBSTR(@LockIds, (11 * (Numbers.Number - 1)) + 1, 11) AS bigint)
FROM
    dbo.Numbers
WHERE
    Numbers.Number <= (LEN(@LockIds) / 11)
ORDER BY
    Numbers.Number

-- Use the @locks table to update the release datetime on
-- the specified lock records.  If I remember correctly, it 
-- used a cursor.

Wow.

So, in order to avoid those “expensive” database calls, we: build a string out of all those pretty, compact integers, pass that big string on the wire to the database server, create a table that holds (wait for it) integers, and use a huge table of numbers to do string parsing in SQL. Before you ask, this table was not used for anything else.

For your additional amusement:

• A long/bigint holds 19 digits, not 11.
• The Numbers table had hundreds of thousands of rows. You only need 364 numbers to parse the maximum string length that could be passed.
• There were mysterious gaps in the numbers table. None of them were in the range that would have affected this procedure, but…c’mon.
• The Numbers table was rebuilt every time the system started from hundreds of thousands of INSERT statements…in multiple databases.
• Why you need to use a double-byte character set for a string that holds only digits is beyond me.
• The team that came up with this was supposed to be our crack team of Enterprise Infrastructure Developers.

I’m posting this story and, soon, others I can bring myself to remember for a few reasons:

1. We have told them plenty of times to developers joining the team. Pointing them to a link will take a lot less time.
2. I want to remind myself how good I have it now, relatively speaking.
3. To, hopefully, stop myself from falling into the optimization trap.
4. Poking fun at this helps me get rid of the bad feelings associated with it.

Update: A co-worker corrected some of my code. SUBSTR is 1-based, and my WHERE clause was jacked up.