Implement Paging for Data Display

Almost every application need the feature of paging, whether it's web application or desktop application. Whenever you want to display some data to the user, there is no way that you display the entire set of the data to the user. Imagine that you have 1 million data on the DB, it would be inefficient either time nor resources. Especially if the application is a public website, the server might crash, if everyone is viewing the page at the same time.

If it's a desktop application, it will consume a lot of memory to display 1 million data in the table (or would you say, JTable), and then the application might crash due to out of memory exception.

For this reason, everyone should implement paging to all kind of data display. PHP MyAdmin is a good example for this. If the application is a desktop application, a proper calculation has to be done to determine how many rows can be display on the screen, so only the displayed data will be retrieved. And whenever the user is doing scrolling at the table, another set of data will be retrieved based on the calculation of which rows the user is displaying now if he was scrolling up/down for how many times.

For the retrieval, below is the query syntax comparison for MySQL and PostgreSQL. First query is the retrieval of 1st - 10th row, second query is for 11th - 20th row.

1. MySQL

SELECT select_list FROM table_expression LIMIT 0, 10

SELECT select_list FROM table_expression LIMIT 10, 10

2. PostgreSQL

SELECT select_list FROM table_expression LIMIT 10 OFFSET 0

SELECT select_list FROM table_expression LIMIT 10 OFFSET 10

Unfortunately, MSSQL and Oracle don't have the "offset" feature, and thus implementing such kind of paging will need ugly subquery that will take a long time to execute, or helper "view" that will slower the performance when inserting data. So in this case, it's to be considered whether the efficiency of data displaying is very important to the application or not.