Is there a performance gain when using View’s in SQL?

Asked

Viewed 1,134 times

4

As View's are virtual tables resulting from consultations SQL, as in the example:

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

From this, we can update or delete a View, but we can’t edit the data directly into them.

There is some performance gain when using them, or use is related only to code reduction in application queries?

sql performance sql-view
  • 1

    Epa! Yes you can change data through a view in MS SQL reference: http://msdn.microsoft.com/en-us/library/ms180800.aspx

  • Actually, in MS SQL Views allow recording.

2 answers

5


Creating a View itself does not produce performance gains. However, there are some things that can be done to improve your performance:

  1. In SQL Server you can create indexes for View, improving its performance.
  2. On Oracle, you can create Materialized Views, which also provides performance gains.
  • 1

    As everything in SQL yes you may have gained or lost performance depending on the situation and the implementation. In the case to create indexes the view needs to be "schema bounded" (equivalent to materialized)

2

Gain in code reuse and in some cases in performance because the view encapsulated SQL is already compiled.

Browser other questions tagged sql performance sql-view

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