I want a solution for the following
Iam retriving data from a table and displaying the same in the JSP (using grid). There are nearly 500 regards and Iam dispalying 100 on first retrevial. If I click on next, it will hit Database and retrive next 100 records and so on. The same is applicable when I click on previous button.
To know how many records are there, Iam first writing a count query. this is used to dislay on the screen like " 100 of 505 records".
Then I will write the query to display the coloumns which get 100 records.
In summary Iam hitting DB 2 times ( First to get total count and second to get 100 records). This is hitting the performance.
Can't I write one query which counts the total records as well as get the first 100 rows whcih colown values ?
Pls let me know how to achieve this ??
B S Reddy
Which DB are you using?. Is it Oracle or sql server or MySQL?
It is possible to handle these things efficiently in DB side rather than bringing it to app layer.
Iam using Oracle as DB
Why dont you include a count field to the same query being executed?
select count(PK),* from MY_TBL
And then you dont have to worry about the first hit to obtain count;)
The trade-in is yours;)
By the way, how can you be certain that the hit count is consistent between queries? For example, at the first hit for the first 100 records, there is a total of 505 records. Meanwhile the user has the record on his screen, some records may dissapear, and others may be created.
The user clicks next, but the first 100 records are no longer 100% the same, lets say record #100 has dissapeared so the record that previously was #101 now is #100. This will have as a result that record will never be shown to the user even though it has existed all the time and the user does nothing else than clicking next.
I'm just trying to illustrate that hitting the db multiple times like this has its drawbacks. They may not be relevant in your case, but watch out for this!