Re: Database performance and size

Stuart Cracraft writes:

> 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.)
>
> I'm sure some of my facts are off. But hopefully someone else will
> add to the above or correct.
>
> --Stuart
>
>
Everything Stuart says is true, and here are some additional personal observations:

1. Performance degradation increases as the number of rows in a table increases.
2. Performance degradation increases as the number of schema objects increase (mostly for larger tables and row counts).
3. The primary factor in all of these equations is physical access. Even extremely large tables (millions of rows) can be accessed quickly if properly indexed and if isolated on low usage tablespaces. Faster disks or arrays can also help.
4. Performance will tend to stabilize after a certain point in time for OLTP due to the random nature of access. This is nearly always helped by proper indexing.
5. Batch performance will be more affected by the addition of new rows due to its sequential nature. For heavy batch update, indexing may actually degrade performance due to the tree balancing necessary to maintain a balanced index.
6. Backup and recovery are also affected by large row counts.
7. De-normalization may help performance considerations by reducing the number of physical I/Os neccessary to construct a query result set. As always, this comes with certain integrity tradeoffs.

All of which adds up to yes, performance suffers as data is added. It may come slowly and may never be noticable to the mere user, but the delays will inevitably show up.

Brent
bxtucke_at_cp.mnet.uswest.com Received on Thu Dec 21 1995 - 00:00:00 CET