sql分页翻页的优化？该怎么解决-MSSQL教程-爱易网页

sql分页翻页的优化？该怎么解决

日期：2014-05-17　浏览次数：20602 次

sql分页翻页的优化？？
前台是extjs 后台c# 接收前台的start 和limit参数进行分页翻页
sql语句如下

SQL code


select top " + limit + " * from View_jydhwxx where fgsbm in (" + fgsbm + ")  and jhrq  >= '" + jdsj1 + "' and jhrq  <= '" + jdsj2 + "' and jydbh not in(select top " + start + " jydbh from View_jydhwxx where fgsbm in (" + fgsbm + ") and jhrq  >= '" + jdsj1 + "' and jhrq  <= '" + jdsj2 + "' order by jydbh desc) order by jydbh desc

以上的语句是没有问题的速度也还可以接受，但是现在我又在上面的语句中加入了一个条件 dzzbm in ("+result+") 如 dzzbm in (001,002,003,004,005,006,007....)
变成了

SQL code

select top " + limit + " * from View_jydhwxx where fgsbm in (" + fgsbm + ") and dzzbm in ("+result+") and jhrq  >= '" + jdsj1 + "' and jhrq  <= '" + jdsj2 + "' and jydbh not in(select top " + start + " jydbh from View_jydhwxx where fgsbm in (" + fgsbm + ") and dzzbm in ("+result+") and jhrq  >= '" + jdsj1 + "' and jhrq  <= '" + jdsj2 + "' order by jydbh desc) order by jydbh desc

现在问题来了速度很慢
应该如何优化？？

------解决方案--------------------

C# code


string sql="select * from (
    select row_number() over(order by name) rn,* from View_jydhwxx where fgsbm 
        in (" + fgsbm + ")  and jhrq  >= '" + jdsj1 + "' and jhrq  <= '" + jdsj2 + "'
        order by jydbh desc) t
    where rn between " + start + " and " + start+limit;