CODEWITHSUNDEEP
sql-server
ARB
ARB

Reputation: 295

SQL Query runs forever - SQL Server 2008

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

Upvotes: 0

Views: 1194

Answers (3)

Andriy M
Andriy M

Reputation: 77657

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

Upvotes: 1

Anthony Faull
Anthony Faull

Reputation: 17957

Try to avoid converting from datetime to varchar.

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

Upvotes: 1

Will A
Will A

Reputation: 24988

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

Upvotes: 0

Related Questions