I am working through a performance issue and it is baffling me. The process is to iterate through a data set, using a year's worth of data at a time, to see what data, if any, needs to be inserted into the target table. This particular data set actually ends up correctly inserting zero records. The problem is that it takes several days to process a mere fifteen million rows. As a debugging tool, I iterated through the data just getting the count(*) of the records to insert and each year correctly comes back within a second or two and a count of zero. My question becomes, "Why does SQL quickly determine that there is no data to process when I ask for the COUNT but takes forever when I ask it to process the zero records?"
What I've done as an interim approach is to add an IF-EXISTS before the main processing a la:
if EXISTS(select *
insert into TargetTable(col list)
select col list
from <MyQuery>This allows the data to process within the expected time frame. I'd be much happier if I knew why there was such an extreme difference in the time it takes to determine that there are no records to process.
The actual <MyQuery> is simple:
from #temp_month_breaks eff
left join dbo.TargetTable tt
on eff.member_id = tt.member_id
and eff.effective_date = tt.effective_date
and eff.termination_date = tt.termination_date
and eff.plan_id = tt.plan_id
where tt.member_id is null
and eff.effective_date between @minid and @currentMaxAny thoughts are welcome.
I should note that this is code that has historically worked fine.
Hear the sledges with the bells - silver bells!
What a world of merriment their melody foretells!