查询语句如下所示:
SELECT * FROM(SELECT ROW_NUMBER() OVER(order by dtTime desc) AS rownum,
dtTime ... FROM TbTable)
AS T
where (rownum BETWEEN 10000 AND 10014)使用到ROW_NUMBER() OVER查询,因为要实现分页,
数据库有十几万数据,查询很慢,查询时间要7,8秒,有什么办法解决????
SELECT * FROM(SELECT ROW_NUMBER() OVER(order by dtTime desc) AS rownum,
dtTime ... FROM TbTable)
AS T
where (rownum BETWEEN 10000 AND 10014)使用到ROW_NUMBER() OVER查询,因为要实现分页,
数据库有十几万数据,查询很慢,查询时间要7,8秒,有什么办法解决????
FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1
) A
WHERE RowNumber > 页大小*(页数-1)
DECLARE @pagenum AS INT, @pagesize AS INT
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum,
newsid, topic, ntime, hits
FROM news) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY newsid DESC
建索引
/// <summary>
/// 把一般的查询sql处理成分页用的sql
/// </summary>
/// <param name="sql">一般的sql</param>
/// <param name="pn">当前页数</param>
/// <param name="countPerPage">每一页的个数</param>
/// <returns>返回分页用的sql</returns>
public static string SQLFenyeChuli(string sql, int pn, int countPerPage)
{
try
{
string topCount = (pn * countPerPage).ToString();//获取获取前多少条数据
string strAlreadyCount = ((pn - 1) * countPerPage).ToString();//获取已经查询的数据 string rtn = sql.Insert(6, " top " + topCount + " 0 as tempcolumn,");//在select后面插入分页用的数据
rtn = "select * from (select row_number() over (order by tempcolumn) temprow, * from (" + rtn + ")tt)t where temprow >" + strAlreadyCount; return rtn;
}
catch
{
return "";
}
}
试试这个把普通的sql进行分页处理一下。其中sql只是普通的查询语句,例如select a,b,c from d order by a desc,应该快点的。