Does the order in which Joins are peformed (from left to right) matter
whether inner or outer when it comes to performance. Assuming that all
indexes are the same but the size of the tables is different?
For example let's assume we have 5 tables, TABLE1, TABLE2, TABLE3,
TABLE4, TABLE5.
For example:
SELECT Smth from TABLE1 INNER JOIN TABLE2 on Condition1
INNER JOIN TABLE3 on Condition2
LEFT JOIN TABLE4 on Condition3
INNER JOIN TABLE5 on Condition4.
Does SQL optimize the query and finds the best way to inner join or it
starts doing the JOINS from left to right?
Thank you for your your feedback.
Gent 4 10920
GM (ge***********@ trustasc.com) writes: Does the order in which Joins are peformed (from left to right) matter whether inner or outer when it comes to performance. Assuming that all indexes are the same but the size of the tables is different?
For example let's assume we have 5 tables, TABLE1, TABLE2, TABLE3, TABLE4, TABLE5.
For example: SELECT Smth from TABLE1 INNER JOIN TABLE2 on Condition1 INNER JOIN TABLE3 on Condition2 LEFT JOIN TABLE4 on Condition3 INNER JOIN TABLE5 on Condition4.
Does SQL optimize the query and finds the best way to inner join or it starts doing the JOINS from left to right? Thank you for your your feedback.
The former. This is a very important concept for modern relational DBMSs.
You specify the logic to be done. The optimizer finds out how to do the
computation. A good optimizer can do heavy rearranging of your queries,
as long as the result is the same.
Sometimes you know better than the optimizer, and you really want it to
do computation in a certain order, and, yes, most optimizers does permit
you to impose a computation order. But that is a feature to use only in
exceptional cases.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
So what is your opinion of the value of query diagramming to ease the
optimizer along in finding the best plan? http://www.oreilly.com/catalog/sqltuning/
Even if the DB does its own query plan, I'd like to kind of understand what
its having to go thru and help it out as best I can (given that I 'may' have
a better understanding of the data design).
"Erland Sommarskog" <es****@sommars kog.se> wrote in message
news:Xn******** **************@ 127.0.0.1... GM (ge***********@ trustasc.com) writes: Does the order in which Joins are peformed (from left to right) matter whether inner or outer when it comes to performance. Assuming that all indexes are the same but the size of the tables is different?
For example let's assume we have 5 tables, TABLE1, TABLE2, TABLE3, TABLE4, TABLE5.
For example: SELECT Smth from TABLE1 INNER JOIN TABLE2 on Condition1 INNER JOIN TABLE3 on Condition2 LEFT JOIN TABLE4 on Condition3 INNER JOIN TABLE5 on Condition4.
Does SQL optimize the query and finds the best way to inner join or it starts doing the JOINS from left to right? Thank you for your your feedback.
The former. This is a very important concept for modern relational DBMSs. You specify the logic to be done. The optimizer finds out how to do the computation. A good optimizer can do heavy rearranging of your queries, as long as the result is the same.
Sometimes you know better than the optimizer, and you really want it to do computation in a certain order, and, yes, most optimizers does permit you to impose a computation order. But that is a feature to use only in exceptional cases.
-- Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp
Technically speaking, the inifxed JOIN notation is done from left to
right in the FROM clause, as modified by parens. This order matters
when your have OUTER JOINs, but INNER JOINs commute and can be
re-arranged.
The optimizer is free to do the joins in any order or in parallel, if
the original result is obtained. Most modern optimizers will look for
the smallest tables (so they can fit into main storage), statistical
distrtibutions and bunch of other things to make this decision.
The truth is that in smaller SQL products, the optimizers start to
choke after five tables -- there are 120 ways to arrange five tables
and the smaller optimizers don't have the time or resources to do the
best algorithms. This is not a big deal unless you have some really
weird data distributions and very large tables.
The bottom line, is that you need to write the code correctly, then
"Trust in the Optimizer, Luke!"
David Rawheiser (ra*******@hotm ail.com) writes: So what is your opinion of the value of query diagramming to ease the optimizer along in finding the best plan?
http://www.oreilly.com/catalog/sqltuning/
Even if the DB does its own query plan, I'd like to kind of understand what its having to go thru and help it out as best I can (given that I 'may' have a better understanding of the data design).
It's certainly a good idea of having some understanding how a good plan
should look like. Not the least in those situations when the optimizer
goes lost, and produces a plan which takes three hours to run.
For queries that just a couple of joins, it is not that difficult to get
an idea of how would you do, if you had to do it manually. But more
complex queries with correlated subqueries can be more difficult to grasp.
I not read the book you are referring to, nor heard of any opinions on it.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: |
last post by:
As we're on this topic in another thread right now:
Say I have a SELECT query from more than one table and with some =
conditions, does it matter in what order I enter the tables in the FROM =
clause and in what order the WHERE conditions appear in my query? Or =
does it make any difference if I use WHERE or HAVING? (I see that MS =
Access...
|
by: Sri |
last post by:
I am writing a download process in which i have a condition where i
need to join four tables. Each table have lot of data say around
300000 recs.
my question is when i am doing the joins on the columns is there any
specific order i need to follow.
|
by: Ed_No_Spam_Please_Weber |
last post by:
Hello All & Thanks in advance for your help!
Background:
1) tblT_Documents is the primary parent transaction table that has 10
fields and about 250,000 rows
2) There are 9 child tables with each having 3 fields each, their own
PK; the FK back to the parent table; and the unique data for that
table. There is a one to many relation...
|
by: michaelnewport |
last post by:
Greetings,
I like to write my inner joins as below,
but someone at work tells me its not as 'performant'
as using the 'inner join' statement.
Is this true ?
Is there a better way to write it ?
thanks
|
by: steve |
last post by:
Hi, I want to share my experience with you. If you have large tables,
and you are doing joins, be extra careful that the keys in the joins
have exactly the same size (and type). Don’t mix mediumint and int
for example.
There is a huge performance hit if your join keys are not the same
size. This IS documented on mysql.com, but not...
| |
by: Beowulf |
last post by:
I have the view below and if I use vwRouteReference as the rowsource
for a combo box in an MS Access form or run "SELECT * FROM
vwRouteReference" in SQL Query Analyzer, the rows don't come through
sorted by Numb.
Everything I've read on the web suggests that including the TOP
directive should enable ORDERY BY in views. Does someone have an...
|
by: kalaivanan |
last post by:
hi dis is kalaivanan,
which one of inner join or left join is efficient and in what way.
|
by: ankush |
last post by:
I have a table heirarchy of around 5 tables. At places where i have to do a search with criteria ranging on all these tables, will it be advisable to have a join on all the tables. A couple of tables can run into million of rows. Is it advisable to do an iterative search by joining two tables and going down the heirarchy (which will end up in...
|
by: imranpariyani |
last post by:
Hi
i have a severe performance problem with one of my views which has 6 to 8 joins .. any help will be appreciated..
the view is:
CREATE OR REPLACE VIEW thsn.trade_view AS
SELECT tra.tra_id, tra.per_id, tra.fir_id, tra.tra_dcn, tra.tra_startdate::date AS tra_startdate, tra.tra_enddate::date AS tra_enddate, tra.tra_highprice,...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...
| |