SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to avoid Nested Loop Join


How to avoid Nested Loop Join

Author
Message
Lexa
Lexa
Say Hey Kid
Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)

Group: General Forum Members
Points: 675 Visits: 508
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
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228151 Visits: 46339
Table definitions, index definitions and execution plan please.

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


sharky
sharky
SSC Veteran
SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)SSC Veteran (218 reputation)

Group: General Forum Members
Points: 218 Visits: 441
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..
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228151 Visits: 46339
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, 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


Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)SSC Guru (99K reputation)

Group: General Forum Members
Points: 99471 Visits: 33014
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 and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Lexa
Lexa
Say Hey Kid
Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)Say Hey Kid (675 reputation)

Group: General Forum Members
Points: 675 Visits: 508
I'm afraid I can't put table definitions and query plans here. I can send you via PM.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)SSC Guru (228K reputation)

Group: General Forum Members
Points: 228151 Visits: 46339
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, 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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search