aspose file tools*
The moose likes JDBC and the fly likes how to retireve partial results from database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to retireve partial results from database" Watch "how to retireve partial results from database" New topic
Author

how to retireve partial results from database

Satyajit Bhadange
Ranch Hand

Joined: May 13, 2010
Posts: 104
hi,

I have come across one problem.

I have huge set of records in database (more than 3 lacs).

when i fire query on database it takes lot of time to retrieve those records. so my applications response time is very bad.

now i want to know that is there any way that i can fire query through jdbc and retrieve only few results (records as needed)
even in TOAD first 500 records are shown, later query is fired if user clicks next.

i want same functionality in my application ...how can i achieve same


Thanks
user101
Problems And Solutions - Algorithms
Agador Paloi
Ranch Hand

Joined: Jan 24, 2006
Posts: 118
Possibly use rownum < 500 and the_key > key_from_last_row_found .
Not sure if this is the best solution but I will throw it out there.


Agad
Satyajit Bhadange
Ranch Hand

Joined: May 13, 2010
Posts: 104
thanks for the solution...

but again rownum will be added to query and queries of my application are getting build dynamically.

there will be extra overhead to add additional where condition.
xsunil kumar
Ranch Hand

Joined: Dec 14, 2009
Posts: 133
Satyajit, even your query is building dyanamically, then also you can use row number concent in your code. Google out how to put row number for any query. Row number concept will be appended on top of your original query. I donot think there is any issue to write it.

There is some other way also , but i am not sure whether it is good to use that one or not.

If you are using result set object to store values form database. Let suppose you want to fetch 50 records in one time.

Make one counter which will tell your fetch count. For the first time, its value will be 1 and now use resultset.next method to get values. When you have taken 50 records then break the while loop.

when you do next query for next 50 results then set absolute location of resultset = 50-1 = 49, and perform above logic.

It will reduce your performance problems becuase when you write resultset.next then only it fetches the values from database.

-Sunil
Satyajit Bhadange
Ranch Hand

Joined: May 13, 2010
Posts: 104
the main problem i am facing is that rs = prepareStatement.executeQuery() is taking too long...
which i guess will be solved after adding rownum in my query...

xsunil kumar
Ranch Hand

Joined: Dec 14, 2009
Posts: 133
Yes, You are right
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to retireve partial results from database