实际项目中有如下SQL, 发现效率很低，用时超过1分钟

　　　　　　　select TaskID, StartDate = min(UpdateTime), EndDate = max(UpdateTime)            from BFDB_Code.dbo.packcodeinfo p            inner join Task t on p.TaskID = t.ID            where datediff(day, t.EndDate, getdate()) < @day                      and t.Status in(4,5)             group by TaskID

通过查询计划可知上面语句进行了全表扫描，所以效率很低

 

单个查询时并不慢，因为在TaskID上已经建立索引

select TaskID, StartDate = min(UpdateTime), EndDate = max(UpdateTime)from BFDB_Code.dbo.packcodeinfo pwhere p.TaskID  in (2488,2499)group by TaskID

但如果是这样查询，仍然会全表扫描

select TaskID, StartDate = min(UpdateTime), EndDate = max(UpdateTime)from BFDB_Code.dbo.packcodeinfo pwhere p.TaskID  in (    SELECT id FROM task t where t.Status in(2,3))group by TaskID

 

优化方案

去除inner join，写一个函数返回类似(2488,2499)值，然后动态构造SQL语句执行。

函数定义get_begin_task

create function [dbo].[get_begin_task]()returns varchar(1000) as begin  DECLARE @csv VARCHAR(1000)SELECT @csv = COALESCE(@csv + ',', '') + Convert(varchar(10), ID)FROM task twhere t.Status in(2,3)    and createdate  >= '2016-05-01';Return @csvend;

修改存储过程如：

declare @sql varchar(8000);set @sql= '    select TaskID, StartDate = min(UpdateTime), EndDate = max(UpdateTime)            from BFDB_Code.dbo.packcodeinfo p            where p.TaskID  in (' + dbo.get_begin_task() +')            group by TaskID'exec(@sql);

小插曲：

因为用到了跨数据库查询，动态执行SQL语句需要执行 EXEC sp_addlinkedserver  ‘BFDB_Code’命令。