Prev: TOP clause
Next: Depending on input parameters, modify select statement in SP
From: tshad on 6 Apr 2010 21:54
Just trying to see why someone would inner join on itself vs just do one
inner join. It looks like this is being done to accommodate paging but not
sure if that is the complete reason. In the original one, there was also an
ORDER BY Clause that went with the TOP 10000 clause. But they took that
out.

If you have something like:

UPDATE TableA
SET TableA.Description = TableD.Description
FROM ##TableGT B
INNER JOIN
(
SELECT TOP 10000 C.ID,vw.Name
FROM @@TempGT C
INNER JOIN vwCustomer vw
ON vwAView.ExternalID = C.ID AND vw.CatID = C.ID
WHERE C.session = @session AND C.batchID = @batchTimeStamp and C.ID >=
@StartRow and C.ID <= @EndRow
) AS TableD
ON TableA.ID = TableD.ID

Couldn't you just write this same type of query without using the derived
table and INNER JOIN on vwAView directly?

Or is there some reason you would have to do this with a derived table.

I didn't write this but am trying figure out why this person did it this way
(the TOP 10000 is unneeded as far as I can tell). I think what he was doing
we some type of paging (this is set up on an SQL 2000 database) where 10000
is a number they would never hit.

I am looking at changing it to use ROW_NUMBER() ordering on the ID column.
But I am not sure why if they are doing an UPDATE, why they are doing them
in batches of rows. This is a batch processes.

If there is no valid reason for doing batches (paging), this looks like the
same thing and wouldn't it be faster (ignoring the paging)?

UPDATE TableA
SET TableA.Description = B.Description
FROM ##TableGT B
INNER JOIN vwCustomer vw
ON vwAView.ExternalID = B.ID AND vw.CatID = B.ID
WHERE B.session = @session AND B.batchID = @batchTimeStamp

I could also do:

UPDATE TableA
SET TableA.Description = B.Description
FROM ##TableGT B
INNER JOIN vwCustomer vw
ON vwAView.ExternalID = B.ID AND vw.CatID = B.ID
WHERE B.session = @session AND B.batchID = @batchTimeStamp AND B.ID
BETWEEN @StartRow AND @EndRow

If they need paging for some reason then I would do something like the
following, although I don't know if it would be better than that previous
one which is doing essentially the same thing except it is doing the Sort
with the ORDER BY.

UPDATE TableA
SET TableA.Description = PagedResults.Description
FROM (
SELECT B.Description,
ROW_NUMBER() OVER (ORDER BY B.ID) AS
ResultSetRowNumber
FROM ##TableGT B
INNER JOIN vwCustomer vw
ON vwAView.ExternalID = B.ID AND vw.CatID = B.ID
WHERE B.session = @session AND B.batchID = @batchTimeStamp
) as PagedResults
WHERE ResultSetRowNumber BETWEEN @StartRow AND @EndRow

These would all be equivelant, wouldn't they?

The 3rd one should run the fastest, I would think, paged and without the 2nd
Inner Join.

Which one I use would depend on whether they really need filter by row
(which is really just an ID number).

Mainly, I wanted to see if I needed the 2nd INNER JOIN or not. This
procedure has about 10 updates like this except for joining with different
tables but all do 2 inner joins.

Thanks,

Tom


 | 
Pages: 1
Prev: TOP clause
Next: Depending on input parameters, modify select statement in SP