0

I have encountered a SQL Server query along the following lines: 

UPDATE R SET R.fieldX = C.fieldX,
R.fieldY = C.fieldY,
R.fieldZ = C.fieldZ,
...etc, about 20 field assignments....
FROM TableA R JOIN TableB T ON T.fieldA = R.fieldA AND T.fieldB = R.fieldB JOIN tableC C ON T.fieldC = C.fieldC  AND T.fieldD = C.fieldD AND R.fieldE = C.fieldE AND R.fieldF = C.FieldF WHERE T.fieldE = 0 AND R.FieldE = 0

The self-referential nature of it (updating a table that is participating in a multiple-table join) looks suspicious to me, and whilst it works, where the tables have large numbers of rows it consumes a huge amount of disk space before completing (if it completes at all before running out of drive space).

The purpose of the query is to transfer data from the various joined tables to the target table.

Is there a more efficient way of accomplishing this?

haughtonomous
  • 4,602
  • 11
  • 34
  • 52
  • You're right to be suspicious, in the sense that while this is legal, the results are only well-defined if rows from `TableA` are not repeated in the result set. That is, the `JOIN` must be one-to-one, otherwise the outcome is not defined. If that *is* the case, though, there's nothing principally wrong with the query, and optimizing it should follow the usual steps for optimizing queries (that is, start by getting and studying the execution plan). (And if that's *not* the case, the problem may be inefficient, redundant updating of rows.) – Jeroen Mostert Mar 22 '18 at 11:13
  • Do u have constraints on the table ? – Ven Mar 22 '18 at 12:24
  • You might try solving all the joins first and using `SELECT INTO` on a temporary table, then indexing it and finally joining with the target table just for the update. If it's a long running query, you might be locking your target table a for long time. – EzLo Mar 22 '18 at 12:35
  • ELP, that sounds worth a look into, but locking the table for a long time isn't itself an issue because this is part of a system update process, when nobody else will be using the system. – haughtonomous Mar 22 '18 at 16:19
  • Ven, only the primary key constraint, as far as I am aware. – haughtonomous Mar 22 '18 at 16:21
  • JM, thanks, I'll look at the execution plan to see if that tells us anything. – haughtonomous Mar 22 '18 at 16:21

0 Answers0