Removing joins & Execution Time

Question

Removing joins & Execution Time

ramana3327

SSCoach

Points: 19722
More actions
June 13, 2014 at 10:23 am

#289977

Hi,
I do have a general question.
I have a query which contains 12 left outer join. I remove some of the joins that don't have parameters. The result is coming same but usually when we remove joining it should take less exec time but for me it is taking more time. What could be the reason?
Here I am not worrying about the result set. I am getting same result set

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Without seeing the queries and the execution plans it is pretty much impossible to say why a query with fewer joins is taking longer. You've changed the text so the optimizer will need to compile the new query and is likely coming up with a plan that may not be ideal for the data being returned. When was the last time stats were updated?

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

ramana3327 SSCoach Points: 19722 More actions · Answer 2

Hi Jack,

I am running in dev box only. So stats were not very up to date. Usually to test the changes do we need to run the stats before change & after the change

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

Probably because the joins weren't the limiting factor of the query's performance. Hard to say much more in general terms.

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

Jeff Moden SSC Guru Points: 1004372 More actions · Answer 4

ramana3327 (6/13/2014)
Hi,
I do have a general question.
I have a query which contains 12 left outer join. I remove some of the joins that don't have parameters. The result is coming same but usually when we remove joining it should take less exec time but for me it is taking more time. What could be the reason?
Here I am not worrying about the result set. I am getting same result set

Probably because the joins were actually acting as filters even if they weren't used in the SELECT list.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

Rough guess? The LEFT JOINs were chained, so a previous LEFT JOIN was acting as a restriction on a deeper one.

Need to see the query to be sure though.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Grant Fritchey SSC Guru Points: 398890 More actions · Answer 6

ramana3327 (6/13/2014)
Hi Jack,
I am running in dev box only. So stats were not very up to date. Usually to test the changes do we need to run the stats before change & after the change

I would have the statistics up to date before testing, yes. But, only if your production instance has good statistics maintenance too. Good statistics are vital because they drive the choices the optimizer makes.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning