Query too slow to show missing child records??

• Laurie Thompson

  SSC Enthusiast

  Points: 165

  More actions

  July 29, 2008 at 3:58 am

  #70971

  Good Morning,

  I'd like to create a query that shows all the missing records in a related child table. However, both tables have over 3 millions records and executing the SQL statement below takes well over two minutes. Is there anyway of creating a faster query ?

  SELECT [Table1].PrimaryKeyID

  FROM [Table1] LEFT OUTER JOIN

  [Table2] ON [Table1].PrimaryKeyID = [Table2].ForeignKeyID

  WHERE ([Table2].ForeignKeyID IS NULL)

  Kind recgards

  Laurence Thompson

• ChiragNS

  One Orange Chip

  Points: 26137

  More actions

  July 29, 2008 at 4:14 am

  #849658

  Another option is to use the ALL operator. You can use it as <> ALL. I dont know if its faster or not.

  "Keep Trying"

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  July 29, 2008 at 6:47 am

  #849730

  SELECT [Table1].PrimaryKeyID

  FROM [Table1]

  WHERE NOT EXISTS (select 1 from [Table2]

  where [Table1].PrimaryKeyID = [Table2].ForeignKeyID

  )

  _____________
  Code for TallyGenerator