【SQL 改写】not exists-left join (distinct) where is null改写

数据已经过脱敏处理

--原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之内出结果