jQuery in Action, 2nd edition*
The moose likes Performance and the fly likes Performace of accessing db recs Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Java » Performance
Bookmark "Performace of accessing db recs" Watch "Performace of accessing db recs" New topic
Author

Performace of accessing db recs

kri shan
Ranch Hand

Joined: Apr 08, 2004
Posts: 1372
I have billions of records on the data base side. In the client side i will access these records and show it thru pagination. Which option is better
a). get all db records and store it in the xml file, do some xml processsing and show those recs
b). thru RowSet / CachedRowSet
c). any other optimised way...
David Harkness
Ranch Hand

Joined: Aug 07, 2003
Posts: 1646
Given that a and b aren't even options, I'm going with c: "any other optimized way."
kri shan
Ranch Hand

Joined: Apr 08, 2004
Posts: 1372
this is not the responsible answer,if you don't know don't reply...
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


a). get all db records and store it in the xml file, do some xml processsing and show those recs
b). thru RowSet / CachedRowSet
c). any other optimised way...

David's answer might be a little flippant (though I don't see it as such) but it is accurate. Criticising or demanding better answers from people who have been kind enough to offer an opinion will not win you many friends here or on any forum site. That being said:
  • a) a silly idea. What does it gain you, moving a bunch of data from one persistant store to another? What is will mean is a huge degredation in application performance as you move the data from one place to another. And this XML file will always be out of date with the DB state. And where did you intend storing this file?
  • b) A disconnected RowSet/CachedRowSet with billions of records is another silly idea. Imagine the size of that object. You'll quickly hit OutOfMemoryExceptions.
  • c) so this really is your only option.


  • [ February 24, 2005: Message edited by: Paul Sturrock ]

    JavaRanch FAQ HowToAskQuestionsOnJavaRanch
    Jeanne Boyarsky
    internet detective
    Marshal

    Joined: May 26, 2003
    Posts: 30356
        
    150

    Originally posted by kri shan:
    I have billions of records on the data base side. In the client side i will access these records and show it thru pagination.


    Kri,
    As written, this is impossible to do in an efficient way. The network transfer of a billion records would take too long.

    You can take advantage of the fact that nobody is paging through all billion records. On the first query, just get the records needed for the first page. Many users will stop at this point. If they click "next", you can get more records, but you still don't need a billion.


    [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
    Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
    David Harkness
    Ranch Hand

    Joined: Aug 07, 2003
    Posts: 1646
    While I didn't provide the answer you wanted, I gave you the information that I had on hand at 3 am. The first two options will not work for the reasons the others gave. With billions of records, the following may still suffer performance problems, but with the right indexes it should be sufficient.

    This thread from Ask Tom discusses in great detail the solution, but here's the summary.

    You need to create a query that contains two nested queries. The innermost query performs the real query using whatever where conditions the user has chosen and sorts. The middle query selects from that query and adds a rownum column with a single where condition: where rownum < <max>. Finally, the outermost query selects from the above and applies where rownum >= <min>.Let me know if you have questions from this or after reading that thread.
    [ February 24, 2005: Message edited by: David Harkness ]
    Daniil Sosonkin
    Ranch Hand

    Joined: Jan 15, 2004
    Posts: 76
    I have a similar situation although it is not with billions of records (that sounds like an overestimate to me anyway). But, assuming its a large amount of records nonetheless. My database is mySQL and I do need to show thousands of lines of information in page form. The only solution I've found that doesn't kill server and database is a two step process. One, cound total number of rows in the database:



    Store this number in a session. Then, get the current viewable page, calculate the limits based on items per page. If its page one then start record will be 0 and end record will be number of items. Then, retreive this from database:



    After that you simply want to loop through the ResultSet. To optimize (and since data doesn't change much), I cache in memory, last 3 pages. But thats because of my specific requirements.

    As everyone says, use the best available optimizations you have.
    kri shan
    Ranch Hand

    Joined: Apr 08, 2004
    Posts: 1372
    Can i use RowSet / CachedRowSet for this purpose?
    Ilja Preuss
    author
    Sheriff

    Joined: Jul 11, 2001
    Posts: 14112
    Originally posted by kri shan:
    Can i use RowSet / CachedRowSet for this purpose?


    According to Paul's answer above not, no.


    The soul is dyed the color of its thoughts. Think only on those things that are in line with your principles and can bear the light of day. The content of your character is your choice. Day by day, what you do is who you become. Your integrity is your destiny - it is the light that guides your way. - Heraclitus
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: Performace of accessing db recs