March 1, 2007 at 2:18 am
Hi All,
I have recently heard that joining to the largest table last in a query involving several tables can have a performance gain.
Is this true? If so how does this increase the performance?
Thanks.
March 1, 2007 at 2:22 am
Hi ADAM ,
I don't have any idea.. regarding this.
Regards ,
Amit Gupta......
March 2, 2007 at 1:10 am
Adam,
I think the suggestion is based upon the idea that within a query execution plan there are times when the query processor must do iterations over resultsets between tables to find matching records etc. If by the time the largest table is referenced there are fewer records to compare it against performance should increase. Whether this is true or not is one for debate.
Personally, I'm usually in favour of letting the query engine decide the best execution plan but I have had instances where index hints have been required to achieve the best query performance.
As with many things regarding query performance my suggestion is to write the same query multiple times and compare the resulting execution plans and run times. In most instances I think you will find little difference.
hope this helps
March 2, 2007 at 4:09 am
"I have recently heard that joining to the largest table last in a query involving several tables can have a performance gain.
Is this true? If so how does this increase the performance?"
Under SQL Server 4.2 in 1992, when a SQL statement referenced more than 4 tables, the query plan would be be generated for the first 4 tables and then that result would be used with the next 3 table to generate the next part of the query plan. Hence, the rule of thumb to have the largest table last.
As this "partial" query plan process is no longer used, ordering the tables in the from clause has no effect on the query plan or the performance.
SQL = Scarcely Qualifies as a Language
March 2, 2007 at 6:25 am
Thanks for your responses guys.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply