June 3, 2011 at 2:03 pm
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)
June 3, 2011 at 3:26 pm
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.June 3, 2011 at 4:26 pm
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!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply