*
The moose likes JDBC and the fly likes Split ResultSet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Split ResultSet" Watch "Split ResultSet" New topic
Author

Split ResultSet

Ashwin Sridhar
Ranch Hand

Joined: Jul 09, 2011
Posts: 272

Hi,

Suppose I am populating my front-end with 2000 records, the user may not see all 2000 records immediately. So I prefer to populate my front-end in batches, say 1000 records. So I am trying to split my resultset into possibly more chunks and display them in a paged manner. I tried using CachedResultSet without much success. Do we have any way of splitting the resultset.


Ashwin Sridhar
SCJP | SCWCD | OCA
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29287
    
140

Paging doesn't usually split the resultset. It usually controls the query itself to only return X records. For example, in Oracle, we use rown um to control which rows are returned.


[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
Ashwin Sridhar
Ranch Hand

Joined: Jul 09, 2011
Posts: 272

Hi Jeanne ,

Using such kind of rownums would make me hit the DB more number of times. I would prefer storing the data on the server and access them. By the time all these data are viewed, i would fetch more data and keep them ready. So it reduces the waiting time drastically.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 29287
    
140

At the cost of the first hit. The user may not visit all 2000 hits, but had to wait for you to retrieve them up front.
Ashwin Sridhar
Ranch Hand

Joined: Jul 09, 2011
Posts: 272

Hi Jeanne ,

That is exactly what am trying to figure out. A way to reduce that wait time. My Query would return 2000 records. But as soon as i reach say 500 records, i would send them back to my grid while the fetching of remaining 1500 records happens simultaneously.

I am not sure if such a concept is available. I am trying to brainstorm various possiblities.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3454
    
  47

Hmmm, it is usually not done this way. Imagine Google caching full results of all queries of all people that do a search.....

When done right (proper indexes in place and proper pagination technique for your database), pagination is quick. If the query itself was inherently costly (this probably means that the design was not done right, though), I'd think of materializing the search results in a temporary table in a database, and then paginating records from that table. Having it in the database scales better in my opinion, as you don't risk to blow up your server memory.

Under very specific condition (hard upper bound on the number of concurrent requests and on the size of every possible request) something you envisage might be possible. Just might be. However, if the users won't always browse complete results, your only achievement could easily be several times inflated network and database load for no real gain whatsoever.

Remember: More computing sins are committed in the name of efficiency (without necessarily achieving it) than for any other single reason...including blind stupidity. — W.A. Wulf (shamelessly stolen from Winston's signature).
Ashwin Sridhar
Ranch Hand

Joined: Jul 09, 2011
Posts: 272

Hi Martin,

Thanks for your response. I fail understand how the performance would improve by storing in a temporary table. In such a scenerio, i might have to hit the DB multiple times. I prefer storing data on my server in smaller chunks. So i will make a single DBhit. Any suggestion on this.
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3454
    
  47

That remark of mine was introduced by "If the query itself was inherently costly (this probably means that the design was not done right, though), ..." (The query itself means the query whose result you want to paginate.)

So if your query does not suffer performance problems, you would not use this technique. If your query suffers the performance problem, this would allow the query to be executed only once and the pagination to go against the stored (precomputed) result of the query. If your only concern is reducing the number of roundtrips to the database, this does not apply to your situation, but it was not crystal clear from the information you've provided so far (at least to me).

My suggestion is: don't do this. Use standard pagination technique as (nearly) everybody else does. If there are performance problems with this approach, investigate where exactly the problems are and address these problems specifically. Again, try entering a query in Google and browse a few pages. Is the response reasonable? Google certainly doesn't cache the results of your search somewhere in a middle tier.
Wendy Gibbons
Bartender

Joined: Oct 21, 2008
Posts: 1106

Ashwain, you CAN NOT return the first 500 results from a query, and then carry on with the same query. The DATABASE carries on till the end, and returns everything.

Jeane is helping you find other ways of doing it.

If you want to return the data to the user in pages, you are going to have to do 4 selects (in the example of 2000 rows displayed in pages of 500).

You could load all 2000 rows into memory, but then as has been said the user will have to wait, which you have said you don't want.
So please read jeane responses again, remembering these facts.

Bother Martin is a faster typer than me
Robert Aryan
Greenhorn

Joined: May 08, 2009
Posts: 21
You can use lambdaj.It is like list query in which you can manipulate the result very easly.

http://code.google.com/p/lambdaj/
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Split ResultSet
 
Similar Threads
To highlight multiple selected values in listbox using struts
paging
resultset
performance
Problem with sun.jdbc.rowset.CachedRowSet