I have a table-valued function and ordinary table and need to perform
INNER JOIN on them. Because, the engine thinks that the function is returning always one row, I try to store the results in temporary table and then to perform the join operation - in both cases, the
Estimate number of rows count that are read from the table/index is wrong.
When the temporary table is used, the correct
Estimate number of rows count value is used for the rows returned by the function. All involved indexes are rebuilt.
Why I am worried about this? This join is part of more complex query which execution is very slow. I believe this is due to the fact that the statistics that are used by the engine are wrong and not the optimal execution plan is used. Even when the query use parallelism again nested loop is used to join the tables.
If I use a
MERGE hints on join the correct statistics are used and better execution plan is built but I prefer to find a way to provide the correct statistics instead of forcing the engine to do something.
Could anyone tell why always one row is expected? Maybe because table-valued function is used, the engine thinks one row is going to be read from the table/index?