Optimising a sql correlated subquery statement

Question

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.

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

• storabjorn: The join is interesting - why would the optimiser do better with the join?. Is it because you've specified 'inner' rather than 'left'?
• JNK: @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.