March 16, 2004 at 8:02 am
Hi
Is there anyway to force the optimiser into a specific execution plan?
I don't have concrete evidence, but I'm pretty sure I've seen the optimiser choose the wrong execution plan (e.g filtering the very large table first, then joining to the small table). How could I force the optimiser into filtering the small table first then join to the large table?
Thanks
Andy
P.S. Defragging the index on the large table seemed to force the optimiser into the correct path
March 16, 2004 at 9:14 am
Are statistics current? In order for the SQL Server Query Optimizer to make good decisions, it must have up-to-date statistics on the tables and indexes in your database. How you write the table join also affects the optimizer. for example if you put all you Join condition in the ON portion of the JOIN on delay until the WHERE clause. You can you Table Hints if the optimizer refuses to cooperate.
Eg. SELECT col1 FROM table1 WITH ( < table_hint > [ ,...n ] ) ]
Where table hint may be
< table_hint > ::=
{ INDEX ( index_val [ ,...n ] )
| FASTFIRSTROW
| HOLDLOCK
| NOLOCK
| PAGLOCK
| READCOMMITTED
| READPAST
| READUNCOMMITTED
| REPEATABLEREAD
| ROWLOCK
| SERIALIZABLE
| TABLOCK
| TABLOCKX
| UPDLOCK
| XLOCK
}
Francis
March 17, 2004 at 4:41 am
there are also join hints (specifiece LOOP, MERGE or HASH JOIN will be used) and query hints.
Look at from clause and option clause in BOL
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply