qry options

• Tara-1044200

  SSCoach

  Points: 15951

  More actions

  June 11, 2013 at 10:56 am

  #273811

  I want to do this.

  select count(*) from

  (

  select col1,col2,......col18 from REVemployee.tbemp

  group by col1,col2,......col18 HAVING COUNT(*)>1

  )a

  is there a better way of doing the same for better proformance?

• Eugene Elutin

  SSC Guru

  Points: 59322

  More actions

  June 12, 2013 at 3:00 am

  #1623245

  Tara-1044200 (6/11/2013)

  ---

  I want to do this.

  select count(*) from

  (

  select col1,col2,......col18 from REVemployee.tbemp

  group by col1,col2,......col18 HAVING COUNT(*)>1

  )a

  is there a better way of doing the same for better proformance?

  Looks fine to me. But you also can try this:

  select count(*) from

  (

  select null a from REVemployee.tbemp

  group by col1,col2,......col18 HAVING COUNT(*)>1

  )a

  You might have slightly better performance.

  You can also find duplicates using windowed function, but this very likely to be much slower.

  _____________________________________________
  "The only true wisdom is in knowing you know nothing"
  "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
  (So many miracle inventions provided by MS to us...)

  How to post your question to get the best and quick help[/url]