Optimizing an index for select count(distinct)

• noeld

  SSC Guru

  Points: 96590

  More actions

  June 18, 2008 at 10:24 am

  #830616

  stephane3d (6/18/2008)

  ---

  No, I didn't consider that actually.

  Can an indexed view be faster than an index on the table itself?

  Thanks

  Stephane

  If it has "pre"-aggregated data, you bet!!!

  
  * Noel

• stephane3d

  Ten Centuries

  Points: 1129

  More actions

  June 18, 2008 at 11:19 am

  #830652

  Hum... I think I miss a thing here.

  If I have an indexed view, should I get rid of any indexes on the table? Is the performance cost on the insert is minimum? And why an indexed view would be faster?

  Thanks

  Stephane

• noeld

  SSC Guru

  Points: 96590

  More actions

  June 18, 2008 at 11:45 am

  #830663

  stephane3d (6/18/2008)

  ---

  Hum... I think I miss a thing here.

  If I have an indexed view, should I get rid of any indexes on the table? Is the performance cost on the insert is minimum? And why an indexed view would be faster?

  Thanks

  Stephane

  1. You could get rid of the indexes but PK should survive.

  2. No such thing as a free lunch. It *will* slowdown your inserts. It is all a matter where your weakest link is. If you run the aggregation frequently enough is worth it, if you don't you are probably ok.

  3. It is faster because you will have only as many rows in that clustered index as the GROUP BY column combinations. So it could be blindly fast !!!

  Hope it helps.

  
  * Noel