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.
The moose likes JDBC 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


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "number of rows vs. performance ?" Watch "number of rows vs. performance ?" New topic
Author

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
k
Richard Williams
Greenhorn

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
RCW


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

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: 1121

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.
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: number of rows vs. performance ?
     
    Similar Threads
    Implementing CASCADE DELETE using Java
    Connection pool + performance
    Caching strategies
    How to map one class to multiple similar tables with Hibernate?
    Resultset with million rows