SQL语句优化有一个很重要的方法:
在Where中的条件表达式,对查询字段尽量不用函数,因为查询字段上的索引将用不上。下面就有一个导致数据库死锁的例子。
select ComId
into #temp001
from JOBCN_129.jobcn90.dbo.com_TractApplyDetailOpen where convert(varchar(10),OpenDate,120)=convert(varchar(10),getdate(),120)
group by ComId
改为
declare @date1 smalldatetime
declare @date2 smalldatetime
set @date1 = convert(smalldatetime,convert(varchar(10),getdate(),120))
set @date2 = @date1 + 1
select ComId
into #temp001
from JOBCN_129.jobcn90.dbo.com_TractApplyDetailOpen where opendate>=@date1 and opendate<@date2
group by ComId
into #temp001
from JOBCN_129.jobcn90.dbo.com_TractApplyDetailOpen where opendate>=@date1 and opendate<@date2
group by ComId