Re: large vs. many tables

Warren J. Saccente of Bellcore writes: > Larry Ciesla of AT&T Network Systems write: > >>Here is a question for anyone out there with experience with large databases. >>I am part of a team designing an OnLine database that must store about 22 >>million records that look something like this: [ database description deleted ] >>These changes will be done in a batch mode, and will consist of inserts, >>deletes and updates. If we were to put all this data in a single large table, >>given the overhead associated with maintaining the indexes, we are concerned >>that the batch processing will take much too long. Since we are at the >>beginning of the project, we can entertain alternatives to one large table. >>We could spread the data out in a bunch of smaller tables. [ description of Warren's project deleted ] > My problem coming into the process late in the game is undoing > what been done. It is almost inpossible to change our architecture because > of all the embedded code at this point. > > My advise to you is put a lot of effort into making the correct decision. > Try it different ways and test the performance with isql. As far as the > index maintenance on updates, you might want to drop indexes before updating > and then re-establish them after. However, with only 3-7 % turnover, I > suspect that you'd be better off doing your updates with the indexes on. > > Another project related to the one I'm on has just made the decision to break > a few of its tables that they consider big into multiple tables out of fear of > bad performance. My advise to them was to figure out how big is big in > that performance will be degradated (they didn't listen). I had a similar experience. I came into a project where the data for several tables was split into 1 of 4 tables depending on the value of 1 column. It took me 2 years to convince people to combine them and I am still finding places buried in our system that depend on the old table structure. The biggest problem with the old way is that it complicated the report writing. We had to use 4GL reporting for the bulk of our work since ACE would have had to do a big union (Yuch!). The real problem that drove the original designers to this approach was the fact that they didn't have the indexes set up properly. Once that was taken care of, the only degredation was on sequential searches. However, my experience had nowhere near the volume discussed above. EBD -- Elliot Dierksen Work) e.dierksen@att.att.com (407) 660-3377 Home) elliot@alfred.UUCP (407) 290-9744