Menu
The question says it all. Which one is faster? And when should we use view instead subquery and vice verse when it comes to speed optimisation?
I do not have a certain situation but was thinking about that while trying some stuff with views in mysql.
609
It all depends on the situation. MS SQL Indexed views are faster than a normal view or query but indexed views can not be used in a mirrored database invironment (MS SQL). A view in any kind of a loop will cause serious slowdown because the view is repopulated each time it is called in the loop. Same as a query.
The retrieval time of the query using joins almost always will be faster than that of a subquery. By using joins, you can maximize the calculation burden on the database i.e., instead of multiple queries using one join query.
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.
The first difference is that inline views can contain multiple columns, while subqueries (in the Oracle meaning) should return only one. The reason is simple – an inline view works like a table and tables can contain more than one column. Subqueries, on the other hand, generally work as a single value.
A smart optimizer will come up with the same execution plan either way. But if there were to be a difference, it would be because the optimizer was for some reason not able to correctly predict how the view would behave, meaning a subquery might, in some circumstances, have an edge.
But that's beside the point; this is a correctness issue. Views and subquerys serve different purposes. You use views to provide code re-use or security. Reaching for a subquery when you should use a view without understanding the security and maintenance implications is folly. Correctness trumps performance.
152
Neither are particularly efficient in MySQL. In any case, MySQL does no caching of data in views, so the view simply adds another step in query execution. This makes views slower than subqueries. Check out this blog post for some extra info http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as-performance-troublemaker/
One possible alternative (if you can deal with slightly outdated data) is materialized views. Check out Flexviews for more info and an implementation.
39
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
