26

Are database views only a means to simplify the access of data or does it provide performance benefits when accessing the views as opposed to just running the query which the view is based on? I suspect views are functionally equivalent to just the adding the stored view query to each query on the view data, is this correct or are there other details and/or optimizations happening?

Morten Christiansen
  • 19,002
  • 22
  • 69
  • 94
  • 1
    I'm looking forward to reading the answers. From practical experience, IF the resulting result set is significant, we've found we get better performance by in-lining the view within a procedure to trim the result set. – SAMills Feb 09 '09 at 18:29
  • Just a link to [this SO answer](https://stackoverflow.com/a/439061) (although specific to sql server). – djvg Dec 19 '18 at 10:55

7 Answers7

9

I have always considered Views to be like a read-only Stored Procedures. You give the database as much information as you can in advance so it can pre-compile as best it can.

You can index views as well allowing you access to an optimised view of the data you are after for the type of query you are running.

Robin Day
  • 100,552
  • 23
  • 116
  • 167
  • Any real examples of this ? Do you know any RDMS that will perform quicker on a view than on a equivalent select statement ? Of course index helps, but that index also helps the select statement. Your answer implies that a view is faster, I was just wondering if you have proof or just guessing ? – sindre j Feb 09 '09 at 19:00
  • 1
    No DB server I'm aware of creates a query execution plan at the time of view or stored procedure creation. Execution plans require index statistics, which are not available at compile time. The compilation just transforms the SQL/DDL into an internal format. – Craig Stuntz Feb 09 '09 at 19:04
  • sindre, some DB servers support "indexed views," which are more like "materialized views" -- closer to an automatically maintained table than a view per se. – Craig Stuntz Feb 09 '09 at 19:08
9

Although a certain query running inside a view and the same query running outside of the view should perform equivalently, things get much more complicated quickly when you need to join two views together. You can easily end up bringing tables that you don't need into the query, or bringing tables in redundantly. The database's optimizer may have more trouble creating a good query execution plan. So while views can be very good in terms of allowing more fine grained security and the like, they are not necessarily good for modularity.

Craig Stuntz
  • 125,891
  • 12
  • 252
  • 273
8

It depends on the RDBMS, but usually there isn't optimization going on, and it's just a convenient way to simplify queries. Some database systems use "materialized views" however, which do use a caching mechanism.

Christian Oudard
  • 48,140
  • 25
  • 66
  • 69
5

Usually a view is just a way to create a common shorthand for defining result sets that you need frequently.

However, there is a downside. The temptation is to add in every column you think you might need somewhere sometime when you might like to use the view. So YAGNI is violated. Not only columns, but sometimes additional outer joins get tacked on "just in case". So covering indexes might not cover any more, and the query plan may increase in complexity (and drop in efficiency).

YAGNI is a critical concept in SQL design.

dkretz
  • 37,399
  • 13
  • 80
  • 138
  • 1
    Funny how I always adhered to the YAGNI principles but didn't know they actually existed until I looked up YAGNI :) – SAMills Feb 09 '09 at 18:42
3

Generally speaking, views should perform equivalently to a query written directly on the underlying tables.

But: there may be edge cases, and it would behoove you to test your code. All modern RDBMS systems have tools that will let you see the queryplans, and monitor execution. Don't take my (or anybody else's) word for it, when you can have the definitive data at your fingertips.

Michael Dorfman
  • 4,060
  • 1
  • 22
  • 29
2

Yes, in all modern RDBMS's (MSSQL after 2005? etc) view's query plans are cached removing the overhead of planning the query and speeding up performance over the same SQL performed in-line. Previously to this (and it applies to parameterized SQL/Prepared Statements as well) people correctly thought stored procedures performed better.

Many still hang onto this today making it a modern DB myth. Ever since Views/PS's got the cached query planning of SPs they've been pretty much even.

Codex
  • 131
  • 6
2

I know this is an old thread. Discussion is good, but I do want to throw in one more thought. Performance also depends on what you are using to pull data with. For example, if you are front-ending with something like Microsoft Access you can definately gain performance for some complex queries by using a view. This is because Access does not always pull from the SQL server as we would like -- in some cases it would pull entire tables across then try to process locally from there! Not so if you use a view.

Michael
  • 21
  • 1