The paging algorithm is an issue that Web developers are very concerned about. Almost every database-related application involves the paging algorithm. Many people have written documents in this area, and it seems that there is nothing to discuss; but in fact, let’s change it. By changing the data representation, a better paging algorithm can be used. Let's see if we can do better now.
Let’s first talk about some existing methods.
One is paging through DataGrid. This method is the simplest, but it is not efficient and requires reading all the data in front of the required data.
The second is to fill the paging method of DataSet by specifying the starting record and record number DbDataAdapter.Fill. This method is also simple, but it is also not efficient and requires reading all the data in front of the required data.
The third is to select the required records from the middle of the table through multiple select top and multiple sorting; in order to prevent the data on adjacent pages from being repeated, it is necessary to use not in, which will cause the tail data of the table with a large amount of data to be selected. Database performance will be greatly reduced.
Suppose we change the presentation form of the table, taking the Grid presentation with scroll bars in the traditional C/S application as an example; in fact, this method is the most suitable presentation method for database tables, and the commonly used presentation method in Web applications The page number connection method of 1,2,3... or the page number browsing bar method of the previous page and next page buttons are a last resort, because simple technology cannot be used to implement scroll bars in Web applications. Grid.
The tables in the database all have primary keys to distinguish different records in the tables; the data in the Grid on the user interface also logically has primary keys, otherwise the data will be ambiguous. However, in most applications, there is no setting. It is impossible to know the primary key of the read data; even if a few applications have set it up, they know the primary key of the read data, but they do not apply it to paging; in fact, as long as you know the primary key of the read data, you can perform paging very easily .
The algorithm for the homepage is very simple
select top page size * from table name order by primary key
For tables with scroll bars, the data scrolls sequentially page by page. Even if you drag the scroll bar, you can scroll page by page to the selected position. When dragging to a The algorithm for new pages is
select top page size * from table name where primary key > primary key recorded at the end of the previous page order by primary key
If caching is used, all data only needs to be downloaded once, and new data will be downloaded only when scrolling to the end.
This algorithm requires knowing the primary key of the data in the Grid and applying the primary key data to paging; for multiple primary keys and sorted tables, the algorithm is the same, but the statements are more complicated. Not only can you start from the home page, but you can also start from the last page and scroll forward.
There is no problem with the performance of this algorithm. No matter how big the table is, the record at which position is selected is the same. It is more suitable to use the paging selection method of home page, previous page, next page and last page, and is more suitable for scroll bars. Grid; not suitable for pagination of specified page numbers.
For a Grid with scroll bars using this algorithm, please refer to our demonstration www.BizStruct.cn .
When raising questions, please consider two points first, otherwise you may not realize the advantages of combining this algorithm with our system:
First: Which one is more convenient, the traditional C/S application form or the paging form on the Web?
First, how different is the table with scroll bars we implemented from the table in traditional C/S applications.
Explanation of reply:
Some replies stated that operations such as "jump to page xx" cannot be implemented.
But let's think about it, in a C/S application environment, if anyone uses this method of page jump, everyone will definitely find it weird.
The Grid with scroll bars we implemented is almost as fast as the previous C/S application in a LAN environment, and it is also very fast in a WAN environment.
The "jump to XX page operation" of Web applications is actually a last resort. If the Grid of traditional C/S applications can be implemented, why should we use this?