Load a page of data at a time

Question

I've got a data grid with pages with more than 10k rows so its very slow when it first loads. whats the best way of solving this problem. I've read that JDBC paging is the usual solution for such problem but some people are saying to use SQL ROWNUM is an easier solution so I wanted to ask first.

If you think paging is the best solution could you please give me a couple of pointers on how to go on about it(link to implimentation etc)

Answer 1

Paging is appropriate, and the tactic in the first answer to this question should work for Oracle.

Answer 2

It's a very good question, and there is no single good answer for each case. I've used and seen various strategies, each of them has its pros and contras.

Loading at once - well, this one is good with small tables, and when data is filtered. When user navigates to other pages, no additional queries are sent to database. The minus - heavy cost at begin of interaction, and very heavy memory requirements. When the typicas is, the user will not scroll the whole data, it's the waste of resources. However, for small dictionaries, it's propably the best solution.

Paging using limit/offset (PostgreSQL), rownum (Oracle) or whatever the keyword is. The plus - high load time for first page. The minus - each next page is loaded slowlier, with heavier work on database site. The best strategy when user will typically see one or a few first pages. The worst, when the user will scroll through all data. It works quite good when the set is ordered by primary key, however it's terrible when the data set is filtered and ordered not by index. For each page it invokes filtering (propably with full table scan) and sorting full data set in memory!

Scrolling using database cursor. This is the most dangerous strategy. The database opens the cursor for query and when user requires next page, cursor is mooved. The optimal strategy for case, when user typically scrolls through all the data. The preferred strategy for reports. Hovewer, in user interactive mode it requires the database connection to be locked for the time of interaction. No one other can use it! And the number of connections to database is limited! It is also very hard to implement in web application, where you don't know if user closed the browser or is still analysing the data - you don't know when to release the connection.