Menu
Which one is faster :
(in Complex Queries). I have a complex query and i have another complex query from the first one.Is it faster to create a view for the first complex query and query from the view or use cte?
286
Below is the T-SQL for each of our test query types. Looking at SQL Profiler results from these queries (each were run 10 times and averages are below) we can see that the CTE just slightly outperforms both the temporary table and table variable queries when it comes to overall duration.
Views make queries faster to write, but they don't improve the underlying query performance. However, we can add a unique, clustered index to a view, creating an indexed view, and realize potential and sometimes significant performance benefits, especially when performing complex aggregations and other calculations.
A view is an object in the database. A CTE only exists for the duration of a single query.
Advantage of Using CTECTE can be more readable: Another advantage of CTE is CTE are more readable than Subqueries. Since CTE can be reusable, you can write less code using CTE than using subquery. Also, people tend to follow the logic and ideas easier in sequence than in a nested fashion.
A view is a permanent object and the results can be indexed, while a CTE is temporary and created only when used so less flexible. It will be more efficient to break apart your complex query into indexed views than into CTE's. It will be most efficient to ensure all of the tables are properly indexed, which will probably do more for performance than worrying about views vs. CTE's.
55
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
