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)
Paging is appropriate, and the tactic in the first answer to this question should work for Oracle.
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.
Check this link about pagination Queries in Oracle.
http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html
Based on the two input parameters ( (1) Page number and (2) number of results to be displayed per page), you can use the query to get the results you need.
Also check the query that Don Roby pointed to above. It is just as good, but I wanted to point you to more oracle specific information and how it is processed.
The easiest is to keep track of which page you are on and then through SQL you specify the offset and limit. On oracle this is done by indexval and rownum but this is not standard and other dbms use limit instead.
You can also go database independent and use JPA on top of JDBC. The Jpa Query class supports setFirstResult and setMaxResults functions for the same purpose. Underneath the hood it will do the respective sql for you depending on the dbms you are using.
Doing select count() helps u determine the number of pages.
You can limit your resultset within your sql query with the limit keyword.
Select * from TableName where id>... order by fieldName asc limit 50;
Select * from TableName where id<... order by fieldName desc limit 50;
You need to add some logic for backward - forward paging.
one possible solution is to get id of the element in your list, lowest for previous page, largest for next page, and use similar sqls like above
You will need two queries. They will be a variation of these:
For the initial query, pagesize will either be a hard coded number or a variable. For the paged query, index-of-last-page must be a variable that you pass into the query and pagesize can be hard coded or a variable.
These queries assume that you are not ordering your results. If you are ordering the return value then the queries will be a variation of these:
for more generic solution, you should use Statement.setFetchSize method. By means of this method, you can limit the number of records fetched from database. By default all records are being fetched.
But you should be cautious while using this method. Because your resultset should be active during lifespan of your grid.
You are probably reading record by record. So, for each record there are two context switches:
Go and check out your code and see if you can find something in a for/while-loop, which you can improve.
And also this: I know someone who works a lot with Oracle. He told me that you can avoid most of the loops by writing intelligent Queries. So, writing a good query is a huge performance improvement.
