Geek Answers Handbook

Views in SQL Server

Can I use views to improve performance for a query?

+4
source share
4 answers

Indexed views will improve query performance if designed correctly.

http://technet.microsoft.com/en-us/library/cc917715.aspx

EDIT: Keep in mind that if you are not using Enterprise Edition or Developer Edition, you need to specify the WITH (NOEXPAND) hint to improve the performance of the selection.

+5
source

A non-materialized view can benefit from caching a query plan, and depending on the configuration it may support a predicate. Clicking a predicate is where the optimizer determines that the WHERE clause in the view:

SELECT v.* FROM VIEW v WHERE v.column = 5

... can be inserted into the query used to build the view:

 SELECT * FROM VIEW_TABLE(S) WHERE column = 5

Otherwise, a non-materialized representation can be considered a macro - placeholder, which extends into the base query. This means that depending on usage, the view may work worse than including the main request in an external request. Layered views on top of each other are not reasonable practice because errors will not occur until runtime (queries that use views).

A materialized view (known as an indexed view in SQL Server) has at least one index on it and can be as fast as querying a regular table.

+4
source

I do not think so. It all depends on the indexes of the tables used in the queries. I do not think that when using views in any database, there is some kind of "performance". The only gain is that they do the work for you.

0
source

You should at least see a slight performance boost with the views, since they will basically tell the database engine that the tables are related.

This is if indexing is enabled and correctly configured for tables.

Otherwise, a slight increase in productivity comes only from the database mechanism, already knowing that it needs to run this main operator.

-1
source

More articles:


All Articles