I have about 10000 records in a database table(oracle is the DB here). Its number may vary frequently depends on the updates/inserts to that database table. I have to show this table records to the user.I used paging to display records and user can walk through all pages by clicking on previous/Next links . Each page can display 100 number of records at a time. For each click do we need to go to database to fetch the records?Or is there any effcient way to handle this situtaion. I have to provide updated data and best performance to the user.Could you explain it in J2EE perspective with available technologies?Which is the best available j2ee technology to do this?
You should try to fetch only from and to the needed records at each page (only 100 records each time); it's easy to calculate given the number of items showed at each page and the page number you're viewing at the moment.
Suppose if I retrieved 100 records.But i have to give sorting facility(asc and dec order) also for some columns. So whenever user tries to sort, it needs to go to database and retrieve the records as per the requested sorting order using sql order by clause.In this case if the number of records is very large, I think it will impact the performance.Is it the right approach in this scenario?
First thing is that you should use order by clause if you are getting the no of record higher then expected
Second you need to think from user perspective that is, he is looking at 100 record only and he is trying to sort out that 100 record and not record which are not display.
You dont want to display something unexpected and dissatisfy him.
There is always a question whether you need to sort the displayed result or you need to sort entire collection of record.
There is no best answer to your question. I guess this question goes to your architect, he might be able to answer your question. After all it is all based on business you are involve and what requirement you need to met