This week's giveaway is in the EJB and other Java EE Technologies forum. We're giving away four copies of EJB 3 in Action and have Debu Panda, Reza Rahman, Ryan Cuprak, and Michael Remijan on-line! See this thread for details.
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 ?
Karl, Databases divide roughly into two categories
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.