** DataGrid ** ** 基于 ** ** Access ** ** 的快速分页法 ** ** **
** 撰文 ** ** / ** ** 黎波 ** ** **
DataGrid 是一个功能非常强大的 ASP.NET Web 服务器端控件,它除了能够方便地按各种方式格式化显示表格中的数据,还可以对表格中的数据进行动态的排序、编辑和分页。使 Web 开发人员从繁琐的代码中解放。实现 DataGrid 的分页功能一直是很多初学 ASP.NET 的人感到棘手的问题,特别是自定义分页功能,实现方法多种多样,非常灵活。本文将向大家介绍一种 DataGird 控件在 Access 数据库下的快速分页法,帮助初学者掌握 DataGrid 的分页技术。
** 目前的分页方法 ** ** **
DataGrid 内建的分页方法是使用诸如“ SELECT * FROM
1<table> ”的 SQL 语句从数据库表中取出所有的记录到 DataSet 中, DataGrid 控件绑定到该 DataSet 之后,它的自动分页功能会帮你从该 DataSet 中筛选出当前分页的数据并显示出来,其他没有用的数据将被丢弃。
2
3还有一种方法是使用自定义分页功能,先将 DataGrid 的 AllowCustomPaging 属性设置为 True ,再利用 DataAdapter 的 Fill 方法将数据的筛选工作提前到填充 DataSet 时,而不是让 DataGrid 帮你筛选:
4
5public int Fill (
6
7DataSet dataSet, // 要填充的 DataSet 。
8
9int startRecord, // 从其开始的从零开始的记录号。
10
11int maxRecords, // 要检索的最大记录数。
12
13string srcTable // 用于表映射的源表的名称。
14
15);
16
17---
18
19该方法首先将来自查询处的结果填充到 DataSet 中,再将不需要显示的数据丢弃。当然,自定义分页功能需要完成的事情还不止这些,本文将在后面详细介绍。
20
21以上两种方法的工作原理都是先从数据库中取出所有的记录,然后筛选出有用的数据显示出来。可见,两种方法的效率基本上是一致的,因为它们在数据访问阶段并没有采取有效的措施来减少 Access 对磁盘的访问次数。对于小数量的记录,这种开销可能是比较小的,如果针对大量数据的分页,开销将会非常巨大,从而导致分页的速度非常的慢。换句话说,就算每个 DataGrid 分页面要显示的数据只是一个拥有几万条记录的数据库表的其中 10 条,每次 DataGrid 进行分页时还是要从该表中取出所有的记录。
22
23很多人已经意识到了这个问题,并提出了解决方法:用自定义分页,每次只从数据库中取出要显示的数据。这样,我们需要在 SQL 语句上下功夫了。由于 Access 不支持真正的存储过程,在编写分页算法上就没有 SQL Server 那么自由了。 SQL Server 可以在存储过程中利用临时表来实现高效率的分页算法,受到了广泛的采用。而对于 Access ,我们必须想办法在一条 SQL 语句内实现最高效的算法。
24
25用一条 SQL 语句取得某段数据的方法有好几种。算法不同,效率也就不同。我经过粗略的测试,发现效率最差的 SQL 语句执行时耗费的时间大概是效率最高的 SQL 语句的 3 倍!而且这个数值会随着记录总数的增加而增加。下面将介绍其中两条常用的 SQL 语句。
26
27为了方便接下来的讨论,我们先约定如下:
28
29** 变量 ** ** **
30
31|
32
33** 说明 ** ** **
34
35|
36
37** 变量 ** ** **
38
39|
40
41** 说明 ** ** **
42
43---|---|---|---
44
45@PageSize
46
47|
48
49每页显示的记录总数
50
51|
52
53@MiddleIndex
54
55|
56
57中间页的索引
58
59@PageCount
60
61|
62
63分页总数
64
65|
66
67@LastIndex
68
69|
70
71最后一页的索引
72
73@RecordCount
74
75|
76
77数据表的记录总数
78
79|
80
81@TableName
82
83|
84
85数据库表名称
86
87@PageIndex
88
89|
90
91当前页的索引
92
93|
94
95@PrimaryKey
96
97|
98
99主键字段名称
100
101@FirstIndex
102
103|
104
105第一页的索引
106
107|
108
109@QueryFields
110
111|
112
113要查询的字段集
114
115** 变量 **
116
117|
118
119** 定义 **
120
121---|---
122
123@PageCount
124
125|
126
127(int)Math.Ceiling((double)@RecordCount / @PageSize)
128
129@FirstIndex
130
131|
132
1330
134
135@LastIndex
136
137|
138
139@PageCount – 1
140
141@MiddleIndex
142
143|
144
145(int)Math.Ceiling((double)@PageCount / 2) – 1
146
147先让我们看看效率最差的 SQL 语句:
148
149SELECT TOP @PageSize * FROM @TableName
150
151WHERE @PrimaryKey NOT IN (
152
153SELECT TOP @PageSize*@PageIndex @PrimaryKey FROM @TableName
154
155ORDER BY @PrimaryKey ASC
156
157) ORDER BY @PrimaryKey ASC
158
159---
160
161这条 SQL 语句慢就慢在 NOT IN 这里,主 SELECT 语句遍历的每个 @PrimaryKey 的值都要跟子 SELECT 语句的结果集中的每一个 @PrimaryKey 的值进行比较,这样时间复杂度非常大。这里不得不提醒一下大家,平时编写 SQL 语句时应该尽量避免使用 NOT IN 语句,因为它往往会增加整个 SQL 语句的时间复杂度。
162
163另一种是使用了两个 TOP 和三个 ORDER BY 的 SQL 语句,如下所示:
164
165SELECT * FROM (
166
167SELECT TOP @PageSize * FROM (
168
169SELECT TOP @PageSize*(@PageIndex+1) * FROM @TableName
170
171ORDER BY @PrimaryKey ASC
172
173) TableA ORDER BY @PrimaryKey DESC
174
175) TableB ORDER BY @PrimaryKey ASC
176
177---
178
179这条 SQL 语句空间复杂度比较大。如果要显示的分页面刚好是最后一页,那么它的效率比直接 SELECT 出所有的记录还要低。因此,对于分页算法,我们还应该具体情况具体分析,不能一概而论。下面将简单介绍一下相关概念,如果您对主键和索引非常熟悉,可以直接跳过。
180
181** 有关主键和索引的概念 ** ** **
182
183在 ACCESS 中,一个表的主键( PRIMARY KEY ,又称主索引)必然是唯一索引( UNIQUE INDEX ),它的值是不会重复的。除此之外,索引依据索引列的值进行排序,每个索引记录包含着一个指向它所引用的数据行的指针,这对 ORDER BY 的执行非常有帮助。我们可以利用主键这两个特点来实现对某条记录的定位,从而快速地取出某个分页上要显示的记录。
184
185举个例子,假设主键字段为 INTEGER 型,在数据库表中,记录的索引已经按主键字段的值升序排好(默认情况下),那么主键字段值为“ 11 ”的记录的索引,肯定刚好在值为“ 12 ”的记录的索引前面(假设数据库表中存在主键的值为“ 12 ”的记录)。如果主键字段不具备 UNIQUE 约束,数据库表中将有可能存在两个或两个以上主键字段的值为“ 11 ”的记录,这样就无法确定这些记录之间的前后位置了。
186
187下面就让我们看看如何利用主键来进行数据的分段查询吧。
188
189** 快速分页法的原理 ** ** **
190
191其实该分页法是从其他方法衍生而来的。本人对原来的方法认真地分析,发现通过优化和改进可以非常有效地提高它的效率。原算法本身效率很高,但缺乏对具体问题的具体分析。同一个分页算法,可能在取第一页的数据时效率非常高,但是在取最后一页的数据时可能反而效率更低。
192
193经过分析,我们可以把分页算法的效率状态分为四种情况:
194
195( 1 ) @PageIndex <= @FirstIndex
196
197( 2 ) @FirstIndex < @PageIndex <= @MiddleIndex
198
199( 3 ) @MiddleIndex < @PageIndex < @LastIndex
200
201( 4 ) @PageIndex >= @LastIndex
202
203状态( 1 )和( 4 )分别表示第一页和最后一页。它们属于特殊情况,我们不必对其使用特殊算法,直接用 TOP 就可以解决了,不然会把问题复杂化,反而降低了效率。对于剩下的两种状态,如果分页总数为偶数,我们可以看作是从数据库表中删掉第一页和最后一页的记录,再把剩下的按前后位置平分为两部分,即前面的一部分,也就是状态( 2 ),后面的为另一部分,也就是状态( 3 );如果分页总数为奇数,则属于中间页面的记录归于前面的部分。这四种状态分别对应着四组 SQL 语句,每组 SQL 语句由升序和降序两条 SQL 语句组成。
204
205下面是一个数据库表,左边第一列是虚拟的,不属于该数据库表结构的一部分,它表示相应记录所在的分页索引。该表将用于接下来的 SQL 语句的举例中:
206
207** PageIndex **
208
209|
210
211** ItemId **
212
213|
214
215** ProductId **
216
217|
218
219** Price **
220
221---|---|---|---
222
2230
224
225|
226
227001
228
229|
230
2310011
232
233|
234
235$12
236
237002
238
239|
240
2410011
242
243|
244
245$13
246
2471
248
249<TD style="BORDER-RIGHT: windowtext 1pt solid; PADDING-RIGHT: 5.4pt; BORDER-TOP: #ebe9ed; PADDING-LEFT:</table>