Friday, April 1, 2011

Order of Joins, FORCE ORDER Hint

Well, as you may know SQL Server can build the execution plan
with different order of joins than in your query. This is done as
part of the query execution optimization.

I had a situation when a query did not performed well, the
execution plan was not optimal. In such cases, what you
should do, is to check whether the statistics are updated,
check if SQL Server uses right indexes,check for missing
indexes and maybe you need to rewrite your query.

In my situation, I had no time
to rewrite the query, but we
noticed that the order of tables
that SQL choses to accessed
was not optimal.

The quick and dirty solution was to add 'Force Order' hint to the query.
What is does? It specifies that the join order indicated by the query
syntax should be preserved during query optimization.

In most cases the SQL Server optimizer will do great job.
So you should use this hint only in exceptional cases.

1 comment:

  1. Another common way is to dynamically generate the order of sql joins according to expected volume of each table and add FORCE ORDER hint. It can boost the performance with updated statistics very well.