Best practises : is sql views really worth it?

Question

I am building a new web applications with data stored in the database. as many web applications, I need to expose data from complexe sql queries (query with conditions from multiple table). I was wondering if it could be a good idea to build my queries in the database as sql view instead of building it in the application? I mean what would be the benefit of that ? database performance? do i will code longer? debug longer?

Thank you

Cautionary note...we had an ancient view that had become a defacto standard throughout the site, It was being used in many, many places as though it was a table. The view sorted a number of fields, then it's use elsewhere also sorted that result.......nice and slow — GDP, Jun 13 '12 at 15:00

ewernli · Accepted Answer · 2012-06-13T15:02:09.990

This can not be answered really objectively, since it depends on case by case.

With views, functions, triggers and stored procedures you can move part of the logic of your application into the database layer.

This can have several benefits:

performance -- you might avoid roundtrips of data, and certain treatment are handled more efficiently using the whole range of DBMS features.
consisness -- some treatment of data are expressed more easily with the DBMS features.

But also certain drawback:

portability -- the more you rely on specific features of the DBMS, the less portable the application becomes.
maintenability -- the logic is scattered across two different technologies which implies more skills are needed for maintenance, and local reasoning is harder.

If you stick to the SQL92 standard it's a good trade-off.

My 2 cents.

score 1 · Answer 2 · answered Jun 13 '12 at 14:56

I think your question is a little bit confusing in what you are trying to achieve (Gain knowledge regarding SQL Views or how to structure your application).

I believe all database logic should be stored at the database tier, ideally in a stored procedure, function rather in the application logic. The application logic should then connect to the database and retrieve the data from these procedures/functions and then expose this data in your application.

One of the the benefits of storing this at the database tier is taking advantage of the execution plans via SQL Server (which you may not get by accessing it directly via the application logic). Another advantage is that you can separate the application, i.e. if the database needs to be changed you don't have to modify the application directly.

For a direct point on Views, the advantages of using them include:

Restrict a user to specific rows in a table. For example, allow an employee to see only the rows recording his or her work in a labor- tracking table.
Restrict a user to specific columns. For example, allow employees who do not work in payroll to see the name, office, work phone, and department columns in an employee table, but do not allow them to see any columns with salary information or personal information.
Join columns from multiple tables so that they look like a single table.

http://msdn.microsoft.com/en-us/library/aa214068(v=sql.80).aspx

score 0 · Answer 3 · answered Jun 13 '12 at 14:50

0

Personally I prefer views, especially for reports/apps as if there are any problems in the data you only have to update a single view rather than re-building the app or manually editing the queries.

answered Jun 13 '12 at 14:50

bendataclear

3,802
3
32
51

Girish Rao · Answer 4 · 2012-06-13T14:58:45.200

You can run into performance issues and constraints on the types queries you can run against a view.

Restrictions on what you can do with views.

http://dev.mysql.com/doc/refman/5.6/en/view-restrictions.html
Looks like the big one is that you cannot create an index on the view. This could cause a big performance hit if your final result table is large
This is also a good forum discussing views: http://forums.mysql.com/read.php?100,22967,22967#msg-22967

In my experience a well indexed table, using the right engine, and properly tuned (for example setting an appropriate key_buffer value) can perform better than a view.

Alternatively you could create a trigger that updates a table based on the results of other tables. http://dev.mysql.com/doc/refman/5.6/en/triggers.html

score 0 · Answer 5 · answered Jun 13 '12 at 14:51

0

SQL views have many uses. Try first reading about them and then asking a more specific question:

answered Jun 13 '12 at 14:51

Milimetric

13,411
4
44
56

I think the underlying point of the question is specific enough, it's just not worded particularly well. – Anthony Grist Jun 13 '12 at 14:53

score 0 · Answer 6 · answered Jun 13 '12 at 14:56

I have seen that views are used a lot to do two things:

Simplify queries, if you have a HUGE select with multiple joins and joins and joins, you can create a view that will have the same performance but the query will be only a couple of lines.
For security reason, if you have a table with some information that shouldn't be accessed for all the developers, you can create views and grant privileges to see the views and not the main table, I.E: table 1: Name, Last_name, User_ID, credit_card, social_security. You create a view table.table view: name, last_name, user_id .

score 0 · Answer 7 · answered Jun 13 '12 at 15:08

The technic you are saying is called denormalization. Cal Henderson, software engineer from Flickr, openly supports this technic.

In theory JOIN operation is one of the most expensive operations, so it is a good practice to denormalize, since you are transforming n queries with m JOIN in 1 query with m JOIN and n queries that select from a view.

That said, the best way is to test it for yourself. Because what could be incredibly good for Flickr may not be so good for your application.

Moreover, the performance of views may vary a lot from one RBDMS to another. For instance, depending on the RBDMS views can be updated when the orginal table is changed, can have indexes, etc.

Best practises : is sql views really worth it?

7 Answers7