Posted by Alex Kuznetsov on 06/28/07 21:12

On Jun 26, 10:31 am, Richard <nasseg...@gmail.com> wrote:
> 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!
>
> Regards,
> // Richard

Not arguing with other party on correctness/standards etc., when you
create a temp table, you get statistics on it. So them optimizer has a
better estimate of number of rows to modify and may choose a better
plan.

http://sqlserver-tips.blogspot.com/

[Back to original message]