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?