Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

2 Answers 2

up vote 7 down vote accepted

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

share|improve this answer

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.

share|improve this answer
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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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