测试下这样是否有效:
declare @PreTime datetime
set @PreTime =getdate()
declare @ControlTime datetime
set @ControlTime=getdate()
select count(FID)
from A
where FID in (select UserID from B
where RegisterTime between @ControlTime and @PreTime)
option(optimize for (@ControlTime='2009-02-12 23:50:00',@PreTime='2009-02-13 12:00:00'))
declare @PreTime datetime
set @PreTime =getdate()
declare @ControlTime datetime
set @ControlTime=getdate()
select count(FID)
from A
where FID in (select UserID from B
where RegisterTime between @ControlTime and @PreTime)
option(optimize for (@ControlTime='2009-02-12 23:50:00',@PreTime='2009-02-13 12:00:00'))
select count(fid) from a
inner join b on a.fid=b.userid
and RegisterTime between @ControlTime and @PreTime
是2K,in 和 join 效果是一样的,但是是不是有的时候join 的效果还不如not in 或者in呀.
create index ix_001 on b (registertime,userid)
declare @PreTime datetime
set @PreTime =getdate()
declare @ControlTime datetime
set @ControlTime=getdate()
exec('
select count(fid) from a
inner join b on a.fid=b.userid
and RegisterTime between '''+@ControlTime+''' and +''' + @PreTime + '''')
现在想知道这两个语句之间为什么会有差异,不光查询计划,还包括io下的表扫描计数,逻辑读等等 都不一样.差在哪,况且存储过程里面用的都是变量,难不成都改成exec(@sql) 执行字符串这样吧?