how to retireve partial results from database

[Satyajit Bhadange]

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

[Agador Paloi]

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]

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]

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]

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]

Yes, You are right