4

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?
Improve this question
1
  • 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, 2013 at 9:59

1 Answer 1

Reset to default
3

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.

Improve this answer
7
  • 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, 2013 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, 2013 at 17:00
  • Oracle and I think Postgres can do this.
    – Colin 't Hart
    Oct 9, 2013 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, 2013 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, 2013 at 11:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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