SQL Server Performance

Strange performance issue with UPDATE FROM

Discussion in 'T-SQL Performance Tuning for Developers' started by crassy303, Jun 26, 2007.

  1. crassy303 New Member

    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!
  2. Adriaan New Member

    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
  3. crassy303 New Member

    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
    )

  4. Adriaan New Member

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

    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.
  6. Chappy New Member

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

Share This Page