By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,648 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

how to improve performance of 'LEFT JOIN'

P: n/a
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

May 18 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Be careful. Your LEFT JOIN is actually an INNER JOIN in disguise (as well
as having a syntax error). Try:

SELECT *
FROM TableA
LEFT OUTER JOIN TableB
ON TableA.item1 = TableB.item1
AND TableB.item2 > yyyy AND TableB.item2 < zzzz
WHERE TableA.item2 = 'xxxx'

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada

<le*********@gmail.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...
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
May 18 '06 #2

P: n/a
le*********@gmail.com (le*********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
May 19 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.