Re: INSERT performance on large table.

James Williams <techsup_at_mindspring.com> wrote:
>I have a user who wishes to initially load 4 million rows on a table.
>After this he will insert 4000 per day. I don't like this approach. I
>would like to maintain a small staging table and then let him have an
>archive area for the 4m plus rows.
>
>
>A table with 4m rows in it. What is the issue of an additional 4k rows
>each day from a performance standpoint.

4 million rows aren't a problem, I deal with Oracle databases with tables with several hundreds of millions of rows, and there's almost certainly people with billions of rows in a table.

4000 rows per day is almost negligible, it would take nearly 3 years for the table to double in size. If all accesses to the table are via B*tree indexes, doubling the size of the index will hardly affect the performance. Going from 4m to 8m rows will make an index roughly 4.6% slower (just taking a straightforward O(log N) comparison, and hoping all other factors cancel out :-).

-- 
Andrew Mobbs - http://www.chiark.greenend.org.uk/~andrewm/