Thursday 26 March 2009

The order of INNER JOIN’s

I want to address a topic that I think a lot of people have been taught in the past and still live by today. I’ve heard different variations of this “rule” in the years that I have been programming and they go something like this.

  • “Where possible ALWAYS have you smallest tables joined to first.”
  • “Where possible ALWAYS have you largest tables joined to first”

So already you might start to doubt the validity of the rule :-) GOOD

I’ve created 5 tables all with the same structure.

   1: CREATE TABLE <Table Name>
   2: (
   3:     RowNum INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
   4:     SomeInt INT NULL,
   5:     SomeString CHAR(2) NULL,
   6:     SomeCSV    VARCHAR(80) NULL,
   7:     SomeNumber MONEY NULL,
   8:     SomeDate DATETIME NULL
   9: )

I’ve added different amounts of data to each table as follows:

--Table1 100 rows
--Table2 1000 rows
--Table3 10000 rows
--Table4 100000 rows
--Table5 1000000 rows

I then created 6 queries all with various join orders:

   1: --Query1
   2: SELECT m.*
   3: FROM Table5 m
   4:     INNER JOIN Table1 a1 ON m.RowNum = a1.RowNum
   5:     INNER JOIN Table2 a2 ON m.RowNum = a2.RowNum
   6:     INNER JOIN Table3 a3 ON m.RowNum = a3.RowNum
   7:     INNER JOIN Table4 a4 ON m.RowNum = a4.RowNum
   8:  
   9: --Query2
  10: SELECT m.*
  11: FROM Table5 m
  12:     INNER JOIN Table4 a4 ON m.RowNum = a4.RowNum
  13:     INNER JOIN Table3 a3 ON m.RowNum = a3.RowNum
  14:     INNER JOIN Table2 a2 ON m.RowNum = a2.RowNum
  15:     INNER JOIN Table1 a1 ON m.RowNum = a1.RowNum
  16:  
  17: --Query3
  18: SELECT m.*
  19: FROM Table5 m
  20:     INNER JOIN Table1 a1 ON m.RowNum = a1.RowNum
  21:     INNER JOIN Table3 a3 ON m.RowNum = a3.RowNum
  22:     INNER JOIN Table4 a4 ON m.RowNum = a4.RowNum
  23:     INNER JOIN Table2 a2 ON m.RowNum = a2.RowNum
  24:  
  25: --Query4
  26: SELECT m.*
  27: FROM Table1 m 
  28:     INNER JOIN Table2 a2 ON m.RowNum = a2.RowNum
  29:     INNER JOIN Table3 a3 ON m.RowNum = a3.RowNum
  30:     INNER JOIN Table4 a4 ON m.RowNum = a4.RowNum
  31:     INNER JOIN Table5 a5 ON m.RowNum = a5.RowNum
  32:  
  33: --Query5
  34: SELECT m.*
  35: FROM Table5 m 
  36:     INNER JOIN Table4 a4 ON m.RowNum = a4.RowNum
  37:     INNER JOIN Table3 a3 ON m.RowNum = a3.RowNum
  38:     INNER JOIN Table2 a2 ON m.RowNum = a2.RowNum
  39:     INNER JOIN Table1 a1 ON m.RowNum = a1.RowNum
  40:  
  41: --Query6
  42: SELECT m.*
  43: FROM Table3 m 
  44:     INNER JOIN Table1 a1 ON m.RowNum = a1.RowNum
  45:     INNER JOIN Table5 a5 ON m.RowNum = a5.RowNum
  46:     INNER JOIN Table4 a4 ON m.RowNum = a4.RowNum
  47:     INNER JOIN Table2 a2 ON m.RowNum = a2.RowNum

Each Query returns 100 rows as expected and using :

   1: SET STATISTICS TIME ON

I get the same result for each query: (I ran each query over 10times):

SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

(100 row(s) affected)

SQL Server Execution Times:
CPU time = 0 ms,  elapsed time = 3 ms.

For some people this might seem strange as they may have been taught that changing the order of your joins can dramatically affect your result.

Now let’s look at the execution plans:

OK so this is all very strange isn’t it!!!

If you run Queries 1 -3 together you get this:

Query 1: Query cost (relative to the batch) 55% 

Query 2: Query cost (relative to the batch) 13%

Query 3: Query cost (relative to the batch) 33%


If you run Queries 4 -6 together you get this:

