i have been trying to determine which is the most efficient, with regards to speed and efficiency, between a view and a common/nested table expression when used in a join.
i have a query which could be represented as index view or a common table expression, which will then be used to join against another table.
the indexed view will use indexes when performing the join. is there a way to make the common table expression faster than an indexed view?
Since an indexed view will have an index created specificially for the purposes of the view, and if a CTE is only producing the same results that the view would produce, the indexed view 'should' be quite a bit more efficient.
Of course, that depends on the statistics (distribution, cardinality, etc.) We don't have any way to assess that -so you will need to test and compare. Select the method that produces the results in an acceptable time with the most efficient execution plan.
Well, without seeing the indexed view or the CTE, it is hard to say with 100% assuredness, but I think I can give this answer at least 98%.
>>the indexed view will use indexes when performing the join. is there a way to make the common table expression faster than an indexed view?<<
Just like Arnie says, the indexed view is for all intents ant purposes a real table. So none of the calculations (assuming you have some) in the view will have to be redone, and you can add additional indexes if necessary to make the work even faster, plus you can add other indexes to the indexed view using the INCLUDE clause that will make this an almost a guarantee.
The more salient question is how expensive will the maintenence of the indexed view be versus the length of time to run the query. In my mind, your question isn't which will be faster, but over the course of a day, which one will hurt your performance more.
If you can post the query/amount of data and such, it would be helpful (and a bit interesting too!)