DataGrid connection ACCESS fast paging method (1) - demand and status quo
First, demand analysis
DataGrid is a powerful ASP.NET web server-side control, in addition to formatting display data in a variety of ways, you can perform dynamic sorting, editing, and paging data. Greatly alleviate the workload of the majority of web programmers. Paging functionality to implement DataGrid has always been a multi-entry problem, especially custom paging functions, and has a variety of ways to achieve, very flexible.
At present, everyone is recognized as the best performance of SQL Sever combines the stored procedure solution. Because the cursor can be used in the stored procedure of SQL Server to traverse all rows in the database table, you can quickly locate a row in the database table in combination with a counter variable. However, there is no better solution in the ASP.NET application using the Access database.
We know that in the ASP, you can use ADO cursors to quickly locate the data of the current page in the database table. However, there is no cursor in ADO.NET, so the traditional DataGrid paging method is to remove all records from the database table with SQL statements such as "select * from item", and then the DataGrid's automatic paging feature will help you display the corresponding paging. The data.
Second, the current solution
Many people have realized the problems described above and proposed a solution, that is, use custom paging, and take out the data to be displayed each time. So, how do you take it? I know about 5 kinds of answers. Different algorithms will be different efficient. After my rough test, the slowest algorithm is time consuming is the fastest 3 times! And this number increases as the total number of records increases.
In order to facilitate the next discussion, before the SQL statement, let us do the following conventions:
PageIndex
ItemID
ProductID
PRICE
0
001
0011
$ 12
002
0011
$ 13
003
0011
$ 12
1
004
0012
$ 13
005
0012
$ 11
006
0012
$ 14
2
007
0013
$ 14
008
0013
$ 12
009
0014
$ 13
3
010
0011
$ 13
011
0012
$ 15
012
0014
$ 16
4
013
0013
$ 12
014
0013
$ 13
variable
use
@PageSize
Total number of records shown per page
@PageCount
Total number of paging
@Recordcount
Total number of records of data sheets
@PageIndex
Current page index
@FirstIndex
The index of the first page
@MIDDleIndex
Index of the middle page
@LastIndex
The index of the last page
@TableName
Database table name
@PrimaryKey
Motor key field name
@Queryfields
To query the field set
@Condition
Screening condition
definition:
@PageCount
=
int
)
Math
.
Ceiling
(("
Double
)
@Recordcount
/
@PageSize
)
@FirstIndex
=
0
@LastIndex
=
@PageCount
-
1
@MIDDleIndex
=
int
)
Math.
Ceiling
(("
Double
)
@PageCount
/
2
)
1
Preset:
@PageSize
=
2
@ Recordcount =
9
@PageCount
=
4
Let's take a look at the slowest SQL statement:
Select Top @PageSize
*
From @tablename as a
Where @primarykey not in
(
Select Top @PageSize
*
@PageIndex @PrimaryKey from @tablename as b
Order by @PrimaryKey
)
Order by @PrimaryKey
This statement is slow in NOT
In
Here, the main SELECT statement is traversed.
@PrimaryKey
Every value must be the result of the result of the SELECT statement
@PrimaryKey
The value is compared, which is very complex. In fact, we should try to avoid NOT when we usually write SQL statements.
In
Statements because it tends to increase the time complexity of the entire SQL statement.
Another is the use of two TOP SQL statements as follows:
SELECT
*
From
(
Select Top @PageSize
*
From
(
Select Top @PageSize
*
@PageIndex
1
) *
From @tablename
Order by @PrimaryKey
TABLEA
Order by @primaryKey DESC
Tableb
Order by @PrimaryKey
This SQL statement is relatively large. If the pages you want to display are just the last page, its efficiency is lower than the records of direct SELECT.
The next article will introduce a quick algorithm, and use different deformations to effectively improve query efficiency.
Author: Tripoli
