数据已经过脱敏处理
--原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之内出结果