前阵子一直在研究一个老问题，就是分页效率问题。虽然网上代码不计其数，但是大部分都是雷同的，不尽如人意。在此我把这几天的研究成果给大家分享下，希望对各位程序开发人员有所帮助。（注：这里没有研究分页缓存机制的问题，只是从优化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，内存增至
以下是测试报告。
|
|
前面的分页显示时间 |
最后的分页显示时间 |
||
|
Not In方式 |
1秒内 |
6秒左右 |
||
|
Max()方式 |
1秒内 |
4-5秒 |
||
|
游标方式 |
1秒内 |
8秒左右
|