aspose file tools*
The moose likes JDBC and the fly likes .getRow is slow for large ResultSet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark ".getRow is slow for large ResultSet" Watch ".getRow is slow for large ResultSet" New topic
Author

.getRow is slow for large ResultSet

Bryce Martin
Ranch Hand

Joined: Nov 19, 2007
Posts: 269
Does anybody know why .geRow is so slow for large ResultSets. I return a result set in my servlet. I do ResultSet.last() then ResultSet.getRow() to get how many rows were returned. The larger the result set the longer it takes getRow() to return a value...This doesn't make any sense to me. Does anybody know where I can look at the code for that method? Maybe that will explain what the heck is going on...
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18985
    
    8

Of course it does. When you say "Find the last row", the driver must read in all the rows and store them in memory. Naturally it takes ten times as long to load 1000 rows as it does to load 100 rows.

But you should really stop wanting to know how many rows there are going to be. Just read them yourself, which you were going to do anyway, and when you have read them all you will then know how many rows there were.
Bryce Martin
Ranch Hand

Joined: Nov 19, 2007
Posts: 269
I don't want to actually read all the rows. I just need to know how many there are so that I can calculate the number of pages I will have, and then display them in selected sets of 10, 20, 30, or whatever I allow the user to choose. I'm currently looking into using row_number() so that I only return the results that I need for a given page. That way I'm only getting result sets of 10, or 20 etc rows of data.
Jeff Rummings
Ranch Hand

Joined: Dec 20, 2007
Posts: 42
I don't want to actually read all the rows. I just need to know how many there are


If you don't need the data, you could do a select count:

Ilja Preuss
author
Sheriff

Joined: Jul 11, 2001
Posts: 14112
Originally posted by Arman Sharif:


If you don't need the data, you could do a select count:



That will get you the number of rows with a widget_id != null. If you really want the total number of rows, you should use



That will also execute even faster.

Of course you can also add a WHERE clause to that statement.


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
Bryce Martin
Ranch Hand

Joined: Nov 19, 2007
Posts: 269
Well I really want to add a Count() to it...I just have to figure that part out. I have an enormous statement...and I just need to figure out if I can do it.

I'll report back if I have problems...
 
Consider Paul's rocket mass heater.
 
subject: .getRow is slow for large ResultSet