Query 4: Query cost (relative to the batch) 33% 

Query 5: Query cost (relative to the batch) 33%

Query 6: Query cost (relative to the batch) 33%


The reason the above percentages are different and why the percentages in the Query Plans for each Clustered Index Seek/Scan are different for each query is mainly due to the statistics on the tables. After running the following statement:

   1: UPDATE STATISTICS Table1
   2: UPDATE STATISTICS Table2
   3: UPDATE STATISTICS Table3
   4: UPDATE STATISTICS Table4
   5: UPDATE STATISTICS Table5

The Query Costs relative to the batch for each query became equal and the percentages for each operator in the different Execution plans became the same.

More importantly we notice that the Execution plans themselves are very much the same for each query.

At this stage I would like to point you to Grant Fritchey’s blog about a recent article Execution Plan Estimated Operator Cost for more information on the strange percentages in execution plans.

The most important thing to remember here is to understand what you are writing. Test what people tell you if you not sure, on top of this understand what an execution plan is showing you. Understand the plan don’t blindly believe the percentages …

41 comments:

Mike Walsh said...

Hey Christopher -

Great post. Big pet peeve of mine is generalisms/old adages without testing for yourself. Nice write up and great looking blog so far.

I'll be adding you to my reader, glad I stumbled upon your site.

Nicholas Chammas said...

Hi Chris,

There are a number of problems with your analysis.

First, as you can see in the execution plans, though you specified a variety of join orders in your queries the optimizer always joined from the smallest table (table 1) out to the biggest one.

Second, if you really want to see the effects of varying the join order in your queries, repeat your queries and use the FORCE ORDER hint. I am sure you will find that there are big differences, especially with more realistic data.

Dan Tow has written an excellent book on tuning SQL queries that goes in depth on the importance of getting the best join order. I highly recommend it.

http://www.amazon.com/SQL-Tuning-Dan-Tow/dp/0596005733/ref=pd_bbs_sr_1?ie=UTF8&s=books&qid=1238523899&sr=8-1

Best,
Nick

Nicholas Chammas said...

So the punchline is that the optimizer will typically know what join order is best (as it did with your sample queries), but not always. That's when you need to manually tune your query.

Christopher Stobbs said...

That is a good point Nick.
But as you stated he Optimizer "will" use the correct order making our life as coders easier.
However as I mentioned in my post make sure you understand what the optimizer is doing, if you don't then you right you might not realise that the optimizer has taken the wrong path.
I should have mentioned that I meant code order and not execution plan order. As you have indicated the execution is always the same hinting at the preferred order of the JOINS
thanks for the feedback

Anonymous said...

Wow, that's crazy man. They should really try to do something to fix that.

Anonymous said...

Do you even speak English? Seriously, wall of text crits me for 99999k.

Anonymous said...

Chrissy M: Die in a fire!

Chris: I love the depth of your analysis, nice blog :P

Anonymous said...

namibia dating http://loveepicentre.com/faq.php black women dating fort lauderdale florida

Anonymous said...

Piece of writing writing is also a excitement, if you be acquainted with afterward you can
write otherwise it is complicated to write.


Also visit my page disgruntled

Anonymous said...

I don't write a bunch of remarks, but i did a few searching and wound up here "The order of INNER JOIN's". And I actually do have a couple of questions for you if you do not mind. Is it simply me or does it give the impression like some of these responses look as if they are left by brain dead people? :-P And, if you are writing on other online social sites, I'd like to follow anything new you have to post. Could you list of the complete urls of all your communal sites like your twitter feed, Facebook page or linkedin profile?

my web site: safe diets
my site :: diet that works

Anonymous said...

Just want to say your article is as astounding. The clarity in
your post is just spectacular and i could assume you
are an expert on this subject. Fine with your permission let me to grab your feed to keep updated
with forthcoming post. Thanks a million and please carry on the rewarding work.


Here is my blog post :: Residency.Scribelabs.com
My website :: austin astrophysics

Anonymous said...

We are a group οf volunteers аnԁ ѕtarting a new scheme in οur
сοmmunity. Youг web ѕite рrovided us wіth valuablе іnformatiοn to work
on. You've done an impressive job and our entire community will be thankful to you.

Here is my site: premature ejaculation pills

Anonymous said...

