Considering future performance in your database

Ten years ago I worked on a project where we did a lot of fancy things with a number of databases. I learned a lot during that project but I also heard a funny story a coworker told me:

I was called in to fix a problem with an accounting system. The customer described the problem as an invalid status message. It turned out that when they did reports and searches on the master table holding all financial activities it took several minutes to complete. This was not the customer's concern. They were concerned that the status message said "hold your breath and count to a hundred", which was false since the operation typically was not completed before you could count to a hundred...

The basic problem was that the developers never tested the database nor the accounting system with enough data to simulate several years of accounting information. You don't want to do that. There are also a number of other things you probably don't want to do. This article (register for a free account to read) covers a few good things to look out for. For example you there is usually a bad idea to do "IF (SELECT COUNT(*) FROM ... WHERE ...) > 0" when "IF EXISTS (SELECT 1 FROM ... WHERE ...)" works just as fine without the potential problem actually counting the number of rows.

Comments (0)

Skip to main content