Left outerjoin and query performance

Question

Post reply

Left outerjoin and query performance

ramana3327

SSCoach

Points: 19722
More actions
May 23, 2014 at 9:52 am

#396806

Hi,
I am working with a SP. I need to tune that. In that SP, they use several left outer joins and used filtering (where A.a=B.a and A.a=B.c or A.a is null and B.C=..).
We can't change the filtering. It may affect the business logic but I am trying to avoid the left outer joins.
If any one has experienced with this situation, I need advice how can we avoid left outer joins. I think we can use derived table or temp table to reduce the left outer joins.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

what you posted is a catch all query.

here's an excellent place to start:

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ramana3327 SSCoach Points: 19722 More actions · Answer 2

Hi Lowell,

Thanks for your reply. I am looking for the different one. I do have that where clauses but I am trying to avoid the left outer joins. I didn't find the good example for that.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

Lowell (5/23/2014)
what you posted is a catch all query.
here's an excellent place to start:
http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

What is posted isn't a catch-all query, at least not a typical one. I see no variables in the code posted.

OP: A properly constructed LEFT JOIN CANNOT be eliminated to just some form of INNER JOIN. You CAN change it to TWO queries - one that is an INNER JOIN and another that uses some type of NOT EXISTS check. Those two could be UNION ALLed together. I don't know that that will be more efficient - almost certainly not because you are hitting at least some if not most data pages twice.

Can you post the full query and what you wish to do and why? What are the actual performance problems you are seeing and what have you done to identify that these LEFT JOINs are the actual culprit?

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

ramana3327 SSCoach Points: 19722 More actions · Answer 4

Hey,

I can't post the query here but I can change the names and I will post that.

Lowell SSC Guru Points: 323518 More actions · Answer 5

TheSQLGuru (5/23/2014)

What is posted isn't a catch-all query, at least not a typical one. I see no variables in the code posted.

i'm under the impression that WHERE SomeColumn = SomeValue OR SomeColumn Is NULL is a catch all query syntax, so it doesn't matter if it's a variable or a column name, or am i missing something?

where A.a=B.a

and A.a=B.c

or A.a is null

Isn't that pretty close to Gails example?, ie

WHERE (ProductID = @product Or @product IS NULL)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

I don't consider them at all the same, or even that similar, but that could be just me. The variable one is subject to parameter sniffing issues and has obvious short-cut refactoring that the strictly column-based one isn't/cannot.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

ramana3327 SSCoach Points: 19722 More actions · Answer 7

Hi,

I am sorry.

Actually that filters are column names ColumnA in tableA=.. and or..

ramana3327 SSCoach Points: 19722 More actions · Answer 8

Hi,

I think I can use outer apply to substitute left outer join, but I am not sure do we need to give column name like join

Suppose in left outer join

select colm1,colum2

from TableA A

left outer join TableB B

on A.colm1=B.col2.

For this above query how to write outer apply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

Lowell (5/23/2014)
i'm under the impression that WHERE SomeColumn = SomeValue OR SomeColumn Is NULL is a catch all query syntax, so it doesn't matter if it's a variable or a column name

No. A 'catch-all' query is one where the where clause has predicates of the specific form (Column = Parameter OR Parameter = Constant)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

ramana3327 (5/25/2014)
Hi,
I think I can use outer apply to substitute left outer join, but I am not sure do we need to give column name like join
Suppose in left outer join
select colm1,colum2
from TableA A
left outer join TableB B
on A.colm1=B.col2.
For this above query how to write outer apply

Please don't post the same question in multiple places

http://www.sqlservercentral.com/Forums/Topic1574415-2799-1.aspx

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ramana3327 SSCoach Points: 19722 More actions · Answer 11

Sorry. The topic name is different so I kept in different place.