Load data page at a time

Question

I have a data grid with pages with more than 10 thousand rows, so it is very slow when it is loaded first. What is the best way to solve this problem. I read that JDBC swapping is the usual solution for such a problem, but some people say using SQL ROWNUM is a simpler solution, so I wanted to ask first.

If you think that paging is the best solution, can you give me a couple of pointers on how to do this (link to implimentation, etc.)

Answer 1

Check out this link about page requests in Oracle.

http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html

Based on two input parameters ((1) Page number and (2) the number of results that will be displayed on the page), you can use the query to get the desired results.

Also check out the request that Don Roby indicated above. This is just as good, but I would like to point you to more detailed information about the oracle and how it is processed.

Answer 2

Paging is appropriate, and the tactics in the first answer should work for Oracle.

Answer 3

This is a very good question, and there is no single good answer for each case. I used and saw various strategies, each of which has its pros and cons.

Downloading right away is good; it's good with small tables and filtering data. When the user goes to other pages, no additional requests are sent to the database. The downside is the high cost at the beginning of the interaction and very heavy memory requirements. When this is typical, the user will not scroll through all the data, it is a waste of resources. However, for small dictionaries this is the best solution.

Paging using constraint / offset (PostgreSQL), rownum (Oracle), or any other keyword. The maximum load time for the first page. Minus - each next page loads slower, with more hard work on the database site. The best strategy is when the user usually sees one or more of the first pages. Worse, when the user will view all the data. It works well when the set is ordered by the primary key, however it is terrible when the data set is filtered and not ordered by index. For each page, it causes filtering (possibly with a full table scan) and sorting the complete data set in memory!

Scrolling with a database cursor. This is the most dangerous strategy. The database opens the cursor for the query, and when the user needs the next page, the cursor moves. The optimal strategy for the case when the user usually scrolls through all the data. Preferred strategy for reporting. Hovewer, in interactive user mode, a database connection is blocked . No one else can use it! And the number of connections to the database is limited! It is also very difficult to implement in a web application where you do not know if the user has closed the browser or is still analyzing the data - you do not know when to release the connection.

Answer 4

The easiest way is to keep track of which page you are on, and then through SQL you specify the offset and limit. In oracle, this is done by indexval and rownum, but this is not standard, and other dbms use a limit.

You can also use an independent database and use JPA on top of JDBC. The Jpa Query class supports the setFirstResult and setMaxResults functions for the same purpose. Under the hood, it will do the appropriate sql for you depending on the dbms you use.

Running select count () helps determine the number of pages.

Answer 5

You can limit your result set in 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 to reverse the swap.

one of the possible solutions is to get the identifier of the item in your list, the lowest for the previous page, the largest for the next page and using similar sqls as above

Answer 6

You will need two queries. They will vary from them:

• initial query select * from tableName, where rownum <= pagesize
• paged query select * from tableName, where indexVal> index-of-last-page and rownum <= pagesize

For the initial request, pageizing will be either a hard-coded number or a variable. For a page-by-page request, the index of the last page must be a variable that you pass into the request, and the page size can be hard-coded or a variable.

These queries suggest that you are not ordering your results. If you order a return value, then requests will vary from them:

• initial query select * from (select * from tableName order by blah), where rownum <= pagesize
• programmed query select * from (select * from tableName, where indexVal> index of the last page by blah) rownum <= pagesize

Answer 7

for a more general solution, you should use the Statement.setFetchSize method. Using this method, you can limit the number of records retrieved from the database. By default, all records are retrieved.

But you have to be careful when using this method. Because your result must be active during the life of your grid.

Answer 8

You are probably reading a recording by recording. So, for each entry there are two context switches:

• Java -> Oracle DB (send write request)
• (Oracle works)
• Oracle DB -> Java (write back)

Go and check your code and see if you can find something in the for / while loop that you can improve on.

And also this: I know someone who works a lot with Oracle. He told me that you can avoid most loops by writing smart queries. Thus, creating a good query is a significant performance improvement.