Is a View faster than a regular Query?

Asked

Viewed 2,203 times

8

When using a query of type

SELECT * FROM myView

is faster than

SELECT * FROM (query para gerar a view acima)

I have an appointment and would like to curl or make it faster, but I am in doubt if I create with View or Query normal!

sql sql-server
  • 1

    I would only work with view, if the query always has to be that way, if you have to use the view, and along with a Where syntax the performance is a m... if the problem is only performance, I do not think it is appropriate, but if it is a matter of code maintenance, there is. I don’t have enough information to draft an answer, so I’ll just leave it as a comment anyway.

  • I remember that views will keep the query plan stored, so I wouldn’t need to go through that phase again. It is also possible that the plan generated at the time of the creation of the view is more efficient than a plan generated in the execution of any query. Now, if the data undergoes a very large change, the view query plan is likely to become inadequate, making it in this case slow

  • 1

    Another point you should ask yourself is, do I really need this extra performance? Because if not, entering this view will look more like a gambiarra. The most common cases are the cases that @Rovannlinhalis talked about, where the query is always done in the same way.

  • Related: Is a view Faster than a simple query?

2 answers

7


Views are commonly used to improve security, prevent repetition of SQL code and semantics.

In most cases there will be no improvement in performance, except if you are talking about a Indexed View. In that case could there is an increase in performance. Read the excerpt taken from MSDN:

Creating an index clustered unique in a view improves performance because the view is stored in the database in the same way as an index clustered is.

Microsoft released a graph that shows an absurd performance gain with this type of view:

This was taken from Improving performance with SQL Server 2005 Indexed Views.

3

To VIEW, among other things, it serves for you to store a complex query in an object in the database, facilitating the reuse of this SQL code. Then every time it is called the SQL code will be executed.

Assuming you use SQL Server, you’ll get performance gains if you use Indexed Views. This feature allows SQL Server to search the data in the indexes created for the view as if it were a table.

There are some restrictions that you can find in the Microsoft documentation. The following link is also documented how to create a Indexed View https://docs.microsoft.com/pt-br/sql/relational-databases/views/create-indexed-views

The Postgresql and the Oracle also implement something similar, but with the name of Materialized View. Below some links about migration Materialized View from the Oracle to Indexed Views of SQL Server.

https://blogs.msdn.microsoft.com/ssma/2011/06/20/migrating-oracle-materialized-view-to-sql-server/

https://www.akadia.com/services/sqlsrv_matviews.html

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.