How can this query be rewriten?

  • I'm pretty sure this query can be optimized, but I don't know how. What is a better way of doing this? The goal is to get two counts from Table2 and populate them in Table1 columns.

    UPDATE Table1

    SET

    Count1 =

    (

    SELECT CASE WHEN COUNT(*) = 0 THEN NULL ELSE COUNT(*) END

    FROM Table2 (nolock)

    WHERE Col1 = Table1.ID

    )

    ,

    Count2 =

    (

    SELECT CASE WHEN COUNT(*) = 0 THEN NULL ELSE COUNT(*) END

    FROM Table2 with (nolock)

    WHERE Col2 = Table1.ID

    )

    WHERE

    timestamp BETWEEN @FromDate AND @ToDate

    AND (Table1.ClientID = @ClientID)

  • programmersunlimited (6/3/2011)


    I'm pretty sure this query can be optimized, but I don't know how. What is a better way of doing this? The goal is to get two counts from Table2 and populate them in Table1 columns.

    UPDATE Table1

    SET

    Count1 =

    (

    SELECT CASE WHEN COUNT(*) = 0 THEN NULL ELSE COUNT(*) END

    FROM Table2 (nolock)

    WHERE Col1 = Table1.ID

    )

    ,

    Count2 =

    (

    SELECT CASE WHEN COUNT(*) = 0 THEN NULL ELSE COUNT(*) END

    FROM Table2 with (nolock)

    WHERE Col2 = Table1.ID

    )

    WHERE

    timestamp BETWEEN @FromDate AND @ToDate

    AND (Table1.ClientID = @ClientID)

    Whats Table1.ClientID cardinality?

    Is this query supposed to update a single row in Table1?

    Do you have an index on Table1.ClientID?

    What's the cardinality on Table2.Col1 and Table2.Col2?

    Do you have indexes on Table2.Col1 and Table2.Col2?

    Last but not least, what a trace is telling you? where is query waiting the most?

    _____________________________________
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at Amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
  • As Paul said, check for indexes. Especially for two-column index on Table1( ClientID, Timestamp) or (Timestamp, ClientID) - test for both cases and measure logical reads. Experiment with cluster index and not having clustered index at all on that table.

    If number of rows in Table2 is not very high, you can try this:

    -- SELECT *

    UPDATE t SET t.Count1 = c1.RowCount, t.Count2 = c2.RowCount

    FROM Table1 t

    JOIN

    (SELECT Col = t2.Col1, RowCount = NULLIF(COUNT(*),0)

    FROM Table2 t2

    GROUP BY t2.Col1

    ) c1 ON c1.Col = t.ID

    (SELECT Col = t2.Col2, RowCount = NULLIF(COUNT(*),0)

    FROM Table2 t2

    GROUP BY t2.Col2

    ) c2 ON c2.Col = t.ID

    WHERE t.timestamp BETWEEN @FromDate AND @ToDate

    AND t.ClientID = @ClientID

    I could play with "GROUP BY t2.Col1, t2.Col2 WITH CUBE" and have just one join, but let's start simple (you can try that yourself and measure).

    Your version executes counting select-s per each row of the outer table. This version counts for all id's, and then joins. You should get different execution plan (not necessarily better).

    Compare logical reads with the previous version of the command with e.g. "SET STATISTICS IO ON" to see which one is better.

    Avoid "nolock" hint. NOLOCK can give you incorrect results (reading phantom data, uncommitted rows from other transactions that may be rollbacked and never really enter the table).

    Good luck!

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply