I wish to know what's the distinction between a question along with a view when it comes to performance. And when a view is pricey, what else besides a question could I actually do to enhance performance?
The reply is that sights and ad-hoc queries, within the simple situation, are nearly identical. A lot to ensure that whenever you program having a view, you need to think about it as if the written text from the view definition had been cut and copied and pasted to your parent query.
When they do the identical factor a view may be slightly faster on first execution because the database server may have a precompiled execution plan for this. Is dependent in your server though.
Empasis on might and slightly...
Sights promote code reuse and may abstract away database complexity to provide a far more coherent 'business' type of data. However are not as tunable. You might find yourself ready where you have to provide join hints or any other low-level optimisations and several DBA's which i have labored with don't like them being put on sights because they will then be used again across many queries, the opinion since these kinds of hints ought to be employed as moderately as you possibly can. I love using sights myself.
In SQL Server In my opinion the performance distinction between sights and queries is minimal. What I would suggest doing to enhance performance would be to create another table store the outcomes of the vista. You can possibly produce a staging table where new information is held after which a saved process could be run at some interval that populates the significant table using the new information. A trigger may be good for this function. With respect to the needs of the application this design might be appropriate. If you're dealing with near real-time data, this method will result in concurrency issues...
Another factor to consider, would be to make certain the base tables you're using to create your view are indexed properly, which the query is enhanced. Finally, I believe that it is possible in SQL Server enterprise to produce indexed sights although I haven't used them before.
A view is barely more costly to the pc than writing the query longhand. A view can help to save the programmer/user considerable time writing exactly the same query out again and again, and setting it up wrong, and so forth. The vista can also be the only method to access the information if sights will also be accustomed to enforce authorization (access control) around the underlying tables.
When the query doesn't succeed, you have to review the way the totally created, and if the tables all possess the appropriate indexes in it. In case your system needs accurate statistics for that optimizer to do well, perhaps you have up-to-date individuals statistics sufficiently lately?
Once upon a very long time ago, I discovered a method in which a query generator had produced one query that listed 17 tables in one FROM clause, including several LEFT OUTER JOIN of the table with itself. And, actually, closer scrutiny says some of the 'tables' were actually multi-table sights, and a few of these also involved self outer joins, and were themselves involved with self outer joins from the view. To express "ghastly" is definitely an understatement. There is lots of cleanup easy to enhance the performance of this query - getting rid of unnecessary outer joins, self joins, and so forth. (It really pre-dated the explicit join notation of SQL-92 - I stated a very long time ago - therefore the outer join syntax was DBMS-specific.)