Re: Database performance and size
Date: 1995/12/21
Message-ID: <30D9A1D8.2CA3_at_venice.sedd.trw.com>#1/1
Stuart Cracraft wrote:
> markp10013_at_aol.com (MarkP10013) wrote:
> >I was wondering if someone could shed some light on this debate;
> >Some people I've talked with think that the performance of a database will
> >be
> >affected as the number of rows in the tables grow. Others say that
> >performance
> >won't be affected at all no matter how large the database gets. Which
> >opinion is true? I know this sounds trivial, but it's seem to be one of
> >those questions that tests theory and real world experiences.
> Of course it will affect performance. A large table without indexes is
> provably less efficient, on a linear/sequential basis, than one with
> indexes. In v6 Oracle, b-trees using binary search algorithms (std.
> comp sci class theory here) are used, I believe. In v7 Oracle,
> I believe there are some options for hashing for such searches.
> Personally, I've experienced a table that was improperly or totally
> unindexes. Performance improved drastically. I found the response
> to the users in the forms went down from 5-10 seconds for an
> insert/update in an indexed table to less than a secon, after getting
> a good balance of indexes (simplification generally.)
Interesting that this should come up. I was just told of a system where 8 weeks worth of data is kept online, the rest archived. In order to keep things speedy, a separate set of tables is kept for each week (e.g., Timecard_Week1, Timecard_Week2, etc.) Inserts and updates are performed on the tables, queries done against a view which unions the tables. every week one of the tables is archived and the rows deleted so the table can be populated with fresh data.
Has anyone else seen this? Is this a common practice? It seems rather unfortunate that we have to take a nice clean paradigm like the relational model is and dirty it up in order to achieve acceptable performance levels.
They certainly never taught this in college!
Matthew M. Lih
Software Lead, SAIN Project
TRW Data Technologies Division
Received on Thu Dec 21 1995 - 00:00:00 CET