Re: Throughput and storage requirements questions for modern vs old database processing apps

John Becich,

I want to give you some very general commentary on old databases that run slow. This is by way of response to your question about whether database SW is the exception to the rule that says that older SW runs very fast. My comments
are unavoidably vague, because there is too much about your specific situation that I don't know.

Old databases sometimes run a lot slower than they did during the first few years of their operation. There a lot of things that can bring about this slowdown, but here are two fairly general ones: mission creep and overpopulation.

Mission creep is where the number of different ways the data is used expands over time. Mission creep in reading data sometimes results in queries that the original database was just not set up to handle. Those queries might run agonizingly slow. Mission creep in writing data could result in new data structures that don't have the necessary indexes.

Overpopulation just happens when the same old data is being accumulated, and is used in the same old way, but there is a lot more or it. Sometimes, the transaction data just builds up over time. With each added amount of data, processing is a little slower. Sometimes the slowdown is negligible until a certain point, after which it becomes dramatic. Sometimes the population buildup isn't transaction data, but reference data.

One database had a table with about 15 "cost centers" in it. There was no index on this table. The program that added a new contract had to scan this table about 20 times in the course of validating various cost centers for the new contract. It was running 3 to four seconds slower than it should, but nobody even knew it. Then the company reorganized. Suddenly the cost center table went to almost 1000 entries. Equally suddenly, performance went into the toilet. It was taking about 15 *minutes* to put a new contract in.

Find the problem, build the necessary index, and wham! All of a sudden the system was running "better than new".

This was all on very different HW and SW than what you have described. But the principles are the same.

So this may explain why your experiences with old SW and with old databases don't seem to agree.

--
Regards,
    David Cressey
    www.dcressey.com