-1

Is a database view simply a way to alias a more complicated query into a shorter one?

If so, are there performance impacts of using them?

Marty Wallace
  • 34,046
  • 53
  • 137
  • 200
  • You can see here a discussion on the subject: http://stackoverflow.com/questions/529259/do-database-views-affect-query-performance – Rui Simoes May 23 '13 at 19:17

2 Answers2

0

Same code used as view or typed in SQL editor/embedded in application will not affect performance.

It's works similar to alias.

Kamil
  • 13,363
  • 24
  • 88
  • 183
0

I can't speak specifically to mysql but these are the performance problems I have run into when developers use views.

First rather than write the specific query they need, they use a view which may have columns or even joins they don't need. It always costs in terms of performance to return more data than you intend to use. Saving a couple of minutes of development time by adding time to every query that is run is short-sighted at best. Code-reuse in SQL is often a poor idea unless you need the exact same code.

In SQL Server, a particularly bad thing can happen when views call views. The underlying views may need to be generated completely and thus you can genrate 20,000,000 records in order to eventually return 3. It might be true for mysql as well, maybe someone who has had this experience (and we almost lost a multi-0million dollar contract over this, so it was very painful) can tell you.

Further you might end up joining multiple times to the same multi-million record table in different layers of the views because they don't know it has already been joined to or they want a column not in the orginal view and don't want to break other things by adjusting the view. You might even run out of allowable levels of joining as we did once in a particulary poorly-designed database I once had the misfortune to have to support.

Views certainly have their uses, but I would be very wary of using them in layers (And they are much harder to maintain once you start to layer them too!)

Now views are good when you need complex logic that will be used in multiple places such as financial calculations. They can also be used to limit data the user can see.

Another use of views is in refactoring databases. You can hide underlying structural changes and avoid breaking code by judiciously using views. This book can help you learn about this use for views: http://www.amazon.com/Refactoring-Databases-Evolutionary-Addison-Wesley-ebook/dp/B001QAP36E/ref=tmm_kin_title_0?ie=UTF8&qid=1369337324&sr=1-1

Indexed views may even improve performance.

HLGEM
  • 94,695
  • 15
  • 113
  • 186