Are database sights only a way to simplify the access of information or will it provide performance benefits when being able to access the sights instead of just running the query that the view is dependant on? I suspect sights are functionally equal to only the adding the saved view query to every query around the view data, is correct or exist other particulars and/or optimizations happening?

It is dependent around the RDBMS, truly there is not optimisation happening, and it is only a convenient method to simplify queries. Some database systems use "materialized sights" however, which use a caching mechanism.

I've always considered Sights to become just like a read-only Saved Methods. You allow the database just as much information as possible ahead of time therefore it can pre-compile as well as it may.

You are able to index sights too permitting you use of an optimized look at the information you're after for the kind of query you're running.

Although a particular query running in the view and also the same query running outdoors from the view should perform equivalently, things get a lot more complicated rapidly when you really need to become listed on two sights together. It is simple to finish up getting tables you don't need in to the query, or getting tables in redundantly. The database's optimizer might have more trouble developing a good query execution plan. So while sights can be quite good when it comes to permitting more fine grained security and so on, they aren't always great for modularity.

Often a view is simply a way to produce a common shorthand for determining result sets that you'll require frequently.

However, there's a downside. The temptation would be to include every column you believe you will need somewhere sometime when you may want to make use of the view. So YAGNI is violated. Not just posts, but sometimes additional outer joins get added on "just just in case". So covering indexes may not cover anymore, and also the query plan may rise in complexity (and drop in efficiency).

YAGNI is really a critical concept in SQL design.

In most cases, sights should perform equivalently to some query written on the actual tables.

But: there might be edge cases, also it would behoove you to definitely test out your code. All modern RDBMS systems have tools that enables you to begin to see the queryplans, and monitor execution. Do not take my (or anybody else's) word for this, when you are able possess the definitive data at the tips of the fingers.

I understand it is really an old thread. Discussion is nice, however i do wish to toss in yet another thought. Performance also is dependent on what you're using to drag data with. For instance, if you're front-ending with something similar to Microsoft Access you are able to certainly gain performance for many complex queries using a view. The reason being Access doesn't necessarily pull in the SQL server as we wish -- in some instances it might pull entire tables across then attempt to process in your area after that! Not if you make use of a view.