Roy Harvey
3/29/2007 2:13:00 PM
In the first version the WHERE clause EXISTS test is limiting which
rows are updated. In the second there is no WHERE clause so if the
UPDATE runs at all, every row in the table is updated. The second
approach can not reproduce the first without adding back in the very
WHERE clause test you removed.
Uri already questioned the GROUP BY in the first version. GROUP BY is
not needed, as all three columns have already been matched to the row
being updated. The GROUP BY clause and related column references can
simply be removed.
However, I do not expect that to impact performance.
There is an alternate syntax you might try that uses a FROM clause in
the UPDATE, and a derived table. Note that FROM is not part of the
standard UPDATE command, and has the potential for logical
inconsitencies when not used properly, but it is commonly used in this
sort of situation.
UPDATE TABTHREE
SET QTY = UpdateQty
FROM (SELECT SUM(QTY) as UpdateQty
FROM TABONE
WHERE TABONE.Col1 = TABTHREE.Col1
AND TABONE.Col2 = TABTHREE.Col2
AND TABONE.Col3 = TABTHREE.Col3
GROUP BY
TABONE.Col1,
TABONE.Col2,
TABONE.Col3) as X
WHERE X.Col1 = TABTHREE.Col1
AND X.Col2 = TABTHREE.Col2
AND X.Col3 = TABTHREE.Col3
Sometimes this approach performs a bit better than the approach used
in your first alternative.
Roy Harvey
Beacon Falls, CT
On Thu, 29 Mar 2007 06:30:01 -0700, Pradeep
<Pradeep@discussions.microsoft.com> wrote:
>Hello,
>
>I would appreciate if any one could help me in the following case.
>
>The following query works fine but intermittently returns TimeOut expired
>Error:
>
>UPDATE TABTHREE
>SET QTY =
>(
>SELECT SUM(QTY)
>FROM TABONE
>WHERE
>TABONE.Col1 = TABTHREE.Col1 AND
>TABONE.Col2 = TABTHREE.Col2 AND
>TABONE.Col3 = TABTHREE.Col3
>GROUP BY
>TABONE.Col1,
>TABONE.Col2,
>TABONE.Col3
>)
>WHERE EXISTS
>(
>SELECT 1
>FROM TABONE
>WHERE
>TABONE.Col1 = TABTHREE.Col1 AND
>TABONE.Col2 = TABTHREE.Col2 AND
>TABONE.Col3 = TABTHREE.Col3
>)
>GO
>
>I tried to change it the following to see if tables wont get locked and i
>wont get the
>TimeOut expired error:
>
>IF EXISTS(
>SELECT 1
>FROM TABONE ,TABTHREE
>WHERE
>TABONE.Col1 = TABTHREE.Col1 AND
>TABONE.Col2 = TABTHREE.Col2 AND
>TABONE.Col3 = TABTHREE.Col3
>) UPDATE TABTHREE SET QTY =(
>SELECT SUM(QTY)
>FROM TABONE
>WHERE
>TABONE.Col1 = TABTHREE.Col1 AND
>TABONE.Col2 = TABTHREE.Col2 AND
>TABONE.Col3 = TABTHREE.Col3
>GROUP BY
>TABONE.Col1,
>TABONE.Col2,
>TABONE.Col3
>)
>
>This one is working well but does not return the same results. I would
>appreciate if someone could help me in rewriting the above query in the below
>form.
>
>Thanks for the help.
>
>Cheers,
>
>Pradeep