|
|
Posted by Erland Sommarskog on 06/26/07 21:46
Richard (nassegris@gmail.com) writes:
> 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?
One problem with UPDATE FROM is that you can update the same row
several times if your join conditions are not unique. What happens if
you run:
UPDATE a
SET Field = (SELECT c.Field1
FROM c
JOIN b ON c.GUID2 = b.GUID2
WHERE a.GUID1 = b.GUID1
AND c.type = 1
AND @date BETWEEN b.DateFrom AND b.DateTo)
I'm most interested to know if the query succeds at all, or if it fails
with an error message. From the table sizes you have indicated, I would
expect an error, but I don't know how your tables are related.
As for the performance, investigating the query plan can give some ideas.
Without seeing query plans, the table definitions, the indexes etc,
it's difficult to say much useful.
Which version of SQL Server are you using?
Is @date a parameter to a stored procedure or a local variable?
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
[Back to original message]
|