1.使用的数据库:
Access数据库查询练习专用数据库 -手机号码归属地Access数据库_MobileDB(10万条记录) 2.两种分页算法
(1)NBear的默认分页方法;
默认是使用了效率最差的方法:
SELECT TOP @PageSize * FROM @TableName
WHERE @PrimaryKey NOT IN (
SELECT TOP @PageSize*@PageIndex @PrimaryKey FROM @TableName
ORDER BY @PrimaryKey ASC
) ORDER BY @PrimaryKey ASC
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
(2)
黎波的DataGrid基于Access的快速分页法;
具体请查看DataGrid基于Access的快速分页法。
3.结果分析:
各变量声明:
RecordCount :总记录数;
PageSize = 10 ,分页数;
PageNo :所要查询的页码;
PageTotal = (RecordCount / PageSize + RecordCount % PageSize == 0 ? 0 : 1) :总页数;
Middle =PageTotal / 2 :中间页码;
(1)PageNo <= 1
![](https://www.cnblogs.com/images/cnblogs_com/fengmk2/pagingR1.gif)
由结果可以看到,对于PageNo=1的情形,两种算法的查询时间相同,时间复杂度都为1.
对于自定义的快速分页法来说,在第一次加载绑定时,首先需要查询记录的总条数,不过,这所消耗的时间非常少。
(2)1< PageNo<= Middle
此时,对于10万条记录的Access数据库来讲,使用NBear的默认分页查询方式,已经无法运行,我等待了几分钟都还没出结果,就取消了。
以下看到的是自定义分页查询的结果。
![](https://www.cnblogs.com/images/cnblogs_com/fengmk2/pagingR2.gif)
(3)Middle < PageNo < PageTotal
![](https://www.cnblogs.com/images/cnblogs_com/fengmk2/pagingR3.gif)
(4)PageNo >= PageTotal
![](https://www.cnblogs.com/images/cnblogs_com/fengmk2/pagingR4.gif)
4.结论
1.这个自定义分页查询算法是很快速的,
假设把所有分页面划分为前面、中间和后面三部分,则最前面和最后面的分页速度最快,最中间的分页速度最慢。并且保证每个部分的速度均匀,造成速度随页码增大而变慢。
2.目前NBear的分页方法还是不高效的,如果项目中需要用到NBear进行大量数据分页查询,推荐使用NBear自定义方式;
3.对于Access数据库,因为不可以使用存储过程,所以只能动态创建Sql脚本来实现,通常这类分页的方法是类似的,可以写一个AccessPagingHelper 类来帮助我们重用自定义快速分页方法。
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
AccessPagingHelper
using System;
using System.Data;
using System.Text;
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
/**//// <summary>
/// AccessPagingHelper 的摘要说明 ,参考了NBear源代码中AccessStatementFactory.cs
/// </summary>
public class AccessPagingHelper
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
{
public AccessPagingHelper()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
}
public static String QuickPaging(int pageSize, int pageNo, int recordCount, string tableName, string primaryKey, bool isAsc, string whereStr, params string[] includeColumns)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (includeColumns.Length == 0)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return null;
}
if (whereStr.IndexOf('=') > -1)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
whereStr = " WHERE " + whereStr;
}
else
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
whereStr = "";
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
StringBuilder sb = new StringBuilder();
sb.Append("SELECT ");
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
tableName = GetFormatValue(tableName);
primaryKey = GetFormatValue(primaryKey);
string columns = GetColumns(includeColumns);
string tempTableName1 = "tempTable1";
string tempTableName2 = "tempTable2";
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
int pageCount = GetPageCount(pageSize, recordCount);
int middlePageNo = GetMiddlePageNo(pageCount);
if (pageNo <= middlePageNo)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb.Append("TOP ").Append(pageSize + " ").Append(columns);
sb.Append(" FROM ").Append(tableName);
if (pageNo <= 1)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
}
else
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb.Append(" WHERE ").Append(primaryKey).Append(isAsc ? ">(" : "<(");
sb.Append(" SELECT ").Append(isAsc ? "MAX(" : "MIN(").Append(primaryKey).Append(") FROM (");
sb.Append(" SELECT TOP ").Append(pageSize * pageNo).Append(" ").Append(primaryKey);
sb.Append(" FROM ").Append(tableName);
sb.Append(whereStr);
sb.Append(" ORDER BY ").Append(primaryKey).Append(isAsc ? " ASC" : " DESC");
sb.Append(" ) AS ").Append(tempTableName1).Append(" )");
}
sb.Append(whereStr);
}
else
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb.Append("* FROM (");
sb.Append("SELECT TOP ");
if (pageNo < pageCount)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb.Append(pageSize).Append(" ").Append(columns);
sb.Append(" FROM ").Append(tableName);
sb.Append(" WHERE ").Append(primaryKey).Append(!isAsc ? ">(" : "<(");
sb.Append(" SELECT ").Append(!isAsc ? "MAX(" : "MIN(").Append(primaryKey).Append(") FROM (");
sb.Append(" SELECT TOP ").Append(recordCount - pageSize * pageNo).Append(" ").Append(primaryKey);
sb.Append(" FROM ").Append(tableName);
sb.Append(whereStr);
sb.Append(" ORDER BY ").Append(primaryKey).Append(!isAsc ? " ASC" : " DESC");
sb.Append(" ) ").Append(tempTableName1).Append(")");
}
else
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb.Append(recordCount - pageSize * (pageCount - 1)).Append(" ").Append(columns);
sb.Append(" FROM ").Append(tableName);
}
sb.Append(whereStr);
sb.Append(" ORDER BY ").Append(primaryKey).Append(!isAsc ? " ASC" : " DESC");
sb.Append(") AS ").Append(tempTableName2);
}
sb.Append(" ORDER BY ").Append(primaryKey).Append(isAsc ? " ASC" : " DESC");
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
return sb.ToString();
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static string GetFormatValue(string value)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (value[0] == '[')
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return value;
}
else
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return "[" + value + "]";
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static string GetColumns(params string[] includeColumns)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
StringBuilder sb = new StringBuilder();
int i = 1;
foreach (string item in includeColumns)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
if (item[0] == '*')
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return null;
}
else if (item[0] == '[')
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb.Append(item);
}
else
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb.Append('[');
sb.Append(item);
sb.Append(']');
}
if (i != includeColumns.Length)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
sb.Append(',');
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
i++;
}
return sb.ToString();
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static int GetPageCount(int pageSize, int recordCount)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return recordCount / pageSize + (recordCount % pageSize == 0 ? 0 : 1);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
public static int GetMiddlePageNo(int pageCount)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
return pageCount / 2;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5.附
我的测试源文件