Strange query behaviour in OLAP

Question

 Strange behaviour from below query.
  
   Table A have around 600 million rows (Clustered Index Timekey column)
    Table B have around 2 million rows (Clustered index TimeKey Column)
  
    select COUNT(1) from
     TableA a inner join (select timekey from TableB where somecolumn = 4) c
      on a.timekey = c.timekey
  
      Now if you run the subquery separetly it runs in subsecond as there no data (0 rows) so my question is why the whole select runs for more than 20 mins
       and doing big seek on clustered index in TableA when there is not even one matching row coming from subquery)
  
       Thanks,
       NJ

Answer 1

Have you tried:

 

select count(1) as cnt FROM TableA

where exists (select 1 from TableB where SomeColumn = 4 and TableB.TimeKey = TableA.TimeKey)

 

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

Yes. Tried all variations of subquery. exists , In() , cross apply etc. but still runs very slow..

I have the backup solution but trying to understand this behaviour. I added option(maxdop 1) then it runs in 5 sec(still slow with no rows returned form subquery) .

Its Sql Server 2008 R2 Enterprise Edition.

NJ

 

Answer 3

The last resort:

select TimeKey into #TempRes from TableB where SomeColumn = 4

index the #TempRes table

do the inner join query.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog