How to Optimize this SQL query of finding Duplicates for Better Database performance

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 9, 2015 at 8:19 am

  #1769102

  muralikrishna2489 (1/8/2015)

  ---

  Hi Laxmi,

  Thanks for the suggestion.

  I am not inserting anything into the table, I just need to Find the count of duplicates. Please tell why I need to use insert statements.

  Probably because of this:

  muralikrishna2489 (1/6/2015)

  ---

  ...The work involves is every week, data from Changes_DupCk table has to be loaded into dbo.Changes and Changes_Frze table. But before loading we have to make sure that dbo.Changes_DupCk table has no dupicate record which is already present in one of the other two tables. ...

  But what does this mean? One interpretation is "Check for dupes in Changes_DupCk (where a dupe is defined as already present in either dbo.Changes and Changes_Frze) - if there are any, don't load any of the rows."

  A more sensible statement might be "Load every "new" row from Changes_DupCk into both dbo.Changes and Changes_Frze, where "new" means the row is not already present in either table".

  What you want to do is trivial - it's just not very clear what you want.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden