Join Operations – Nested Loops

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Jason Brimhall » Join Operations – Nested Loops

21 posts, Page 1 of 3

1 2 3 »»»

Join Operations – Nested Loops

Rate Topic

Display Mode

Topic Options

Author

Message

SQLRNNR

Posted Monday, January 03, 2011 11:25 PM

SSCoach

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 18,857, Visits: 12,442

Comments posted to this topic are about the item Join Operations – Nested Loops

Thanks to those who helped review this for me. Their suggestions and insight were very helpful

Gail Shaw
Wayne Sheffield
Chris Morris

Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server 2008

SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp

Post #1042172

WayneS

Posted Tuesday, January 04, 2011 1:27 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:59 PM
Points: 6,386, Visits: 8,288

Good, thorough article Jason. Thanks!

Wayne
Microsoft Certified Master: SQL Server 2008

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Post #1042228

zlthomps

Posted Tuesday, January 04, 2011 7:13 AM

SSC Rookie

Group: General Forum Members
Last Login: Friday, June 07, 2013 6:01 AM
Points: 37, Visits: 154

Thanks for the article, very good read.

Post #1042382

Steve Jones - SSC Editor

Posted Tuesday, January 04, 2011 8:20 AM

SSC-Dedicated

Group: Administrators
Last Login: Today @ 5:09 AM
Points: 31,526, Visits: 13,864

Nice job, Jason. Good explanation, and looking forward to reading about the other join types.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #1042453

SQLRNNR

Posted Tuesday, January 04, 2011 8:38 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 18,857, Visits: 12,442

Thanks Wayne, Steve and zlthomps.

Post #1042474

mtillman-921105

Posted Tuesday, January 04, 2011 10:01 AM

Right there with Babe

Group: General Forum Members
Last Login: Yesterday @ 10:04 AM
Points: 753, Visits: 3,786

Well thought-out and illustrated Jason, thanks! Cool

So should we always check queries returning just a few rows to see if a forced Nested Loop would help?

______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

Post #1042555

SQLRNNR

Posted Tuesday, January 04, 2011 10:11 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 18,857, Visits: 12,442

mtillman-921105 (1/4/2011)

Well thought-out and illustrated Jason, thanks! Cool

So should we always check queries returning just a few rows to see if a forced Nested Loop would help?

I would say proceed very cautiously. The DB Engine does an excellent job of determining which join operation to use. I would certainly say to verify indexes first and then check your query. If after that, you still see a performance issue - go ahead and try the hint but be careful about it.

Post #1042566

mtillman-921105

Posted Tuesday, January 04, 2011 10:25 AM

Right there with Babe

Group: General Forum Members
Last Login: Yesterday @ 10:04 AM
Points: 753, Visits: 3,786

'Makes perfect sense Jason, thanks again.

- Mark

______________________________________________________________________
The greatest enemy of knowledge is not ignorance, it is the illusion of knowledge. - Stephen Hawking

Post #1042575

SQLRNNR

Posted Tuesday, January 04, 2011 10:27 AM

SSCoach

Group: General Forum Members
Last Login: Today @ 10:31 AM
Points: 18,857, Visits: 12,442

mtillman-921105 (1/4/2011)

'Makes perfect sense Jason, thanks again.

- Mark

You're welcome.

Post #1042579

Solomon Rutzky

Posted Tuesday, January 04, 2011 1:50 PM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 11:16 PM
Points: 285, Visits: 1,386

Hey Jason. Nice article and one question. In your two main examples the difference is the WHERE condition that constrains the results to 10 rows as opposed to the full 10,000 in the table. Is it fair to compare the query times (and make implications on the differences of the JOIN types) given that they are different queries? One is asked to get 10 rows and the other query gets all 10,000 so naturally they would not take the same amount of time, right? Maybe that is not the point you were trying to get across to begin with, but my initial thought as to the speed increase wasn't that it was due to the different JOIN type but instead to only pulling 10 rows. I wonder if there is a way to show two queries that pull the same amount of rows but are written differently so as to force the different JOIN types (Merge vs Nested Loop).

Thanks and take care,
Solomon...

SQL# - http://www.SQLsharp.com/

Post #1042681

« Prev Topic | Next Topic »

21 posts, Page 1 of 3

1 2 3 »»»

Permissions