title: SQL Query runs forever - SQL Server 2008
body: <p>I have two Tables in two different databases</p>
  
  <pre><code>Database1 - Table1
  Database2 - Table2
  
  Table1 Columns: NimID,IDDate,Station
  Table2 Columns: XilID,XilDate
  
  Table1                                                   Table2
  NimID   IDDate           Station                   XilID       XilDate      
  234     2011-04-21       HYD                     234        2011-04-21
  237     2011-04-21       CHN                     208        2011-04-21
  208     2011-04-21       HYD                     209        2011-04-15
  209     2011-04-15       DEL                      218        2011-05-28
                                                               212       2011-03-11
  </code></pre>
  
  <p>I want to find out how many IDs in Table1 are not in Table2 where IDDate=XilDate='2011-04-21' group by Table1.Station   .
  I made the query below</p>
  
  <pre><code>select x.Station as Station,
         count(distinct x.NimID) as Difference
  from (
        select a.NimID,
               a.IDDate,
               a.Station
        from database1.dbo.table1 a
        where left(cast(a.Date as date),11)='2011-04-21'
   ) as X, (
        select b.XilID,
               b.XILDate 
        from database2.dbo.Table2 b
        where b.XilDate='2011-04-21'
   ) as Y
   where x.NimID not in (y.XilID)
   group by x.Station
  </code></pre>
  
  <p>But this query runs forever..
  Please remember the tables are from different databases located on same server and Table1 contains 10,000,000 records and Table2 contains around 13,000,000 records
  Please correct my query if wrong or suggest me the faster way</p>
  
  <p>Thanks</p>