said by guppy_fish:Enterprise level database systems are very dependent on the dataset size, number of records, its very likely that this started out fine and as the database has grown, the performance is dropping exponentially, its a full time job for a dba to do nothing but monitor an application/database and then have to rework the tables, tuples, keys ect.
It sounds as if your company bought something , maybe even sold without the knowledge and db system needs full time support.
Its also possible that its hardware limits the program has run into as the database grew. Again, this is a dba's job to be the expert and find the solutions, its very common and some of the most highly sought after professionals that can use the db tools to see where resources are being consumed.
You may not be happy, but its definitely not unexpected.
My guess is your memory and caching are no longer adequate for the size of datasets being processed. db's can chew up huge amount of ram and when things have to access disk, everything comes to a crawl.
Also, if your in a VM environment or server that is doing other services this can have near lethal effect on performance
That all makes sense..except that it is not the topic. My problem is that our support contact did not actually investigate the problem far enough to know that the custom program even had high logging. I used to be on the other side at one of their competitors on the support side. I would have checked the custom program specs if I did not know, he did not.
Now, as to your scenario, yes, that all makes sense, except I know it is not the case. I know for several reasons, we purge old data so we always have about the same amount. We recently did a reorg and reindex on the DB tables, it was the same after as before. We also have another warehouse on different hardware, DB, etc, that is not as busy and has less data. The performance is the same. They do not have a problem with this because they operate differently and in a different time zone so the routing info comes in long before they need it. The problem is something in how they go about this internally to the system.