Prev: Update a value in an XML column
Next: Index Rebuild with ONLINE = ON causes major blocking
From: tshad on 31 Mar 2010 18:26
If you have an update using an inner join of a derived table, does an ORDER
BY in the derived table help you or slow you down.

For example if you had something like:

UPDATE Employees
SET Names = ,
Addresses =
FROM ##GobalTempTable gtt
INNER JOIN
(
SELECT TOP 1000
FROM ##GlobalTempTable gtt2
INNER JOIN vwGlobalEmployees ge on ge.EmployeeID = gtt2.EmployeeID
WHERE CustID = @CustID
ORDER BY StatusID
) AS Emp
on gtt.ID = emp.ID

Would the above work better if you took out the "ORDER BY StatusID"?

Thanks,

Tom


From: Plamen Ratchev on 31 Mar 2010 18:34
Two things:

1). Yes, ORDER BY will slow down as it requires a sort (this may depend if you have an index on the column)
2). The query will be different without ORDER BY as TOP will be non deterministic and can return any 1000 rows

--
Plamen Ratchev
http://www.SQLStudio.com
From: Plamen Ratchev on 31 Mar 2010 18:41
One more comment: I do not see the target Employees table in the FROM clause. While the query may work it is a good
practice when using the SQL Server specific UPDATE with FROM clause to include the table in FROM.

--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 31 Mar 2010 18:49

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:q_qdnaJBq8tjUC7WnZ2dnUVZ_qWdnZ2d(a)speakeasy.net...
> Two things:
>
> 1). Yes, ORDER BY will slow down as it requires a sort (this may depend if
> you have an index on the column)

Why would that be the case?

The result set wouldn't be in the order of StatusID - which I assume is why
they have the ORDER BY.

But if the TOP statement were removed, I assume that the ORDER BY would
slow down the querys unnecessarily.

This procedure actually has about7 of these statements in it and they all do
the ORDER BY.

> 2). The query will be different without ORDER BY as TOP will be non
> deterministic and can return any 1000 rows
True.

Thanks,

Tom


From: Plamen Ratchev on 31 Mar 2010 19:01
tshad wrote:
> Why would that be the case?
>
> The result set wouldn't be in the order of StatusID - which I assume is why
> they have the ORDER BY.

ORDER BY still needs to sort in order to provide the TOP values.

>
> But if the TOP statement were removed, I assume that the ORDER BY would
> slow down the querys unnecessarily.

No, you will get an error. A table expression cannot be ordered set, and you cannot use ORDER BY without the TOP option.

--
Plamen Ratchev
http://www.SQLStudio.com
 |  Next  |  Last
Pages: 1 2 3
Prev: Update a value in an XML column
Next: Index Rebuild with ONLINE = ON causes major blocking