Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110

    Unanswered: Column order in Indexes

    Hey all,

    When creating nonclustered indexes do the order of the columns make any difference to speed? My particular circumstance is similar to the following:

    Table A
    col1 id
    col2 datetime
    col3 smallint
    col4 varchar
    col5 ....

    The unique clustered index is on the id column.

    slow query:
    delete A where col2 < dateadd(day,-65,getdate()) and smallint not in (1,3,5,7)

    A possible index could be on col2, col3.. Would this improve the delete, mostly likely yes if the current query is performing a table scan? And would col3, col2 be better or worse than col2, col3? If anything at all I would imagine worse?

    Any ideas? I'm sure this type of question would have been covered before, however I was unable to find any threads..

    Thanks,

  2. #2
    Join Date
    Sep 2002
    Location
    Hong Kong
    Posts
    159
    Personally I would pre-calculate the date arithmetic and store that away in a variable. You might need again it later and particularly with getdate(), it's probably a good idea to call it once and reuse within the stored procedure. What happens if your script/proc runs over midnight? - Different dates!

    Generally I would put the most selective column first and the second most selective as the second column and so on. That's general principal. The actual answer you need is create both indexes and try it out. There's no need to execute the delete statement, just use "set showplan on" and "set noexec on" and let the optimiser decide which one it likes the best.

    create index a_1 on Table_A ( col2, col3)
    go
    create index a_2 on Table_A ( col3, col2)
    go

    set showplan on
    go
    set noexec on
    go
    delete A where col2 < dateadd(day,-65,getdate()) and smallint not in (1,3,5,7)
    go

    HTH.

  3. #3
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110
    Many thanks.. I'll let you know..

  4. #4
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110
    FYI, the date column had to go first.. The optimiser chose a table scan over the index in which the date was second.

    Thanx

  5. #5
    Join Date
    Jan 2004
    Posts
    51
    How many rows were in your table?

  6. #6
    Join Date
    Feb 2003
    Location
    Brisbane, Australia
    Posts
    110
    I was running this on a test server only (at this stage) and that only had a few thousand rows.. I need to time the index creation before running it production, so I will do all the same steps again once I get a copy of the production table. I'll let you know if the results change.

    Thanks,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •