求一个ACCESS的高效分页语句
求一个Access 2003 的高效分页语句，网上找的一堆，大部分雷同、而且测试无效，郁闷。
数据量10万。系统设计当初，没想到会有10万的数据，所以当时采用的是内存分页。才导致
现在每次按分页页码，都能引起服务器CPU使用率飙升。
求解！
------------
我有几个贴，都是在线的，有号的方法立马结贴
http://topic.csdn.net/u/20120814/10/9ff7eb6b-96fa-4ff5-97b9-69fd268db298.html
http://topic.csdn.net/u/20120310/17/e09d8b13-dfed-4204-bbc2-5c8d3876ad19.html
[解决办法]
换数据库。
[解决办法]
程序代码string strSql = " Select P.ProductID,P.ProClassID,P.ProductName,P.ProFlag,P.DateTime,P.ProductImage,C.ProClassName,C.ProClassKey,C.ProClassDes FROM ProductInfo P " + " LEFT JOIN ProClass C ON P.ProClassID=C.ProClassID "; string strShow = " ProductID,ProductName,DateTime,ProductImage,ProFlag,ProClassName,ProClassKey,ProClassDes "; return ClassFile.AccessHelper.ExecutePager(PageIndex, PageSize, "ProductID", strShow, strSql, strWhere, " ProductID DESC ", out PageCount, out RecordCount); 程序代码/// <summary> /// 分页使用 /// </summary> /// <param name="query"></param> /// <param name="passCount"></param> /// <returns></returns> private static string recordID(string query, int passCount) { using (OleDbConnection m_Conn = new OleDbConnection(ConnectionString)) { m_Conn.Open(); OleDbCommand cmd = new OleDbCommand(query, m_Conn); string result = string.Empty; using (OleDbDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { if (passCount < 1) { result += "," + dr.GetInt32(0); } passCount--; } } m_Conn.Close(); m_Conn.Dispose(); return result.Substring(1); } } /// <summary> /// ACCESS高效分页 /// </summary> /// <param name="pageIndex">当前页码</param> /// <param name="pageSize">分页容量</param> /// <param name="strKey">主键</param> /// <param name="showString">显示的字段</param> /// <param name="queryString">查询字符串，支持联合查询</param> /// <param name="whereString">查询条件，若有条件限制则必须以where 开头</param> /// <param name="orderString">排序规则</param> /// <param name="pageCount">传出参数：总页数统计</param> /// <param name="recordCount">传出参数：总记录统计</param> /// <returns>装载记录的DataTable</returns> public static DataTable ExecutePager(int pageIndex, int pageSize, string strKey,string showString, string queryString, string whereString, string orderString, out int pageCount, out int recordCount) { if (pageIndex < 1) pageIndex = 1; if (pageSize < 1) pageSize = 10; if (string.IsNullOrEmpty(showString)) showString = "*"; if (string.IsNullOrEmpty(orderString)) orderString = strKey+" asc "; using (OleDbConnection m_Conn = new OleDbConnection(ConnectionString)) { m_Conn.Open(); string myVw = string.Format(" ( {0} ) tempVw ", queryString); OleDbCommand cmdCount = new OleDbCommand(string.Format(" select count(*) as recordCount from {0} {1}", myVw, whereString), m_Conn); recordCount = Convert.ToInt32(cmdCount.ExecuteScalar()); if ((recordCount % pageSize) > 0) pageCount = recordCount / pageSize + 1; else pageCount = recordCount / pageSize; OleDbCommand cmdRecord; if (pageIndex == 1)//第一页 { cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, whereString, orderString), m_Conn); } else if (pageIndex > pageCount)//超出总页数 { cmdRecord = new OleDbCommand(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageSize, showString, myVw, "where 1=2", orderString), m_Conn); } else { int pageLowerBound = pageSize * pageIndex; int pageUpperBound = pageLowerBound - pageSize; string recordIDs = recordID(string.Format("select top {0} {1} from {2} {3} order by {4} ", pageLowerBound, strKey, myVw, whereString, orderString), pageUpperBound); cmdRecord = new OleDbCommand(string.Format("select {0} from {1} where {2} in ({3}) order by {4} ", showString, myVw,strKey, recordIDs, orderString), m_Conn); } OleDbDataAdapter dataAdapter = new OleDbDataAdapter(cmdRecord); DataTable dt = new DataTable(); dataAdapter.Fill(dt); m_Conn.Close(); m_Conn.Dispose(); return dt; } }