Post reply

select distinct query

  • December 29, 2009 at 11:42 pm

    #143853

    Hi all,

    I have a table with around 30 millions records.

    Most of the query created is using select distinct statement.

    and most of the column will fall under "where " statement.

    What are the best countermeasure to overcome the performance issue?

    Creating index would help?

    However, index pages will grow a lot and db will increase dramatically.

    Please kindly advice.

  • December 30, 2009 at 12:08 am

    #1097331

    If u want the performance improvement an non clustered index is need on the columns you are using in where clause.

  • December 30, 2009 at 12:27 am

    #1097337

    Index creation is a workaround, is there any advice to have both performance improvement but with less db size increment?

    By the way, is it necessary for me to create clustered index?

  • December 30, 2009 at 1:13 am

    #1097344

    Read this series of articles on indexes:

    http://www.sqlservercentral.com/articles/Indexing/68439/

    BrainDonor

    Steve Hall
    Linkedin
    Blog Site

  • December 30, 2009 at 1:32 am

    #1097349

    golden-1131024 (12/30/2009)

    Index creation is a workaround, is there any advice to have both performance improvement but with less db size increment?

    A workaround?

    Indexing is the key to good performance. No indexes, you will have poor performance. Create indexes to support your workload, ensure that the queries can use the indexes

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 30, 2009 at 4:30 am

    #1097380

    Thanks all.

    How can I ensure the index is being used by the query?

    Through execution plan?

  • December 30, 2009 at 5:00 am

    #1097388

    Yup, the execution plan is the place to look.


    Clear Sky SQL
    My Blog[/url]

  • December 30, 2009 at 6:53 am

    #1097407

    Hi all,

    Thanks for the information provided.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply