SQL Query runs forever - SQL Server 2008

Question

I have two Tables in two different databases

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

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

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

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

Thanks

Andriy M · Accepted Answer · 2011-06-17 08:21:18Z

1

DECLARE @date datetime;
SET @date = '20110421';

SELECT
  Station,
  Diff = COUNT(*)
FROM (
  SELECT
    a.NimID,
    a.IDDate,
    a.Station
  FROM database1.dbo.table1 a
    LEFT JOIN database2.dbo.table2 b ON a.NimID = b.XilID AND b.XilDate = @date
  WHERE b.XilID IS NULL
    AND a.IDDate >= @date
    AND a.IDDate < DATEADD(day, 1, @date)
) s
GROUP BY Station

UPDATE

Actually, the above solution could be rewritten without subselects. The subselect is the result of trying some idea, which I've eventually discarded, but the subselect has remained for some unknown reason. Here's an identical solution with no subselects:

DECLARE @date datetime;
SET @date = '20110421';

SELECT
  a.Station,
  Diff = COUNT(*)
FROM database1.dbo.table1 a
  LEFT JOIN database2.dbo.table2 b ON a.NimID = b.XilID AND b.XilDate = @date
WHERE b.XilID IS NULL
  AND a.IDDate >= @date
  AND a.IDDate < DATEADD(day, 1, @date)
GROUP BY a.Station

edited Jun 17, 2011 at 8:21

answered Jun 17, 2011 at 7:31

Andriy M

78k18 gold badges100 silver badges157 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ARB Over a year ago

Amazing man... Thanks a lot... I always hated to use joins..Now joins helped me with millions of data..:)

Andriy M Over a year ago

@ARB: Good for you then. Seems very much like you are going to hate them less now. :) Please have a look at my update where I've attempted to rewrite the solution to make it simpler.

Anthony Faull · Accepted Answer · 2011-06-17 07:11:24Z

1

Try to avoid converting from datetime to varchar.

  WHERE a.Date >= '2011-04-21'
  AND a.Date < (CAST('2011-04-21' AS datetime) + 1)

answered Jun 17, 2011 at 7:11

Anthony Faull

18.1k5 gold badges58 silver badges75 bronze badges

1 Comment

ARB Over a year ago

Hi, thanks.. it reduces half of the time.. But still taking 1 second for each record... huh

Will A · Accepted Answer · 2011-06-17 07:02:40Z

Try the below - note that you appeared to be attempting to join the two tables to perform the 'not in' which would result in a very slow to produce and very wrong resultset.

Also, if IDDate is a DATETIME column then you'd be better of performing a range check e.g. (a.IDDate >= '2011-04-21' AND a.IDDate < '2011-04-22'). Thinking about it - if it's a text column in the format yyyy-MM-dd then a range check would also work - if it's a text column with mixed format dates then forget I mentioned it.

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.IDDate as date),11)='2011-04-21'
 ) as X
 where x.NimID not in (
      select b.XilID
      from database2.dbo.Table2 b
      where b.XilDate='2011-04-21'
 )
 group by x.Station

Collectives™ on Stack Overflow

SQL Query runs forever - SQL Server 2008

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related