Databases: An Equal Opportunity To Be Humbled

Earlier this year all our tax billing programs had to be updated to print a new kind of bar code. The US Post Office notified us with plenty of lead time that the older Postnet bar code would not longer be accepted as of May 2011. Instead a new “intelligent” bar code would be used. That bar code contained many more characters, some constant, like our town’s ID, and some varying, like a unique billing serial number, not related to the bill’s account number.

Our billing software uses the Informix SE database, which accepts transactions. I thought it won’t hurt to add one read and write to a completely different table and still within the same transaction protecting reads and writes to tables that store bills and balances. I was mistaken.

A billing run that normally takes less than ten minutes increased about eighteen times.

The fix was to invent a new scheme where starting serial numbers and number of bills to be printed are stored, so possibly overlapping bill runs would be guaranteed unique serial numbers on each bill across all bills. This is the Post Office requirement for the serial number portion of the intelligent bar code.

All the serial numbers are recorded at the end of the bill run, by using database tools to load the table, instead of SQL. The run time returned within two minutes of the original.

So, no matter what you think is going to happen with a database, something else can and will take its place. It is good to test and gather performance metrics.

Advertisements

Leave a comment

Filed under database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s