Re: how to improve performance of 'LEFT JOIN'
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Fri, 19 May 2006 21:52:02 +0000 (UTC)
lelandhuang@xxxxxxxxx (lelandhuang@xxxxxxxxx) 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@xxxxxxxxxxxxx
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
.
- References:
- how to improve performance of 'LEFT JOIN'
- From: lelandhuang@xxxxxxxxx
- how to improve performance of 'LEFT JOIN'
- Prev by Date: Re: Numbering in SQL
- Next by Date: Re: JDBC connectivity with SQL Server
- Previous by thread: Re: how to improve performance of 'LEFT JOIN'
- Next by thread: Copying files between servers
- Index(es):
Relevant Pages
|
