sqlserver使用临时表提高速度

select Distinct
t8.objname 唯一编码
,t8.objjc 名称
,t8.objid,t8.parentid,t8.objname,t8.objjc,t8.objorder
,case t8.worktype when 'ST' then 0 when 'FR' then 2 when 'WK' then 4 else 6 end xbico
from cwfsys_work a 
left join cwfsys_work t1 on a.objid=t1.parentid or a.objid=t1.objid
left join cwfsys_work t2 on t1.objid=t2.parentid or t1.objid=t2.objid
left join cwfsys_work t3 on t2.objid=t3.parentid or t2.objid=t3.objid
left join cwfsys_work t4 on t3.objid=t4.parentid or t3.objid=t4.objid
left join cwfsys_work t5 on t4.objid=t5.parentid or t4.objid=t5.objid
left join cwfsys_work t6 on t5.objid=t6.parentid or t5.objid=t6.objid
left join cwfsys_work t7 on t6.objid=t7.parentid or t6.objid=t7.objid
left join cwfsys_work t8 on t7.objid=t8.parentid or t7.objid=t8.objid

where a.objname='#工作分类#'

原sql ,运行5-6秒

 

if object_id('tempdb..#tempwork') is not null drop table #tempwork
select Distinct
t4.objname 唯一编码
,t4.objjc 名称
,t4.objid,t4.parentid,t4.objname,t4.objjc,t4.objorder
,case t4.worktype when 'ST' then 0 when 'FR' then 2 when 'WK' then 4 else 6 end xbico
into #tempwork
from cwfsys_work a 
join cwfsys_work t1 on a.objid=t1.parentid or a.objid=t1.objid
join cwfsys_work t2 on t1.objid=t2.parentid or t1.objid=t2.objid
join cwfsys_work t3 on t2.objid=t3.parentid or t2.objid=t3.objid
join cwfsys_work t4 on t3.objid=t4.parentid or t3.objid=t4.objid
join cwfsys_work t5 on t4.objid=t5.parentid or t4.objid=t5.objid
join cwfsys_work t6 on t5.objid=t6.parentid or t5.objid=t6.objid
join cwfsys_work t7 on t6.objid=t7.parentid or t6.objid=t7.objid
join cwfsys_work t8 on t7.objid=t8.parentid or t7.objid=t8.objid
where a.objname='#工作分类#'

select Distinct
t8.objname 唯一编码
,t8.objjc 名称
,t8.objid,t8.parentid,t8.objname,t8.objjc,t8.objorder
,case t8.worktype when 'ST' then 0 when 'FR' then 2 when 'WK' then 4 else 6 end xbico
from #tempwork t4
join cwfsys_work t5 on t4.objid=t5.parentid or t4.objid=t5.objid
join cwfsys_work t6 on t5.objid=t6.parentid or t5.objid=t6.objid
join cwfsys_work t7 on t6.objid=t7.parentid or t6.objid=t7.objid
join cwfsys_work t8 on t7.objid=t8.parentid or t7.objid=t8.objid
if object_id('tempdb..#tempwork') is not null drop table #tempwork

修改后sql，采用临时表，运行2-3秒

转载于:https://www.cnblogs.com/cwfsoft/archive/2010/08/23/1806177.html