Re: Database performance and size

In article <4c952s$90p_at_nosy.bart.nl>, Nick Keeman <nick_at_bart.nl> wrote:
>In article <4bapid$595_at_newsbf02.news.aol.com>,
> 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.
>>
>The performance will get slower if the number of rows gets bigger!!
>
>For select-statements:
>
>- Full table scan's have to read more rows from disk or SGA. (Linear
> slow down)
>- Indexed queries work with search algorithems. a select on a table with 100
> rows will be about twice as slow as a select on a table with 10000 rows.
> (logaritmic slow down)

In most systems I have seen in which there has been database growth another factor is even more significant in slowing performance - an increase in the number of rows meeting query selection criteria.

For example, a query which retrieves a single account and all its transactions will, if there are more transactions for a given account, retrieve more rows and perform more I/O, and so become significantly slower.

It is very important to remove data which is no longer of prime interest from tables if performance is to be maintained.

John

-- 
--------------------------------------------------------------------------
John Symington                                    Phone  +61 3 9885 5829    
Common Sense Computing                            Fax    +61 3 9885 0116    
16 Dunlop St, Ashburton, Vic. 3147 Australia      E-Mail john_at_comsense.com