aspose file tools*
The moose likes JDBC and the fly likes How to handle larg ResultSet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "How to handle larg ResultSet" Watch "How to handle larg ResultSet" New topic
Author

How to handle larg ResultSet

Hanna Habashy
Ranch Hand

Joined: Aug 20, 2003
Posts: 532
I am a developing an application where the user can type any query and the query will be executed on a selected database.

How can I handle a larg ResultSet? 10 millin + rows.

I implemented pagination, so I don't transfer all of the rows, but the a select query takes 30 min to execute.

I used Statement.setFetchSize() but it didn't help?

Any suggestions?

Thanks


SCJD 1.4<br />SCJP 1.4<br />-----------------------------------<br />"With regard to excellence, it is not enough to know, but we must try to have and use it.<br />" Aristotle
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

Some databases support the LIMIT command to accomplish this such as "SELECT * FROM `your_table` LIMIT 5, 10" to select records 6-10.


My Blog: Down Home Country Coding with Scott Selikoff
Amit M Tank
Ranch Hand

Joined: Mar 28, 2004
Posts: 257
If you are using SQL server you can use



If you are using Oracle you can use rownum



hope this helps


Amit Tank
Linked In
Hanna Habashy
Ranch Hand

Joined: Aug 20, 2003
Posts: 532
Thanks guys:
The problem is I don't know what kind of query is being executed. It is an ad-hoc query application. The user will inter any type of query (update, select, delete, storProc, etc). One user can inter a good query, and another can enter a bad one.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18651
    
    8

Have you tried using the setMaxRows() method on your Statement object? Pick a reasonable number and apply it to all queries that the users input. No user is going to be able to look at 10 million rows anyway, so limiting them to (say) 10 thousand rows shouldn't be a problem.

I don't know if that causes the database to only select 10 thousand rows, but I would try it to see what happens with your database.
Hanna Habashy
Ranch Hand

Joined: Aug 20, 2003
Posts: 532
Originally posted by Paul Clapham:
Have you tried using the setMaxRows() method on your Statement object? Pick a reasonable number and apply it to all queries that the users input. No user is going to be able to look at 10 million rows anyway, so limiting them to (say) 10 thousand rows shouldn't be a problem.

I don't know if that causes the database to only select 10 thousand rows, but I would try it to see what happens with your database.


The requirments states that the user should be able to look into all the returned result. I tried setMaxSize() and setFetchSize() but it didn't help.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18651
    
    8

Originally posted by Hanna Habashy:
The requirments states that the user should be able to look into all the returned result.
Well, those are just bad requirements if the results can return 10 million rows. You could do the arithmetic: if you display the result in pages of 50 rows, then there would be 200,000 pages for the user to look at. If they clicked on "Next Page" once per second, it would take over two days (without bathroom breaks) to look at all the rows. The requirement should be changed as it provides no benefit and considerable cost.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

Originally posted by Hanna Habashy:
The requirments states that the user should be able to look into all the returned result.


Why is adding a 'next page' button a violation of this? There's no way the requirement means to download 100% of the records to the client, as this would make a completely unusable system. Imagine if google's search engine caused you to download 10 million records at a time. The system would be useless.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18651
    
    8

Originally posted by Scott Selikoff:
Why is adding a 'next page' button a violation of this?
I don't think it is a violation, and I don't think Hanna is saying it is. Hanna is just asking, as far as I can tell, how to prevent the database and/or database driver from returning the whole result of the query all at once.

Apparently setFetchSize and setMaxRows don't work, we are told. This makes me suspect that the result sets aren't forward-only result sets, or that something else has been missed.

But personally I would just implement a reasonable limit for the number of records returned, one which nobody would ever reach by clicking the "next page" button. That's what Google did.
Hanna Habashy
Ranch Hand

Joined: Aug 20, 2003
Posts: 532
Thank you all very much.

My problem is that if a user attempts to execute query like this (select * from tableName) if the table has few million rows, it will take long time to be executed.

If I set setMaxRows(10000), it will be faster, however the user cannot see data after 1000 rows.

By default, the application ecapsualte and return 40 rows at a time (pagination).

I close the connection every time a use get the next page(I don't know if this is the best approach or not). Because of that, the same query has to be execuated every single time the user ask for another page.

Important note: This application is very generic, and permit the user to access many databases, so using a connection pool is not an option. I open a direct connection to the specified database to execute the query on.
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3710
    
    5

The only true way to avoid the issue that is database independent would be to put the logic into the query to filter out results. Depending on the database schema, there are ways of adding clauses to shrink the query down, although it can lead to messy queries.

Also, have you tried adding sorted indexes to speed up the query? If your are returning a million records in sorted order and are doing so without a tree index, its going to take awhile no matter how many results you return.
[ May 12, 2008: Message edited by: Scott Selikoff ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: How to handle larg ResultSet