Strange performance issue with UPDATE FROM

Hello!

I have this piece of SQL code:


UPDATE a
SET Field1 = c.Field1
FROM a
INNER JOIN b ON a.GUID1 = b.GUID1
INNER JOIN c ON b.GUID2 = c.GUID2
WHERE c.Type = 1
AND @date BETWEEN b.DateFrom AND b.DateTo

This query takes hours to complete.

Now while trying to find out what's causing the poor performance (it surely looks simple enough!) I've rewritten it to use temp tables:



SELECT a.GUID1, a.Field1, c.Type, b.DateFrom, b.DateTo
INTO #temptable
FROM a
INNER JOIN b
ON a.GUID1 = b.GUID1
INNER JOIN c
ON b.GUID2 = c.GUID2
WHERE c.Type = 1
AND @date BETWEEN b.DateFrom AND b.DateTo




UPDATE a
SET Field1 = subsel.Field1
FROM (SELECT * FROM #temptable) AS subsel
WHERE subsel.GUID1 = a.GUID1


Now it completes in 10 seconds.

My question is why? Am I wrong in saying that the two batches above produce same results? Is there something I've missed about the UPDATE FROM syntax? Why would the first query perform THAT poorly?

Table sizes:
a: 24k rows
b: 268k rows
c: 260k rows

GUIDs are of type uniqueidentifier.

Any answers appreciated!

GUIDs are not the nicest key values to work with - they're all over the place, alphabetically speaking, and SQL Server has to sort them ... And you're doing joins on two different sets of GUIDs ...

FROM a
INNER JOIN b ON a.GUID1 = b.GUID1
INNER JOIN c ON b.GUID2 = c.GUID2
WHERE c.Type = 1
AND @date BETWEEN b.DateFrom AND b.DateTo

Perhaps you can use b and c as a derived table with criteria:

UPDATE a
SET Field1 = X.Field1
FROM a
INNER JOIN
(SELECT b.GUID1, c.Field1
FROM b INNER JOIN c ON b.GUID2 = c.GUID2
WHERE c.Type = 1 AND @date BETWEEN b.DateFrom AND b.DateTo) X
ON a.GUID1 = X.GUID1

Is there any way to optimize those GUID joins? The concept of having GUIDs as keys is all around the application I'm working with, and there is nothing I can do about that...

A simplified code piece to show what I'm dealing with here:



FROM z
INNER JOIN a ON ..GUID = ..GUID
INNER JOIN b ON ..GUID = ..GUID
INNER JOIN c ON ..GUID = ..GUID
INNER JOIN d ON ..GUID = ..GUID
LEFT OUTER JOIN e
ON ..GUID = ..GUID
AND VERSION =
(
SELECT MAX(VERSION) FROM f
WHERE ..GUID = ..GUID
)

Pre-joining with a derived table may help, which is similar to what your temp table approach did.

By any chance, do the tables also have an identity column? If you could repeat the identity column as an FK on the other table, then you can join on the identity/int columns instead of the GUIDs.

What indices are on the tables? Make sure the guids have at least a non clustered index on them