how to improve performance of 'LEFT JOIN'

From: "lelandhuang@xxxxxxxxx" <lelandhuang@xxxxxxxxx>
Date: 17 May 2006 23:37:03 -0700

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
--------------------------------------------------------------------------

Can anyone give me some suggestion?
Thanks a lot.

Leland Huang

.

Follow-Ups:
- Re: how to improve performance of 'LEFT JOIN'
  - From: Erland Sommarskog
- Re: how to improve performance of 'LEFT JOIN'
  - From: Tom Moreau

Prev by Date: Re: SELECT and regular expression
Next by Date: Re: Your Professional Advice Please - Design
Previous by thread: Re: Need a So-Called SSN Encryption
Next by thread: Re: how to improve performance of 'LEFT JOIN'
Index(es):
- Date
- Thread

Relevant Pages

Re: Query/Table term confusion
... --lets assume freight as an order amount on the northwind sample database. ... select OrderId, OrderDate, O.freight ... The O is an alias given to the outer table of the query. ... the subquery and the outer query alias is used. ...
(microsoft.public.sqlserver.mseq)
Re: ORDER BY a SubSelect?
... >I tried duplicating the entire subquery in the order by clauase and ... The statement you put together with the outer ...
(comp.databases.ms-access)
Re: Exists
... The correlation you're looking for is between a column in the table1 outer ... query and a column in the table2 subquery. ... > relate to the outer query? ...
(microsoft.public.sqlserver.programming)
Re: ORDER BY a SubSelect?
... I tried duplicating the entire subquery in the order by clauase and ... The statement you put together with the outer ...
(comp.databases.ms-access)
Re: Frustrated Join?
... I tried Googling for 'Frustrated Join' and 'Frustrated Outer ... And, of course, references to some of Allen Browne's post to these news ... > Chaim wrote: ... > FROM TableA Left Outer Join TableB ...
(microsoft.public.access.gettingstarted)