Post reply

To Index or not

  • February 15, 2011 at 1:27 am

    #234535

    Hi everyone, not sure if this is the correct sub forum, but here goes.

    I have a table consisting of about 13 million records, there is a column called Product Class.

    There are a lot of queries that request a certain product class to be returned, my question is, is it best practice to create an index on the column? The reason I ask is that there are only 240 unique product classes spread across the 13 million records. To my knowledge, indexes should not be used of the values are mostly the same?

    Any advice on how to improve performance when requesting that column in a WHERE clause?

    Cheers

  • February 15, 2011 at 1:53 am

    #1286898

    table structure please

  • February 15, 2011 at 2:01 am

    #1286903

    [ISBN13] [varchar](13)

    [TP] [varchar](9)

    [TL] [text]

    [NBSPC] [varchar](50)

    [RPG] [varchar](200)

    The field in question is the NBSPC field.

  • February 15, 2011 at 2:02 am

    #1286905

    Indexes can be very useful on columns that have lots of repeated values. The trick is, nonclustered covering 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

  • February 15, 2011 at 2:04 am

    #1286906

    Please post query, index definitions and execution plan.

    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

    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

  • February 15, 2011 at 2:07 am

    #1286910

    Thanks Gila, I was on the right path then, just needed some confirmation.

    my index I created looks like this:

    CREATE INDEX idx_PC ON nbs_sa_4740 (NBSPC)

    INCLUDE (ISBN13, TL)

    thanks

  • February 15, 2011 at 2:19 am

    #1286917

    Not necessarily a good idea.

    TL is a text column (should be changed to varchar(max) as text is deprecated). If you include that, you are duplicating the entire column. The entire text column will be stored twice, once in the table, once in the index. That could be a very large waste of space.

    How big are the values in TL in general?

    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

  • February 15, 2011 at 2:25 am

    #1286921

    that makes sense, didn't think of that.

    They can be quite long, but I doubt it is that large, the table is part of a legacy system developed yonks ago. Will do some investigating and change the field size accordingly.

  • February 15, 2011 at 5:21 am

    #1286976

    wow! the average size is 90-120 chars with the max being 290 chars, why would they have made it a text field.

    anyways, will run the ALTER TABLE statement when everyone has finished working.

  • February 15, 2011 at 5:30 am

    #1286978

    Lol. Varchar(300) methinks?

    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

  • February 15, 2011 at 5:33 am

    #1286980

    yip, I made it varchar(320)

    also going through the other bib tables to make sure there aren't other unnecessary uses of text fields

Viewing 11 posts - 1 through 10 (of 10 total)

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