I have been surfing online more than three hours lately,
but I never discovered any fascinating article like yours.
It's beautiful value enough for me. Personally, if all webmasters and bloggers made good content as you probably did, the web will probably be a lot more useful than ever before.

Feel free to visit my page http://safedietplans.com

Anonymous said...

I think this is one of the most important info for me.
And i'm glad reading your article. But want to remark on some general things, The site style is perfect, the articles is really great : D. Good job, cheers

My web page ... http://www.drolet.ca/masters/michaelkorsoutlet.html

Anonymous said...

What's up everyone, it's my first pay a visit at this web site, and post is really fruitful designed
for me, keep up posting these types of articles or reviews.


my blog; sidney crosby Jersey

Anonymous said...

Somebody necessarily assist to make critically posts I would state.

This is the very first time I frequented your web page and up to now?

I surprised with the research you made to make this actual
put up extraordinary. Excellent process!

my website Louis Vuitton Bags

Anonymous said...

Excellent way of describing, and fastidious article to obtain information about my presentation subject matter, which i am going to convey in college.


Here is my page ... Abercrombie Paris

Anonymous said...

First of all I would like to say fantastic blog! I had a quick question that
I'd like to ask if you do not mind. I was curious to find out how you center yourself and clear your mind prior to writing. I've
had a difficult time clearing my mind in getting my thoughts out there.
I truly do take pleasure in writing however it just seems like
the first 10 to 15 minutes are lost just trying to figure
out how to begin. Any recommendations or tips? Thank you!



My webpage: Louis Vuitton Outlet

Anonymous said...

I am in fact thankful to the owner of this web page who has shared this
great post at at this place.

Also visit my web blog Gafas Oakley Baratas

Anonymous said...

I just could not leave your site prior to suggesting that I really loved the usual information
an individual supply for your visitors? Is going to be back
often in order to check up on new posts

Also visit my web blog :: web site

Anonymous said...

Truly when someone doesn't understand after that its up to other people that they will help, so here it occurs.

Review my web-site: NFL Jerseys Cheap

Anonymous said...

Hello, I desire to subscribe for this webpage to obtain most up-to-date updates, thus where can i do it please
help.

Also visit my site: Montre Guess

Anonymous said...

Someone necessarily assist to make critically articles I would state.
That is the very first time I frequented your web page and up to now?
I surprised with the analysis you made to create this particular put up extraordinary.
Magnificent job!

Here is my blog :: http://www.explorethecapabilities.com

Anonymous said...

company website 5 mg propecia generic - propecia results 1 month

Anonymous said...

When someone writes an piece of writing he/she maintains the image of a
user in his/her brain that how a user can understand it.
Therefore that's why this post is outstdanding. Thanks!

Feel free to surf to my blog post; article source

Anonymous said...

Wow, this piece of writing is fastidious, my younger sister is analyzing these kinds of things, therefore I am going to inform
her.

Here is my weblog; web site

Anonymous said...

Generally I do not learn article on blogs, however I wish to say that this
write-up very compelled me to try and do it! Your writing taste has been surprised me.
Thank you, quite nice article.

My homepage - Christian Louboutin Heels ()

Anonymous said...

Pretty component of content. I simply stumbled upon your website and in accession
capital to say that I acquire in fact enjoyed account your
weblog posts. Any way I will be subscribing for your feeds or even
I fulfillment you access persistently quickly.


my website :: Air Max

Anonymous said...

Hey there! I could have sworn I've been to this website before but after reading through some of the post I realized it's
new to me. Anyways, I'm definitely glad I found it and I'll be book-marking and checking back often!


Feel free to surf to my webpage Louis Vuitton Handbags

Anonymous said...

http://mckague.org/GreenBayPackers.html I totally adore your blog and uncover almost all of your post’s to be just what I’m looking for. Would you offer you guest writers to create content available for you personally? I wouldn’t thoughts publishing a post or elaborating on a number in the subjects you create regarding here. Once more, awesome blog! ナイキ [url=http://mckague.org/ChicagoBears.html]Julius Peppers Jersey[/url]
http://www.mckague.org/OaklandRaiders.html Thank you for any other fantastic short article. Exactly where else could anybody get that style of information in such an ideal manner of writing? I’ve a presentation subsequent week, and I am in the look for such details. [url=http://mckague.org/HoustonTexans.html]J.J. Watt Jersey[/url] http://mckague.org/ChicagoBears.html I need to to thank you for this good study!! I absolutely loved just about every bit of it. I have you book-marked to examine new stuff you post… [url=http://www.mckague.org/OaklandRaiders.html]Rolando McClain Jersey[/url]
http://www.mckague.org/NewEnglandPatriots.html I retain listening for the news lecture about receiving boundless online grant applications so I've been seeking around for the best web-site to acquire one particular. Could you inform me please, exactly where could i obtain some? [url=http://www.mckague.org/NewEnglandPatriots.html]Aaron Hernandez Jersey[/url]

