【SQL改写】notexists-leftjoin(distinct)whereisnull改写_标量子查询

最新推荐文章于 2022-06-25 16:42:15 发布

bwh0520

最新推荐文章于 2022-06-25 16:42:15 发布

阅读量302

点赞数

--原SQL 30min出不来结果
select a.*
from TeableA a
where is_open = '1'
and ecd = 'xshg'
and not exists
(select * from bvaluation b
where a.calendar_date = b.tdate)
and a.calendar_date >= '2011-09-06'
and a.calendar_date < convert( date, getdate())
--表信息查看
select count(1)
from TeableA a
where is_open = '1'
and ecd = 'xshg'
and a.calendar_date >= '2011-09-06'
and a.calendar_date < convert( date, getdate())
--1357
select count(1) from bvaluation b
--8262721
--改写SQL1
select
a.*
from TeableA a left join tdate b on (a.calendar_date = b.tdate)
where
a.is_open = '1' and
a.ecd = 'xshg' and
a.calendar_date >= '2011-09-06' and
a.calendar_date < convert( date,getdate()) and
b.tdate is null
--发现a.calendar_date: b.tdate=1:N ，join后会导致a表的重复，查询反馈很快，但是因为重复result set跑完耗时还是较长。
--sp_helpindex bvaluation
--bvaluation$BPK_AK_Key nonclustered, unique, unique key located on DATA SID, TSYMBOL, tdate, YEART_MAT
--唯一约束中包含tdate
--改写SQL2（Final）
select
a.*
from TeableA a left join (select distinct tdate from bvaluation)
b on (a.calendar_date = b.tdate)
where
a.is_open = '1' and
a.ecd = 'xshg' and
a.calendar_date >= '2011-09-06' and
a.calendar_date < convert( date,getdate()) and
b.tdate is null
--1s之内出结果