Prev: Update a value in an XML column
Next: Index Rebuild with ONLINE = ON causes major blocking
From: tshad on 9 Apr 2010 00:20
Another question using this query.

I don't think this is the case but the question came up today.

In the following query, even though the INNER JOIN has a query that is
ordered by the StatusID, it doesn't process the query in the order of that
new table does it?

This table has a status ID of 1, 2 or 3. And the original designer did this
to make sure that the StatusID = 1 would be processed first and then the
StatusID =2 and then StatusID = 3.

I think that it may order the table to be displayed in that order but when
it joined to another table it is like any other table (which is in tempDB)
and is processed in the order the system wants to process it. Order doesn't
play into this scenario.

Later, what they did was create 3 identical queries where the WHERE clause
would be StatisID = 1 for the 1st query, StatisID = 2 for the 2nd and
StatisID = 3 for the 3rd.

Tom

"tshad" <tfs(a)dslextreme.com> wrote in message
news:ur4AoES0KHA.3652(a)TK2MSFTNGP04.phx.gbl...
> 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 9 Apr 2010 09:54
tshad wrote:
> In the following query, even though the INNER JOIN has a query that is
> ordered by the StatusID, it doesn't process the query in the order of that
> new table does it?
>

There is no order of rows in the subquery. The ORDER BY clause is used only to define the top rows for the TOP operator,
not to order the rows. The derived table is a table expression and as such it is unordered set.

Also, the update is a set operation, not row by row, so processing one status first and then next does not make sense.

--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 9 Apr 2010 12:26

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:Ft2dneMhMc7grCLWnZ2dnUVZ_rCsnZ2d(a)speakeasy.net...
> tshad wrote:
>> In the following query, even though the INNER JOIN has a query that is
>> ordered by the StatusID, it doesn't process the query in the order of
>> that new table does it?
>>
>
> There is no order of rows in the subquery. The ORDER BY clause is used
> only to define the top rows for the TOP operator, not to order the rows.
> The derived table is a table expression and as such it is unordered set.
>
But doesn't it need to order it to get the right top rows. Say the TOP was
TOP 1, we would want the first and if ordered by in desc order, we would
want the last in an ordered set. Are you saying that isn't the case?

> Also, the update is a set operation, not row by row, so processing one
> status first and then next does not make sense.
>

I agree and was what I thought, which was why they decided to change it to 3
query operations so they would be done in order.

Also,

In this case, the plan would be to get the derived table once (with its
join) then do the 2nd inner join on the 2 tables (the global table and the
derived table) - right?

Thanks,

Tom

> --
> Plamen Ratchev
> http://www.SQLStudio.com


From: Plamen Ratchev on 9 Apr 2010 12:40
tshad wrote:
>> There is no order of rows in the subquery. The ORDER BY clause is used
>> only to define the top rows for the TOP operator, not to order the rows.
>> The derived table is a table expression and as such it is unordered set.
>>
> But doesn't it need to order it to get the right top rows. Say the TOP was
> TOP 1, we would want the first and if ordered by in desc order, we would
> want the last in an ordered set. Are you saying that isn't the case?
>

The order is only relevant for the TOP option, it does not guarantee ordering of the result set from the subquery.

>> Also, the update is a set operation, not row by row, so processing one
>> status first and then next does not make sense.
>>
>
> I agree and was what I thought, which was why they decided to change it to 3
> query operations so they would be done in order.
>
> Also,
>
> In this case, the plan would be to get the derived table once (with its
> join) then do the 2nd inner join on the 2 tables (the global table and the
> derived table) - right?
>
Look at the execution plan. The optimizer may decide to push step up or down the plan if it finds it more efficient.


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