jquery Easy UI Paging

In order to solve the problem of displaying data under large data volume, we need one effect: The user gets new data from the database each time the paging toolbar is manipulated, the server determines the data segment to be fetched according to the parameters passed back, and then queries the database for the data of the specified rows. Then pass it to the foreground by the DataGrid display.

First, set the initialization of the DataGrid

[JavaScript]View Plaincopyprint?

2. $ (function () {
4. $ (' #tableTrainee '). DataGrid (' Getpager '). Pagination ({
6. Pagesize:10, //number of records displayed per page, by default
8. PageList: [Ten, Three,], //You can set a list of the number of records per page
10. Onselectpage: function (pagenumber, pageSize) {
12. Searchtrainee (); //change is triggered each time the page is changed
14. }
16. });
18. });

The Searchtrainee () method will be loaded once each time the page is updated.

Second, get the data and load

[JavaScript]View Plaincopyprint?

2. function Searchtrainee () {
4. Functionsearchtrainee () {
6. var companycode =$ (' #hiddenCompanyCode '). Val ();
8. var name = $ (' #txtName '). Val ();
10. if (companycode== "") Companycode = "000";
12. var dg =$ (' #tableTrainee ');
14. var opts =dg.datagrid (' options ');
16. var pager =dg.datagrid (' Getpager ');
18. var _pagenumber =opts.pagenumber;
20. var _pagesize =opts.pagesize;
22. //Asynchronously gets data to JavaScript object, entry parameter is query condition and page number information
24. $.post (' ajax/gettraineehandler.ashx ', {
26. Name:name,
28. Companycode:companycode,
30. Pagenumber:_pagenumber,
32. Pagesize:_pagesize
34. }, function (data) {
36. //Note that data from the database has a total column of record rows
38. var total = json.parse (data). Rows[0].total;
40. $ (' #tableTrainee '). DataGrid (' LoadData ', Json.parse (data));
42. Pager.pagination ({
44. //update pagination's navigation list parameters
46. Total:total,//Total
48. PageSize: _pagesize,//number of rows
50. PageNumber: _pagenumber//pages
52. });
54. });
56. }

The next task is to use the server to get the data for the database.

According to the above requirements, the database query statement can not use COUNT (*), select * Similar to the need for global search statements, because it is too time-consuming.

Online cattle people on the page search statements are very many posts, search methods are various, here I used the following:

[SQL]View Plaincopyprint?

1. Select Top Page size * from ID
2. Where ID not in
3. (
4. Select Top Page Size * (pages-1) ID from table ORDER by ID ASC
5. )

The advantage of this statement is that you do not have to query all rows, the disadvantage is that the larger the page number, the less efficient the query. For example, it takes about 1 minutes to get 10 rows of data after 10 million rows in a single test.

Now the main problem is basically solved, but there are still many problems:

1. When you get the total number of data bars from the server, the Select COUNT (*) statement is used, which is similar to select *, where all the data needs to be searched, causing big data to become slow.

2. Not only can you improve query efficiency from query statements, you should add indexes to your database tables if you can, even better if you can add stored procedures.

2013.12.30 Supplement

When querying the database, you can also use the following statement

[SQL]View Plaincopyprint?

2. SELECT TOP Page Size *
4. From table1
6. WHERE ID >
8. (
10. SELECT ISNULL (MAX (ID), 0)
12. from
14. (
16. SELECT TOP Page Size * (pages-1) ID from table1 ORDER by ID
18. ) A
20. )
22. ORDER by ID

By comparison, this statement is more efficient than using the not-in statement above, but still cannot avoid the fact that the page increase can lead to poor efficiency, and it still takes more than 1 minutes to get 10 rows of data after 10 million rows.

To get the total number of pages in the lower right corner of the DataGrid, it is unavoidable to use the Count statement, which will appear the short board of the query statement, helpless ~ ~

If you get the total number of rows without a condition, you can use the following statement

SELECT ROWS from sysindexes WHERE ID = object_id (' TableName ') and indid = 1

Note that it gets not the exact value, but the total number of database table rows that are updated by the server over time.

jquery Easy UI Paging