sql分页翻页的优化？？

jiery666 2012-08-19 09:36:50

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



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....)
变成了

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

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

...全文

77 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

jiery666 2012-08-19

打赏
举报

基本解决了我的问题

where rn between " + start + " and " + start+limit;
这个错了 start+limit 没有转换过来

人生无悔 2012-08-19

打赏
举报



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;