locked
Strange query behaviour in OLAP RRS feed

  • 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
    Tuesday, June 21, 2011 11:45 PM

Answers

  • 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
    • Proposed as answer by Alex Feng (SQL) Tuesday, June 28, 2011 9:20 AM
    • Marked as answer by Kalman Toth Friday, July 1, 2011 11:49 AM
    Wednesday, June 22, 2011 12:40 AM

All replies

  • 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
    Wednesday, June 22, 2011 12:16 AM
  • 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

     


    Wednesday, June 22, 2011 12:34 AM
  • 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
    • Proposed as answer by Alex Feng (SQL) Tuesday, June 28, 2011 9:20 AM
    • Marked as answer by Kalman Toth Friday, July 1, 2011 11:49 AM
    Wednesday, June 22, 2011 12:40 AM