分页代码及相应SQL效率的分析
前阵子一直在研究一个老问题，就是分页效率问题。虽然网上代码不计其数，但是大部分都是雷同的，不尽如人意。在此我把这几天的研究成果给大家分享下，希望对各位程序开发人员有所帮助。（注：这里没有研究分页缓存机制的问题，只是从优化SQL语句考虑）。
在数据访问层中：
代码：
private const string GET_SINGLE_PAGE_MESSAGE_LIST_FOR_SQL_TOP_IN = "SELECT TOP {0} * FROM (SELECT TOP {1} Id,Title,AddUser,CreateTime FROM [Messages] WHERE Id NOT IN (SELECT TOP {2} Id FROM [Messages])) as [Msg]";
/// <summary>
/// Get Single Page Message List,SQL的TOP,Not In方式
/// </summary>
/// <param name="pgpo">PaginationGoPageObject</param>
/// <returns>IList</returns>
public static IList GetSinglePageMessageListForSqlTopIn(PaginationGoPageObject pgpo)
{
DataBaseInstance dbInstance = new DataBaseInstance();
dbInstance.Open(1);
//不能通过参数的方式来实现
string GET_SINGLE_PAGE_MESSAGE_LIST_FOR_SQL_TOP_IN_str = string.Format(GET_SINGLE_PAGE_MESSAGE_LIST_FOR_SQL_TOP_IN, pgpo.PageSize, pgpo.PageSize * pgpo.CurrentPage, pgpo.PageSize * (pgpo.CurrentPage - 1));
dbInstance.SqlCommandText = GET_SINGLE_PAGE_MESSAGE_LIST_FOR_SQL_TOP_IN_str;
IList MsgList = dbInstance.ExecuteList();
dbInstance.Close();
IList messageDataObjects;
if (MsgList == null || MsgList.Count == 0)
{
messageDataObjects = null;
}
else
{
messageDataObjects = new ArrayList(MsgList.Count);
foreach (IDictionary dicMessageDO in MsgList)
{
MessageDataObject messageDO = new MessageDataObject();
messageDO.Id = Convert.ToInt32(dicMessageDO["ID"]);
messageDO.Title = dicMessageDO["TITLE"].ToString();
messageDO.AddUser = dicMessageDO["ADDUSER"].ToString();
messageDO.CreateTime = (DateTime)dicMessageDO["CREATETIME"];
//messageDO.SortId = (int)dicMessageDO["SORTID"];
//messageDO.Message = dicMessageDO["MESSAGE"].ToString();
//messageDO.Reply = dicMessageDO["REPLY"].ToString();
//messageDO.Image = dicMessageDO["IMAGE"] as byte[];
messageDataObjects.Add(messageDO);
}
}
return messageDataObjects;
}
private const string GET_SINGLE_PAGE_MESSAGE_LIST_FOR_SQL_TOP_MAX = "SELECT TOP {0} Id,Title,AddUser,CreateTime FROM [Messages] WHERE (Id > (SELECT MAX(Id) FROM (SELECT TOP {1} Id FROM [Messages] ORDER BY Id) AS [Msg_temp]))";
/// <summary>
/// Get Single Page Message List,SQL的TOP,MAX方式
/// </summary>
/// <param name="pgpo">PaginationGoPageObject</param>
/// <returns>IList</returns>
public static IList GetSinglePageMessageListForSqlTopMax(PaginationGoPageObject pgpo)
{
DataBaseInstance dbInstance = new DataBaseInstance();
dbInstance.Open(1);
//不能通过参数的方式来实现
string GET_SINGLE_PAGE_MESSAGE_LIST_FOR_SQL_TOP_MAX_str = null;
if (pgpo.CurrentPage == 1)//判断是否为首页
{
GET_SINGLE_PAGE_MESSAGE_LIST_FOR_SQL_TOP_MAX_str = string.Format("SELECT TOP {0} Id,Title,AddUser,CreateTime FROM [Messages]", pgpo.PageSize);
}
else
{
GET_SINGLE_PAGE_MESSAGE_LIST_FOR_SQL_TOP_MAX_str = string.Format(GET_SINGLE_PAGE_MESSAGE_LIST_FOR_SQL_TOP_MAX, pgpo.PageSize, pgpo.PageSize * (pgpo.CurrentPage - 1));
}
dbInstance.SqlCommandText = GET_SINGLE_PAGE_MESSAGE_LIST_FOR_SQL_TOP_MAX_str;
IList MsgList = dbInstance.ExecuteList();
dbInstance.Close();
IList messageDataObjects;
if (MsgList == null || MsgList.Count == 0)
{
messageDataObjects = null;
}
else
{
messageDataObjects = new ArrayList(MsgList.Count);
foreach (IDictionary dicMessageDO in MsgList)
{
MessageDataObject messageDO = new MessageDataObject();
messageDO.Id = Convert.ToInt32(dicMessageDO["ID"]);
messageDO.Title = dicMessageDO["TITLE"].ToString();
messageDO.AddUser = dicMessageDO["ADDUSER"].ToString();
messageDO.CreateTime = (DateTime)dicMessageDO["CREATETIME"];
messageDataObjects.Add(messageDO);
}
}
return messageDataObjects;
}
private const string GET_SINGLE_PAGE_MESSAGE_LIST_FOR_SQL_CURSOR = "SELECT TOP {0} Id,Title,AddUser,CreateTime FROM [Messages]";
/// <summary>
/// Get Message List For Single,游标方式
/// </summary>
/// <param name="pgpo">PaginationGoPageObject</param>
/// <returns>IList</returns>
public static IList GetSinglePageMessageListForSqlCursor(PaginationGoPageObject pgpo)
{
DataBaseInstance dbInstance = new DataBaseInstance();
dbInstance.Open(1);
//不能通过参数的方式来实现
string GET_SINGLE_PAGE_MESSAGE_LIST_FOR_SQL_CURSOR_str = string.Format(GET_SINGLE_PAGE_MESSAGE_LIST_FOR_SQL_CURSOR, pgpo.PageSize * pgpo.CurrentPage);
dbInstance.SqlCommandText = GET_SINGLE_PAGE_MESSAGE_LIST_FOR_SQL_CURSOR_str;
IList MsgList = dbInstance.ExecuteListForPage(pgpo.PageSize,pgpo.CurrentPage);
dbInstance.Close();
IList messageDataObjects;
if (MsgList == null || MsgList.Count == 0)
{
messageDataObjects = null;
}
else
{
messageDataObjects = new ArrayList(MsgList.Count);
foreach (IDictionary dicMessageDO in MsgList)
{
MessageDataObject messageDO = new MessageDataObject();
messageDO.Id = Convert.ToInt32(dicMessageDO["ID"]);
messageDO.Title = dicMessageDO["TITLE"].ToString();
messageDO.AddUser = dicMessageDO["ADDUSER"].ToString();
messageDO.CreateTime = (DateTime)dicMessageDO["CREATETIME"];
messageDataObjects.Add(messageDO);
}
}
return messageDataObjects;
}
以上用了SQL的3种方式：Not In方式，Max()方式，游标方式。
在我的电脑上DELL品牌机（型号：E520，内存增至2G,双核CPU），我导入了1000万条数据，每页显示10条记录。
以下是测试报告。
前面的分页显示时间
最后的分页显示时间
Not In方式
1秒内
6秒左右
Max()方式
1秒内
4-5秒
游标方式
1秒内
8秒左右
不难看出，Max()方式是效能最高的，它的缺点当然也很明显：增加了数据库服务器的负担。
Not In方式是不可取的，它不仅增加服务器负担,还是效率最低的。
当然对于我们来说，还要结合实际情况做合理的判断，进行取舍。
在无数次的试验中，我个人的经验就是好的算法是取得好效果的关键，在此我也望各位程序员对算法加强足够的重视，写出更优秀的代码。
- 最新文章
- 从SQL备份文件中导入现存数据库中[02-28]
- 如何恢复/修复MS SQL数据库的MDF文件[02-28]
- 分析MS SQL Server里函数的两种用法[02-28]
- 邹键SQL通用分页存储过程[02-28]
- 常用精妙的SQL语句[02-28]
- 给SQL Server Model数据库增加一些变化[02-28]
- 相关文章
- 从SQL备份文件中导入现存数据库中[02-28]
- 如何恢复/修复MS SQL数据库的MDF文件[02-28]
- 分析MS SQL Server里函数的两种用法[02-28]
- 邹键SQL通用分页存储过程[02-28]
- 常用精妙的SQL语句[02-28]
- 给SQL Server Model数据库增加一些变化[02-28]
