Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to avoid Nested Loop Join Expand / Collapse
Author
Message
Posted Tuesday, March 25, 2014 9:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 7:19 AM
Points: 161, Visits: 506
I've a query like so:

select distinct t1.f1 from t1 join t2 on t1.f2 = t2.f2
join t3 on t1.f4 = t3.f4
join t4 on t2.f5 = t4.f5
where t3.dt is not null and t2.f9 = xxx

it takes 6 min to execute. If i change select to do a select t1.* instead of select distinct t1.f1, it takes 3 seconds.

When i look at the execution plans for both queries the difference is that in 1st query there is a nested loop join where there is a hash join in the 2nd.

So if I change the query to:

select distinct t1.f1 from t1 inner hash join t2 on t1.f2 = t2.f2
join t3 on t1.f4 = t3.f4
join t4 on t2.f5 = t4.f5
where t3.dt is not null and t2.f9 = xxx

The query takes 3 seconds.

The problem is that this is dynamically generated SQL in an application and we don't want to use hints within it. I've tried updating stats on all tables in the joins, added covering indexes etc. nothing changed the plan. What can be done to force the plan to change from nested loop join to a hash join without using a hint explicitly?

Thanks
Post #1554544
Posted Tuesday, March 25, 2014 9:42 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 40,177, Visits: 36,580
Table definitions, index definitions and execution plan please.


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1554550
Posted Tuesday, March 25, 2014 10:26 AM


Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 5:44 AM
Points: 66, Visits: 437
I think the "Distinct" stinks..

Try to rewriting the query using aggregate functions instead

You need to look at the query plan..

I have a personal pet hate using the Distinct keyword. Overly used in lazy programming..
Post #1554577
Posted Tuesday, March 25, 2014 11:26 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 40,177, Visits: 36,580
If the requirement is to remove duplicate rows, use DISTINCT
If the requirement is to produce aggregates with grouping, use GROUP BY

Seeing Group By with no aggregates suggests that someone read the myth that group by is faster/better than DISTINCT and didn't do any tests.
Specify the query in the simplest, most obvious way, if after testing performance is inadequate then consider alternate query forms and test them. If they're faster great, if not, go back to the simple form.



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1554604
Posted Tuesday, March 25, 2014 12:03 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:54 PM
Points: 13,872, Visits: 28,271
Do you need the aggregation of DISTINCT? Are there data problems you could fix instead of using DISTINCT? You might want to try attacking that. But, I'm with Gail, without seeing what's happening through the execution plan, there's not much to suggest here.

----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server Query Performance Tuning
SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1554628
Posted Tuesday, March 25, 2014 6:42 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 7:19 AM
Points: 161, Visits: 506
I'm afraid I can't put table definitions and query plans here. I can send you via PM.
Post #1554734
Posted Wednesday, March 26, 2014 2:19 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 4:08 PM
Points: 40,177, Visits: 36,580
If you PM them to me I'll delete them (not a private consultant any longer). The reason we're asking for them to be posted here is so that anyone wandering by can help, not just the person you send the details to

You can obfuscate both, replace the table and column names with generic names, run a search/replace over the plan to do the same (just make sure it still loads). We don't care about the names, we care about the operators, their properties and what that says about what SQL's doing



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1554816
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse