5

I want to speed up following piece of code

delete from ssn_sdo
where       
           (art_id=@art_id and skl_id=@skl_id and @level=0 )
        or (art_id=@art_id and skl_id=@skl_id and @level=1 and tip=@tip)
        or (art_id=@art_id and skl_id=@skl_id and doc_id = @doc_id)

My server is MS SQL-Server 2005.

art_id,skl_id and doc_id are integers, while tip is varchar(10) type of.

I want to make an index(es) on ssn_sdo table so this delete is going to faster.
What I was considering to do is to make three indexes, each one for every case:

doc_id (ASC), art_id (ASC), skl_id (ASC)  
skl_id (ASC), art_id (ASC), tip (ASC)  
skl_id (ASC), art_id (ASC),  

Or is there better way to make one index which will include all three cases.
I am careful with indexes because I do not want to slow down inserts in this table.

7

One index on (art_id, skl_id, doc_id, tip) is most likely enough

All the conditions start (art_id, skl_id) which should give a seek, and then a residual lookup on either doc_id or tip will be done afterwards

The index could also be (skl_id, art_id, doc_id, tip) based on the number of unique values in each column: most uniqueness should be first

Multiple indexes will mean that the cached query plan may be suboptimal for different conditions

Of course, if one or both of art_id/skl_id is the clustered index already, then you may not need an index because it will seek on the clustered index and then do a residual. If doc_id is the clustered index then you need the extra index

8

Bit akward, I can't see how to comment but I wanted to ask why your condition isn't:

delete from ssn_sdo
where       
       art_id=@art_id 
       and skl_id=@skl_id
       and (@level = 0 or (@level = 1 and tip=@tip) or doc_id = @doc_id)

Not sure that helps at all, before or after you add any further indexes, but it'd interesting to see the executions plans (actual) and see if any benefit is realised.

  • 3
    +1 This makes my suggested index and answer a lot more obvious. – gbn Jul 4 '13 at 9:36
  • Thanks, and for anyone reading it turns out I needed 50 reputation in order to comment. Little akward, but there now – Paul Jul 4 '13 at 9:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.