里面的select怎么做效率能更高点？

[chenlm]

declare @table1 table (f1 int,f2 int,f3 int)
insert @table1 values( 1 , 2 , 2)
insert @table1 values( 1 , null , 4)
insert @table1 values( 3 , 6 , null)
insert @table1 values( 4 , 6 , 12)
insert @table1 values( 4 , 6 , 7)
insert @table1 values( 9 , 12 , 11)
insert @table1 values( 2 , null , null)


--除非f2,f3都为空，f1不会大于f2,f3
--f1不会为空
--先判断f2大还是f3大，如果f3大，那么就f3,如果f2大就f2,如果f2,f3都为空，则f1

select * from @table1 where case when isnull(f3,0)>isnull(f2,1) then f3 when f2 is not null then f2 else f1 end between 2 and 5

但是这样子效率太差了，数据库中有上百万的数据
怎么做效率好点？

下面是原始的代码
select top 50 xxb.ddbh,xxb.mbid,yhbh,yhmc,xqmc,xxb.ddlx,gzdj,subddlx,sgrq,slry
,yjsj,yjry,clry,lz.clzt,lz.clbm,yhdz,sjjjbf,isnull(reportcount,1) as 'reportcount',
case when isnull(xxb.ChangeTime,'1900')>isnull(lz.yjsj,'2003') then xxb.ChangeTime
when lz.yjsj is not null then lz.yjsj else xxb.sgrq end as 'LastTime'
from gz_xxb as xxb left join dd_lz as lz on xxb.serialno = lz.serialno
where 1=1
--and case when isnull(xxb.ChangeTime,'1900')>isnull(lz.yjsj,'2003') then xxb.ChangeTime
--when lz.yjsj is not null then lz.yjsj else xxb.sgrq end between '2003.11.14' and '2003.11.16 23:59:59'
--and LastTime between ??? and ??? 这样能做就好了
and xxb.ddlx = 'G001'
and area = '市区'
and lz.clzt = '归档'
and clbm = 'Mase'

[pengdali]

select * from @table1 where case when f3>f2 then f3 when f2 is not null then f2 else f1 end between 2 and 5

[pengdali]

select top 50 xxb.ddbh,xxb.mbid,yhbh,yhmc,xqmc,xxb.ddlx,gzdj,subddlx,sgrq,slry
,yjsj,yjry,clry,lz.clzt,lz.clbm,yhdz,sjjjbf,isnull(reportcount,1)[reportcount],
case when xxb.ChangeTime>lz.yjsj then xxb.ChangeTime
when lz.yjsj is not null then lz.yjsj else xxb.sgrq end [LastTime]
from gz_xxb as xxb left join dd_lz as lz on xxb.serialno = lz.serialno
where 1=1

[pengdali]

select top 50 xxb.ddbh,xxb.mbid,yhbh,yhmc,xqmc,xxb.ddlx,gzdj,subddlx,sgrq,slry
,yjsj,yjry,clry,lz.clzt,lz.clbm,yhdz,sjjjbf,isnull(reportcount,1)[reportcount],
case when xxb.ChangeTime>lz.yjsj then xxb.ChangeTime
when lz.yjsj is not null then lz.yjsj else xxb.sgrq end [LastTime]
from gz_xxb as xxb left join dd_lz as lz on xxb.serialno = lz.serialno
where 1=1

[chenlm]

select xxb.ddbh,xxb.mbid,yhbh,yhmc,xqmc,xxb.ddlx,gzdj,subddlx,sgrq,slry
,yjsj,yjry,clry,lz.clzt,lz.clbm,yhdz,sjjjbf,isnull(reportcount,1)[reportcount],
case when xxb.ChangeTime>lz.yjsj then xxb.ChangeTime
when lz.yjsj is not null then lz.yjsj else xxb.sgrq end [LastTime]
from gz_xxb as xxb left join dd_lz as lz on xxb.serialno = lz.serialno
上面的代码做成 视图(v_dd)
select top 100 * from v_dd where 1=1 and ...... lasttime between ??? and ??....
然后在这个视图上筛选 效率高还是下面的效率高？？

select top 50 xxb.ddbh,xxb.mbid,yhbh,yhmc,xqmc,xxb.ddlx,gzdj,subddlx,sgrq,slry
,yjsj,yjry,clry,lz.clzt,lz.clbm,yhdz,sjjjbf,isnull(reportcount,1)[reportcount],
from gz_xxb as xxb left join dd_lz as lz on xxb.serialno = lz.serialno
where 1=1
and.....
and case when xxb.ChangeTime>lz.yjsj then xxb.ChangeTime
when lz.yjsj is not null then lz.yjsj else xxb.sgrq end between ????and ?????
and ....
and ....

[chenlm]

select xxb.ddbh,xxb.mbid,yhbh,yhmc,xqmc,xxb.ddlx,gzdj,subddlx,sgrq,slry
,yjsj,yjry,clry,lz.clzt,lz.clbm,yhdz,sjjjbf,isnull(reportcount,1)[reportcount],
case when xxb.ChangeTime>lz.yjsj then xxb.ChangeTime
when lz.yjsj is not null then lz.yjsj else xxb.sgrq end [LastTime]
from gz_xxb as xxb left join dd_lz as lz on xxb.serialno = lz.serialno
上面的代码做成 视图(v_dd)
select top 100 * from v_dd where 1=1 and ...... lasttime between ??? and ??....
然后在这个视图上筛选 效率高还是下面的效率高？？

select top 50 xxb.ddbh,xxb.mbid,yhbh,yhmc,xqmc,xxb.ddlx,gzdj,subddlx,sgrq,slry
,yjsj,yjry,clry,lz.clzt,lz.clbm,yhdz,sjjjbf,isnull(reportcount,1)[reportcount],
from gz_xxb as xxb left join dd_lz as lz on xxb.serialno = lz.serialno
where 1=1
and.....
and case when xxb.ChangeTime>lz.yjsj then xxb.ChangeTime
when lz.yjsj is not null then lz.yjsj else xxb.sgrq end between ????and ?????
and ....
and ....

[zjcxc]

楼主的条件是and的吗?

[chenlm]

to zjcxc(邹建) :哪个条件？

where 后都是 and