Anonymous said...

Hello, I desire to subscribe for this web site to get newest updates, therefore where can i do it please
assist.

Feel free to visit my blog :: Sac Louis Vuitton Pas Cher

Anonymous said...

Hey there! I understand this is kind of off-topic but
I had to ask. Does operating a well-established blog such as yours
require a large amount of work? I am completely new to writing a blog however I do write in my diary on a daily basis.
I'd like to start a blog so I can easily share my experience and thoughts online. Please let me know if you have any kind of recommendations or tips for new aspiring bloggers. Appreciate it!

my blog: Michael Kors Handbags

Anonymous said...

Hello, every time i used to check blog posts here in the early
hours in the break of day, because i like to find out more and more.


my blog - Boutique Air Jordan (smu-fr.org)

Anonymous said...

This paragraph gives clear idea in favor of the new viewers of blogging, that in fact how to do blogging and site-building.


My blog :: Officiel Louis Vuitton Site ()

Anonymous said...

This is very interesting, You're a very skilled blogger. I have joined your rss feed and look forward to seeking more of your fantastic post. Also, I have shared your site in my social networks!

My web page ... Sac Louis Vuitton Pas Cher

Anonymous said...

Wonderful article! This is the kind of information that should be shared
around the net. Disgrace on Google for now not positioning
this publish higher! Come on over and consult with my site
. Thank you =)

my page :: Boutique Air Max - http://smu-fr.org -

Anonymous said...

Thanks for the marvelous posting! I quite enjoyed reading
it, you could be a great author.I will always bookmark your blog and definitely will come back someday.
I want to encourage you to definitely continue your great writing, have a nice weekend!


Have a look at my blog - Boutique Guess []

Anonymous said...

Hey! I know this is kinda off topic but I was wondering if you knew where I could locate a captcha plugin for my comment form?
I'm using the same blog platform as yours and I'm having problems finding one?
Thanks a lot!

Stop by my weblog :: Chaussure De Foot ()

Anonymous said...

Thanks for sharing your thoughts. I truly appreciate your efforts and I will be waiting for your next post thanks
once again.

Here is my web site - related site

Poker said...

I really enjoy to be here. Your have great insight. Thank you
-------------------------
Poker senza deposito gratis
Poker startkapital ohne einzahlung
No deposit free bonuses
Check out the rules of the game in the most popular form of poker - Texas Hold'em. Card sets are very similar to our native five card poker but the rules are different. To do this, visit the most popular website on the subject and learn to play. Learning the basics does not take much time. After reading the rules of Poker time to choose the right poker room. I recommend: Pokerstars popular and a lot of freerolls for Poles Titan Poker - free freeroll for Poles on Sunday
Poker bonus sin deposito
Poker sans depot
The final step is to find the free tournament. As it free poker no deposit.
Gives you a adress the biggest daily forum where you can find a list of current freeroll poker rooms available. Stay date new topics in discussion forums and given gradually registrants in the new poker rooms. After a while, you'll be able to play several tournaments a day and if you played and increased skills-you can earn a pair of gold ... no deposit bonuses, free poker money, or even a few hundred per month.
Laptop samsung
TV Deal
Viteo Arabic
Bonus titan senza deposito, gratis poker.

Unknown said...

Do you need Personal Loan?
Business Cash Loan?
Unsecured Loan
Fast and Simple Loan?
Quick Application Process?
Approvals within 24-72 Hours?
No Hidden Fees Loan?
Funding in less than 1 Week?
Get unsecured working capital?
Contact Us At :mrhamdnloanoffer@gmail.com
LOAN SERVICES AVAILABLE INCLUDE:
================================
*Commercial Loans.
*Personal Loans.
*Business Loans.
*Investments Loans.
*Development Loans.
*Acquisition Loans .
*Construction loans.
*Business Loans And many More:
Contact Us At :mrhamdnloanoffer@gmail.com