Folks, I could not find any material that answers my question.
I have a very long query in which I am using temp table. I use it because otherwise I will have to join with a very huge transaction table multiple number of times. Instead, I filter the rows into a temp table and do the join on this.
Now there is strange thing that I noticed. When I do a join with the temp table the execution plan shows the 'estimated number of rows' as 1 and the 'actual number of rows' as a bigger value.
I have read in SSC that use of UDF will cause this. But in this case I am not using a UDF. Won't the optimizer 'know' the number of rows in the temp table? The temp table is in turn derived from another temp table which is populated from the output of a UDF joined with other tables. Is this the reason?
Please see the attached execution plan. How badly could this aspect affect the performance?
I do not want to post DDL scripts as the query is quite exhaustive [but not tough :)] and not straightforward.
- confused arjun