sql语句的优化？

[jiery666]

select top 42 * from View_jydhwxx where fgsbm in (101) and dzzbm in (043,065,006,839,008,546,118,312,512,535,021,061,023,714,020,123,017,007,032)  and jhrq  >= '2012-08-10 16:49:06' and jhrq  <= '2012-08-17 16:49:06' and jydbh not in (select top 0 jydbh from View_jydhwxx where fgsbm in (101) and jhrq  >= '2012-08-10 16:49:06' and dzzbm in (043,065,006,839,008,546,118,312,512,535,021,061,023,714,020,123,017,007,032)  and jhrq  <= '2012-08-17 16:49:06' order by jydbh desc) order by jydbh desc

以上这句语句是没有问题的，查询的速度是2秒钟
但是 如果 将 select top 0 jydbh 改为42的倍数 即 42 84 。。。 的话 速度就很慢 提示时间超时
select top "+start+" jydbh
start 是接收翻页的参数 第一页是0 第二页42 第三页84 类推.....

怎么将上面的语句改为翻页的时候 速度快点

[--小F--]

用ROW_NUMBER来分页吧。

[问苍天人生几何]

试试

select top 42 * from View_jydhwxx 
where fgsbm=101 
and dzzbm in (043,065,006,839,008,546,118,312,512,535,021,061,023,714,020,123,017,007,032)  
and jhrq  between '2012-08-10 16:49:06' and '2012-08-17 16:49:06' 
and not exists (select 1 from View_jydhwxx t
				  where t.fgsbm=101
				  and t.jhrq  between '2012-08-10 16:49:06' and '2012-08-17 16:49:06' 
				  and t.dzzbm in (043,065,006,839,008,546,118,312,512,535,021,061,023,714,020,123,017,007,032)  
				  and t.jydbh=jydbh) 
order by jydbh desc

[jiery666]

public DataSet GetRegionsInfo(int start, int limit,string jdsj1,string jdsj2,string fgsbm,string zxmc)
        {
            DataSet ds_zx = sqlhelper.GetDataSet("select FDz_id from bm_zxxxmx where Fzx_id in (" + zxmc + ")");
            int size = ds_zx.Tables[0].Rows.Count;
            string result = null;
            for (int i = 0; i < size; i++)
            {
                result += ds_zx.Tables[0].Rows[i]["FDz_id"].ToString() + ",";
            }
            result = result.TrimEnd(',');
            try
            {
                string 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";
                ds = sqlhelper.GetDataSet(sql);
                return ds;
            }
            catch (Exception)
            {

                throw;
            }
        }

以上是extjs中分页并可翻页的后台sql语句
start limit 参数必须用上啊 翻页的时候 start 接收参数 是变动值

[以学习为目的]

支持小F姐，用ROW_NUMBER实现

[jiery666]

select top 42 * from View_jydhwxx where fgsbm in (101) and dzzbm in (043,065,006,839,008,546,118,312,512,535,021,061,023,714,020,123,017,007,032)  
and jhrq  >= '2012-08-10 16:49:06' and jhrq  <= '2012-08-17 16:49:06' and jydbh not in (select top 42 jydbh from View_jydhwxx 
where fgsbm in (101) and jhrq  >= '2012-08-10 16:49:06' and dzzbm in (043,065,006,839,008,546,118,312,512,535,021,061,023,714,020,123,017,007,032) 
 and jhrq  <= '2012-08-17 16:49:06' order by jydbh desc) order by jydbh desc

将以上语句用not exists 替换 怎么做？