十几万数据使用ROW_NUMBER() OVER查询很慢

[sakuram]

查询语句如下所示：
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秒，有什么办法解决？？？？

[Iovswety]

目前来说，这个办法来做分页查询是很好的了，或者从索引、数据库设计方面入手提高效率吧。。。

[whowhen21]

这种查询理论上比那种in子查询还快点呀，不至于这么慢吧~~

[prame001]

这个应该是还好的啊~~~~

[wuyq11]

SELECT TOP 页大小 *
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
建索引

[wubudang]

/// <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，应该快点的。