|
|
Posted by Erland Sommarskog on 05/20/06 00:52
lelandhuang@gmail.com (lelandhuang@gmail.com) writes:
> I am developing reporting service and using lots of 'LEFT OUTER JOIN',
> I am worried about the performance and want to use some subquery to
> improve
> the performance.
> Could I do that like below,
>
> [the origin source]
> SELECT *
> FROM TableA
> LEFT OUTER JOIN TableB
> ON TableA.item1 = TableB.item1
> WHERE TableA.item2 = 'xxxx'
> TableB.item2 > yyyy AND TableB.item2 < zzzz
>
> I add the subquery to query every table before 'LEFT JOIN'
> --------------------------------------------------------------------------
> SELECT *
> FROM
> (SELECT *
> FROM TableA
> WHERE TableA.item2 = 'xxxx'
> ) TableC
> LEFT OUTER JOIN
> (SELECT *
> FROM TableB
> WHERE TableB.item2 > yyyy AND TableB.item2 < zzzz
> ) TableD
> ON TableC.item1 = TableD.item1
> WHERE TableC.item2 = 'xxxx'
> TableD.item2 > yyyy AND TableD.item2 < zzzz
> --------------------------------------------------------------------------
This is a meaningless rewrite of the query, that at worst could server
to confuse the optimizer to give you a worse query plan. At best, the
optimizer will recast the second query into the first.
As Tom notes, the outer join is probably not correctly written. Assuming
that the query should read:
SELECT *
FROM TableA
LEFT OUTER JOIN TableB
ON TableA.item1 = TableB.item1
AND TableB.item2 > yyyy AND TableB.item2 < zzz
WHERE TableA.item2 = 'xxxx'
The most important for the query to perform well, is that you have a
clustered index on TableA.item2 and an index (clustered or non-clustered)
on TableB.item1.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|