sql 临时表与子查询效率解析

update DC_Keyword set wordStatus='已启用'
where word = '类目出价' and wordStatus=''

select 
a.data_time,
a.campaign_id,
a.adgroup_id,
a.nick,

b.word,
b.wordStatus,
--a.isUseCategoryPrice,
case b.wordStatus when '未启用' then 0
else 1 end NewIsUseCategoryPrice
INTO #TEMP111
from DC_ADGroup a
inner join DC_Keyword b
on a.data_time=b.data_time and a.campaign_id=b.campaign_id
and a.adgroup_id=b.adgroup_id
where b.word = '类目出价'


update DC_ADGroup set isUseCategoryPrice= BB.NewIsUseCategoryPrice
--select 
--AA.data_time,
--AA.campaign_id,
--AA.adgroup_id,
--AA.nick,

--BB.word,
--BB.wordStatus,
--AA.isUseCategoryPrice,
--BB.NewIsUseCategoryPrice
from DC_ADGroup AA
inner join #TEMP111 BB --注意：如果这里不用临时表，当执行大数据量时，效率很差
ON AA.data_time=BB.data_time AND AA.nick=BB.nick AND AA.campaign_id=BB.campaign_id AND AA.adgroup_id=BB.adgroup_id

--不建议用下面这种方式：

update DC_Keyword set wordStatus='已启用'
where word = '类目出价' and wordStatus=''

update DC_ADGroup set isUseCategoryPrice= BB.NewIsUseCategoryPrice
--select 
--AA.data_time,
--AA.campaign_id,
--AA.adgroup_id,
--AA.nick,

--BB.word,
--BB.wordStatus,
--AA.isUseCategoryPrice,
--BB.NewIsUseCategoryPrice
from DC_ADGroup AA
inner join 
(

select 
a.data_time,
a.campaign_id,
a.adgroup_id,
a.nick,

b.word,
b.wordStatus,
--a.isUseCategoryPrice,
case b.wordStatus when '未启用' then 0
else 1 end NewIsUseCategoryPrice
INTO #TEMP111
from DC_ADGroup a
inner join DC_Keyword b
on a.data_time=b.data_time and a.campaign_id=b.campaign_id
and a.adgroup_id=b.adgroup_id
where b.word = '类目出价'

) BB 
ON AA.data_time=BB.data_time AND AA.nick=BB.nick AND AA.campaign_id=BB.campaign_id AND AA.adgroup_id=BB.adgroup_id

 

转载于:https://www.cnblogs.com/xust/articles/2869706.html