SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Performance: count(*) vs insert-select
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1770 Posts

Posted - 12/11/2012 :  13:57:07  Show Profile  Reply with Quote
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 *
from <MyQuery>
)
  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:
select
  eff.member_id,
  eff.payor_id,
  eff.effective_date,
  eff.termination_date,
  eff.plan_id
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 @currentMax
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!

Edited by - Bustaz Kool on 12/11/2012 14:23:20
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000