Re: database size vs. performance questions

In article <1993Jul1.150352.615_at_gtewd.mtv.gtegsc.com> davidsonj_at_gtewd.mtv.gtegsc.com writes:

>I am attempting to estimate database performance based on a prototype
>we have developed. I have not attempted to fully populate the database
>for the timing measurements made (I don't have time to generate that quantity
>of data :-), instead I was hoping someone on the net could help by answering
>a couple of questions:
>
>1) How does the size of a table affect performance? (Obviously, the larger
> the table, the longer it takes to query.) Is there a factor which could
> be used to translate the response time for a table with 1000 rows
> versus a table with 100,000 or 1,000,000? Is there a similar effect
> on insert or update performance? (I did a little test on one table and
> saw little or no difference between inserts to a table of 1000 rows
> versus 100,000 rows. Does this jibe with your experiences?)

It depends a bit on whether it is indexed or not. Most databases maintain a list of empty pages and a pointer to the first one. If there is no index, inserting a row should be relatively independent of the table size ie constant.

If there is an B-tree index on the other hand, it should be proportional to log(n) so it will take longer as the table gets larger (but the degradation shouldn't be too bad).

With a hash index, it should be reasonably constant, but you may find restrictions on deletes and will almost definitely have restriction on database size.

Searching for a single row, of course, with an unindexed table will be proportional to n. With an B-tree index it will be log(n) and with a hash index constant.

>
>2) Do changes in the overall database size affect the performance of
> queries (and updates) of a table whose size is unchanged? For example,
> is the performance different when querying a 1000 row table that is
> part of a 20MB database versus a 100MB database (assuming all other
> factors are equal and both databases are populated near capacity)?

It shouldn't do.

Oh yeah, I take no responsibility for irresponsible database designs that don't fit into the above theory.

Regards

-- 
Chris Jack