Post reply

index-question

  • January 23, 2006 at 5:57 am

    #616400

    So both ID and Col1 are already indexed. If this is going to be a once off query of Col2 whrn don't bother indexing it.

    What's the data type of Col2? What kind of query are you doing on it (exact match, like,...?)

    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

  • January 23, 2006 at 10:20 am

    #616490

    ID is int, COL1 is char(14) and COL2 is char(16)

    my query is: select ID from TBL where COL1= @a or COL2= @a

    , where @a is a char(14) parameter.

  • January 23, 2006 at 3:38 pm

    #616550

    And just a note of caution here, it would be wise if you did not use langauge that might offend others (your post #33), especially when they are also trying to help you out!

  • January 23, 2006 at 11:24 pm

    #616571

    In that case I will stick with my earlier advice. If this is a once-off query and in the future you will only search by ID then don't put an index on Col2. The overhead of creating an index will nullify any gains you get from the query.

    If this query will be run in the future, then consider a NC index on Col2 and you should be fine (Since the other 2 columns are already indexed)

    There is no need to include ID in any of the NC indexes since, as the clustering key, it is already included in the indexes as the pointer to the data

    HTH

    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

  • January 24, 2006 at 12:08 am

    #616577

    uhum.

    thats good! i take your advice.

    thanks a lot G

  • January 25, 2006 at 1:48 am

    #616830

    A UNION might help get rid of the OR IE:

    select ID from TBL where COL1= @a UNION select ID from TBL where COL2= @a

    This may or may not speed it up, but will produce duplicates where @a is in both columns.

    Or possibly:

    select ID from TBL where NOT (COL1 <> @a AND COL2 <> @a)

    I think this one should work

    "Don`t try to engage my enthusiasm, I don`t have one."

    (Marvin)

  • January 26, 2006 at 10:56 pm

    #617363

    select ID from TBL where COL1= @a UNION select ID from TBL where COL2= @a

    That will result in one index seek (first query) and one table scan (second) plus a distinct sort (to remove duplicates. union removes duplicates, union all doesn't)

    It's highly unlikely that either of the above will be faster than a simple OR. Especially since the second is logically identical to an OR

    You can get an estimated execution plan to be sure.

    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

Viewing 7 posts - 16 through 21 (of 21 total)

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