It's not a secret anymore!
The moose likes JDBC and Relational Databases and the fly likes number of rows vs. performance ? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "number of rows vs. performance ?" Watch "number of rows vs. performance ?" New topic

number of rows vs. performance ?

karl koch
Ranch Hand

Joined: May 25, 2001
Posts: 388
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
Richard Williams

Joined: Mar 05, 2003
Posts: 13
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

Is there a light at the end of the tunnel ... or is it just the front of an approaching Train !!!
Dirk Schreckmann

Joined: Dec 10, 2001
Posts: 7023
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.

[How To Ask Good Questions] [JavaRanch FAQ Wiki] [JavaRanch Radio]
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1141

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,
    I agree. Here's the link:
    subject: number of rows vs. performance ?
    jQuery in Action, 3rd edition