分页代码及相应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]))";

<script type="text/javascript"> </script><script type="text/javascript" src="http://pagead2.googlesyndication.com/pagead/show_ads.js"> </script>

/// <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方式是不可取的，它不仅增加服务器负担,还是效率最低的。

当然对于我们来说，还要结合实际情况做合理的判断，进行取舍。

在无数次的试验中，我个人的经验就是好的算法是取得好效果的关键，在此我也望各位程序员对算法加强足够的重视，写出更优秀的代码。