Provide a query hint to specify the cardinality of table variable
A problem with table variables is that they don't have statistics. Therefore queries with table varaibles are often optimized under the assumption that there is a single row in the table. You can work around this problem by using temp tables, but temp tables leads you deeper into the quagmire of deferred name resolution. There are also situations when you don't want the statement recompile that often comes with temp tables, and you would be content with table variables, could you only specify an idea of number of rows in the table variable.
Note: the item https://connect.microsoft.com/SQLServer/feedback/details/798726/introduce-t-sql-syntax-to-allow-a-hint-to-be-provided-for-a-table-variable
addresses the same problem and suggest a hint when the table is declared. I think a query hint is a better alternative, since the number of rows in the table could vary during the length of a long stored procedure.
Upvotes: 17<=-=Mar 9 2014 4:32PM=-=>
This issue has a critical performance impact on large DB optimization. Actually it would be good to have cardinality hint on any table in query, or at least temp table.
The reason is this connect issue – https://connect.microsoft.com/SQLServer/feedback/details/758241/update-statistics-does-not-cause-recompilation-for-a-cached-temporary-table
The hint should take local variable as input, not only literal.
Having a local variable causes the issue that you don’t have the amount when the plan is made when a procedure execution starts — but at least I would really love to see even a hint with fixed number, similarly to “optimize for” -hint.<=-=Jun 23 2015 10:53AM=-=>
That’s funny. I actually was looking into this last month, trying to find an optimize for “count” clause to help with the batch results coming in a user defined table. Good idea!<=-=May 25 2016 12:25AM=-=>
Yes it would be useful to have a query HINT to override cardinality estimation of any given recordset, consider that ORACLE already has a such feature and it has been implemented exactly for temporary tables.