number of rows vs. performance ?

[karl koch]

hi ranchers
even though this is a performance question i think it fits in JDBC forum since it is DB related...
is there a general rule for the performance hit i take if a table grows (inc. the number of rows) ?
we will add about 2.5 million rows a year to a single table...
we need to keep the data of one year in the DB for prbably 5 years -> 12.5 million rows if they all go to the same table.
any advice ? is this ok ? should we avoid such big tables at any cost ? how to avoid tables that big ?

thanks for advices
k

[Richard Williams]

I'm faced with a similar dillema, but only 10s of thousands, not millions.
I would like to have the experts opinions on JDBC performance TIA
RCW

[Dirk Schreckmann]

I've seen systems where they'd create a new table each month. This would reduce by an order of magnitude the number of records in each table.

[Avi Abrami]

Karl,
Databases divide roughly into two categories

Data warehouse

Online transaction processing (OLTP)

depending on how you intend to use the database.
In any case, the bulk of the responsibility for handling performance issues would reside with the database server -- and not with the JDBC code (in my opinion). This is because database servers are built to handle large amounts of data efficiently and quickly, therefore you should utilise that wherever possible (I believe).

Is there a general rule for the performance hit
I take if a table grows (inc. the number of rows)?

I do not know of any general rule, but there is obviously a performance hit when a table grows larger.

How to avoid tables that big?

Apart from Dirk's suggestion to break up the large table into smaller tables, many leading database vendors (including Oracle and IBM) support "partitioned" tables -- a single database table that is divided into partitions. This improves performance for very large tables. I suggest you check with your database vendor for more details regarding the availability of this option.
In any event, the performance is _always_ closely coupled to the way you use your database -- practically _all_ of the material I have read regarding database performance implies this fact. Therefore it is difficult to give general advice when it comes to performanec tuning.
So without more details, I'm afraid that's all I can offer you.
Hope it helps.
Good Luck,
Avi.