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 am updating a column on tableA with data from tableB like this:

update tableA
  set bKey = select bKey from tableB B where B.colB = tableA.colB

bKey has a unique index on tableB.

Which is the better optimisation for this query, indexing tableB on ColB or (ColB,bKey) and why?

I am interested in answers for any database.

share|improve this question

migrated from stackoverflow.com Feb 13 '12 at 16:05

This question came from our site for professional and enthusiast programmers.

1 Answer 1

Neither.

Index ColB INCLUDE (bKey)

Since you aren't filtering on bKey you don't need it at all the nodes of the index, just the leaf level.

If you only index on ColB then you will still have to pay for a key lookup to get the value of bKey from the clustered index.

Also is there a reason this isn't using a JOIN instead of a correlated subquery?

UPDATE a
SET a.bKey = b.bKey
FROM TableA A
INNER JOIN TableB B
ON A.ColB = B.ColB
share|improve this answer
    
The join is interesting - why would the optimiser do better with the join?. Is it because you've specified 'inner' rather than 'left'? –  storabjorn Aug 22 '11 at 11:08
    
@Stora - no, but depending on the implementation of SQL (sql server, MySQL, etc) and version the subquery may be executed once per row instead of as a set, while the JOIN will always be a set-based operation. –  JNK Aug 22 '11 at 12:21

Your Answer

 
discard

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