August 10, 2010 at 1:07 am
Hi All,
I have searched for answers to the issue (whether order of how i join my tables in the FROM part of my query affects its performance) but not actually clear. The optimizer would actually choose the optimal order to join.
I have tried testing this issue with a complex query wherein I had 2 multi-million rows tables apart from other look-up tables.I interchanged/changed the join order but there was no drastics change in the response as well as total time.
So how do I say if the order in which I join tables will effect my query performance? Do we have something more than the effective use and implementation of indexes associated with this topic?
Please let me know if i need to do some more homework on it.
August 10, 2010 at 4:51 am
No replys. Does that mean the question is too easy to be asked on forum? Or am i not really clear with the question? Pls let me know.
Guys, i have a presentation on this.Need expert comments!!
August 10, 2010 at 4:59 am
your graphical execution plan will give the better idea which join is worse and which join is good.
Its hard to decide without knowing the query that which join will be bad
----------
Ashish
August 10, 2010 at 5:32 am
Ashish thanks a ton for the reply.
My query is something like:
SELECT LT1.COL1, LT1.COL2, LT2.COL3, LT2.COL4, LT1.COL5, LT1.COL6
FROM LARGE_TABLE_1 LT1
JOIN LARGE_TABLE_2 LT2 ON LT1.C1 = LT2.COL1 AND LT1.COL2 = LT2.COL2
JOIN LOOK_UP_TABLE1 LU1 ON LT1.COL3 = LU1.COL1
JOIN LOOK_UP_TABLE2 LU2 ON LT1.COL4 = LU2.COL1
Here, large table 1 and 2 have indexes (individual not composite) on columns col1,col2,col3 and col4 respectively.
Now if i re-write the query as :
SELECT LT1.COL1, LT1.COL2, LT1.COL3, LT1.COL4, LT1.COL5, LT1.COL6
FROM LARGE_TABLE_1 LT1
JOIN LOOK_UP_TABLE1 LU1 ON LT1.COL3 = LU1.COL1
JOIN LOOK_UP_TABLE2 LU2 ON LT1.COL4 = LU2.COL1
JOIN LARGE_TABLE_2 LT2 ON LT1.C1 = LT2.COL1 AND LT1.COL2 = LT2.COL2
Here, the join order for large table 2 LT2 has been changed.
So my question is:
Joining large table to the driving table before or after joining to other look up tables should that have any effect on performance?
My understanding is optimizer would decide the best possible way and hence whether i join the large table to driving table before or after will not matter.
Please correct me if I am missing out on something.
August 10, 2010 at 5:36 am
it depends how updated your indexes and tables are.
Clustered indexed columns are always preferrable in joins and then non clustered
As well what type of join you using.
----------
Ashish
August 10, 2010 at 6:45 am
On fairly simple queries the order of tables in the WHERE clause usually doesn't affect performance or the plan generated in any way. As queries get more complicated the order can affect performance, but there are not hard and fast rules for exactly when it does. As a general practice, I wouldn't worry about it until you've got a query that is giving you problems.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
August 18, 2010 at 12:46 am
Hey CELKO, .THANX A TON for that answer. That was exactly what i needed to know. I am working on sql server, and FYI about the question, it actually came from my reporting manager who happens to be an oracle expert.:-)
August 19, 2010 at 7:29 am
If you REALLY want to force the order, put
OPTION (FORCE ORDER)
at the end of your query. See:
http://msdn.microsoft.com/en-us/library/ms181714.aspx
Dave.
August 19, 2010 at 9:50 am
ashish.kuriyal (8/10/2010)
it depends how updated your indexes and tables are.
:blink: Could you please elaborate? This is really confusing.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 19, 2010 at 10:22 am
PaulB-TheOneAndOnly (8/19/2010)
ashish.kuriyal (8/10/2010)
it depends how updated your indexes and tables are.:blink: Could you please elaborate? This is really confusing.
I imagine Ashish means it depends how many updates have been made to your indexes since they were last rebuilt - so whether they are fragmented, and (of more relevance here) whether their statistics are a true representation of the actual data.
August 19, 2010 at 10:29 am
This is really confusing.
What I mean was if there are any indexes on your table or not. If its then how much fremented they are?
Update stats I mean how much updated are your tables are, stats need to be updated for references between tables which you will be using in your joins.
Not sure if you still confused....:w00t::w00t::w00t:
----------
Ashish
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply