If you find your Table Variable queries are not performing well then you might consider adding the optimizer hint RECOMPILE to see if your query starts performing better.
Once the optimizer has a better guess at the actual number of rows in a table variable, then it has a much better chance of picking an operator that works well for larger record sets, like a Hash Match for a join operation. This will allow the optimizer to know how many rows are in the table variable.
TSQL OPTION RECOMPILE CODE
Therefore, to improve the performance of your code that use table variables that contain lots of rows, you should consider adding the optimizer hint RECOMPILE as shown below: SELECT O., TV.īy adding the optimizer hint RECOMPILE, a statement that uses a table variable will be recompiled after the temporary table has been populated. In my mind this is confusing because RE-compile suggests that a 'compile is performed again on an existing compiled plan' yet no plan exists. WITH RECOMPILE or OPTION (RECOMPILE) - in this context a new plan is compiled but not cached when a query runs. Operators that perform well for small numbers of rows do not always scale when large numbers of rows are involved. Here are three uses of the term 'recompile': 1. Having an estimated row count of 1 works well for operators that have small row counts, like a NESTED LOOP operator for a join operation. This occurs because table variables don’t have statistics. This is because when a batch is compiled the table variable has yet to be populated with any rows, and therefore the optimizer uses an estimated row count of 1 for table variables.
Described problem sounds to me like it should be resolved with multi-column stats or indexes. It is better to use sprecompile to mark object for recompilation if you suspect that cached query plan is bad. Table Variables have been known to run slowly when the table variable contains lots of rows. Josh, Option recompile should be used with great caution and not without extensive testing.
0 kommentar(er)
