Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'd like to know how/if anyone has gained significant performance benefits from using views instead of subqueries.

I would prefer not to define a specific case, since I'm trying to establish good practice and rule of thumb in addition to case-by-case.

An example would be finding the last claim date in an insurance policy claim list where you started the search by sorted/filtered customer set and all claims are in their own table. I'm currently using a view and thinking about a subquery instead.

Things that might affect performance across cases:

  • Can views be used somehow to avoid a full scan where a subquery would need to?
  • Are there any limitations/caveats on ensuring that the best indexes are used when joining to a view?
share|improve this question
2  
What DBMS are you talking about? I'd imagine that most treat views and derived tables very similarly except for the case of materialised views. –  Martin Smith Oct 9 '13 at 9:59
add comment

1 Answer

up vote 1 down vote accepted

The case where you can achieve performance benefit using a view (or common table expression = "inline view") instead of a subquery is if you have to repeat the same subquery several times in your query.

If instead you can replace each subquery with the same view name (or named common table expression) then the optimizer knows it's the same thing and can either cache the result or restructure the execution path so that the result only needs to be queried once.

share|improve this answer
    
I suppose you are referring to SQL-Server's optimizer. If so, please add this detail as optimizers (in other DBMS) may have different capabilities (enhanced or lowered.) –  ypercube Oct 9 '13 at 13:39
    
@ypercube - SQL Server doesn't do this most of the time (if at all). If a CTE is referenced multiple times it gets evaluated multiple times. –  Martin Smith Oct 9 '13 at 17:00
    
Oracle and I think Postgres can do this. –  Colin 't Hart Oct 9 '13 at 18:44
    
More importantly, if a CTE is referenced multiple times, this fact by itself is not sufficient to deduce that the optimal access paths for each of the contexts in which they appear are also identical. Think any CTE, with attributes say A and B, being tested once for "does it contain a tuple where A=1" and elsewhere being tested for "does it contain a tuple where B=7". The CTE is the same, the optimal access paths for the two usages of the CTE most likely aren't, and neither of them necessarily involves full evaluation. –  Erwin Smout Oct 9 '13 at 22:16
    
All valid points, but the point I was trying to make is that this is one case where a view (inline or "normal") can yield a performance improvement over the use of a subquery or multiple subqueries. @Erwin's point is important too: the use of views or CTEs doesn't guarantee materialization at execution time. Conversely, I don't think (m)any optimizers can recognize two subqueries as being identical and materialize them once? –  Colin 't Hart Oct 10 '13 at 11:34
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.