6

What is the difference between SELECT data from table directly or from view?
And What is the best use for each one?

ecleel
  • 11,748
  • 15
  • 48
  • 48

4 Answers4

4

According to Microsoft there is a performance benifit if you use indexed views in sql server 2000/2005/2008.

Indexed views can increase query performance in the following ways
1. Aggregations can be precomputed and stored in the index to minimize expensive computations during query execution.
2. Tables can be prejoined and the resulting data set stored.
3. Combinations of joins or aggregations can be stored

But just like indexes on tables, indexes on views experience modification overhead. So only add an index to a view if the benefit of its speed increase when running exceeds the time it takes to update the view's index.

The below links give more information on this (on when to use what).

  1. SQL Server 2000/2005 Indexed View Performance Tuning and Optimization Tips.
  2. Improving Performance with SQL Server 2000 Indexed View.
  3. See performance gains by using indexed views in SQL.
Community
  • 1
  • 1
Binoj Antony
  • 15,886
  • 25
  • 88
  • 96
  • 2
    If you read that article closely, you'll see that it's talking about "materialized views", which are really another animal, which really creates a special form of table. – dkretz Feb 22 '09 at 22:28
1

In most databases, they are functionally interchangeable (disregarding materialized views, which are something entirely different anyway.) There are two common reasons for creating views. 1. An abstraction (and column-aliasing) mechanism, and 2. For permissions and access control. But as for efficiency, it's not an issue.

dkretz
  • 37,399
  • 13
  • 80
  • 138
  • 1
    Not true. Some databases have performance issues when views are nested. – Jason Cohen Feb 22 '09 at 21:45
  • And do you have any references? That seems highly unlikely, (although that's why I said "in most databases".) – dkretz Feb 22 '09 at 21:58
  • Yes. In Oracle nested views can behave badly. We've experienced this ourselves when comparing expanded queries versus the same queries defined as multiple views inside Oracle. – Jason Cohen Feb 22 '09 at 22:18
  • I'll be interested - and surprised - to see if we get any corroboration on that. It would be a pretty devastating indictment of any commercial DBMS product. – dkretz Feb 22 '09 at 22:50
1

It depends on the database and the definition of the view.

A simple view that aliases columns or performs simple calculations will not be different from making the query directly.

However, in some cases the views can be much slower. An example: In Oracle, if you nest view queries too much (e.g. one view uses another as a table, which uses another, etc.), you can create awful performance.

In general you need to test with the specific database and queries in question.

Jason Cohen
  • 81,399
  • 26
  • 107
  • 114
  • Are you claiming that using the view makes your query slower than the inline subselect it represents? – Khb Feb 22 '09 at 21:59
  • If it's inefficient, I'd bet that it's because the views are inappropriately adding more fields and/or joins than are needed. But it's still the same performance as the equivalent single SQL statement. – dkretz Feb 22 '09 at 21:59
  • 1
    No, we've tried using the SAME queries, just in views or in selects with sub-queries. The latter can be much faster. Why? Dunno, Oracle is full of mystery, and not in a good way. – Jason Cohen Feb 22 '09 at 22:19
  • No, I'm not claiming views are automatically slower; indeed they are usually faster just because they're precompiled. But unfortunately reality isn't the same as theory with complex views. – Jason Cohen Feb 22 '09 at 22:19
0

Think of it this way:

A view is just a select statement that lives on the server and has been compiled by the SQL engine.

Usually views are used to limit/simplify the results from the table.

Regards K

Khb
  • 1,423
  • 9
  • 9