Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
 Posted - 2012-12-11 : 13:57:07
|
| 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:[CODE]if EXISTS(select *from <MyQuery>) insert into TargetTable(col list) select col list from <MyQuery>[/CODE]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:[CODE]select eff.member_id, eff.payor_id, eff.effective_date, eff.termination_date, eff.plan_idfrom #temp_month_breaks effleft 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_idwhere tt.member_id is nulland eff.effective_date between @minid and @currentMax[/CODE]Any 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! |
|
|
|
|
|
Performance: count(*) vs insert-select