Use SQL View or SQL Query?

Question

I am working on an application to get data from a MS-SQL server (2005). In the command text, I can pass a sql query like this:

string query = "SELECT T1.f1, T1.f2, T2.f3 FROM table1 T1 join table2 T2" +
   "on T1.id = T2.id AND T1.dt = T2.dt ..."
....
cmd.CommandText = query;

I could also put the query as a view on my SQL server like this:

 CREATE VIEW V1 AS
   "SELECT T1.f1, ..."

Then I can use the view in a simplified query like this:

 string query = "SELECT f1, f2, f3 FROM V1";
 ....
 cmd.CommandText = query;

I am not sure which way is better. Will be the view be faster then a SQL query? By the way, the query I show here is a simplified one. The actual query SELECT is more complicated one.

Answer 1

Or you could use a stored procedure. Using a stored proc will allow SQL to cache the execution plan. I think the same is true of a view. Your first method (ad-hoc query) will probably be the least efficient.

Answer 2

The view is probably a better.

That way, you can modify the query (for optimization) at a later date without having to change the code.

Answer 3

The View may be faster, because you are requesting a shorter command string, but the difference is going to be inconsequential.

The real difference and value of Views, is that like functions and subroutines, they are reusable.

Answer 4

There is no performance difference (unless the text of the sql query is truly gigantic and incurs a cost on the wire).

Answer 5

I would create a VIEW for several reasons

A) A well constructed view does tend to perform faster than a query, though with query optimization you may not notice much of a difference.

B) It keeps knowledge of the database structure within the database itself - adding a good layer of abstraction (as a side note, consider using a stored procedure rather than an inline query - this also keeps database knowledge within the database itself)

C) If you do need to make a structural change to the database, you can keep the view consistent without needing to rebuild your code.

AMMENDMENT I'm going to ammend this answer in light of some of the comments so as to clarify some points ...

It is absolutely true that a standard view does not provide any real performance gain over a query. A standard view is materialized at run time which essentially makes it no different than a convenient way to execute a query of the same structure. An index view, however, is materialized immediately and the results are presisted in physical storage. As with any design decision, the use of an indexed view should be carefully considered. Thiere is no free lunch; the penalty you pay for use of indexed views comes in the form of additional storage requirements and overhead associated with maintaining the view when there are any changes to the underlying database. These are best used in instances of commonly used complex joining and aggregation of data from multiple tables and in cases in which data is accessed far more frequently than it is changed.

I also concur with comments regarding structural changes - addition of new columns will not affect the view. If, however, data is moved, normalized, archived, etc it can be a good way to insulate such changes from the application. These sitauations are RARE and the same results can be attained through the use of stored procedures rather than a view.

Answer 6

In general I've found it's best to use views for several reasons:

• Complex queries can get hard to read in code
• You can change the view without recompiling
• Related to that, you can handle changes in the underlying database structure in the view without it touching code as long as the same fields get returned

There are probably more reasons, but at this point I would never write a query directly in code.

You should also look into some kind of ORM (Object Relational Mapper) technology like LINQ to SQL (L2S). It lets you use SQL-like queries in your code but everything is abstracted through objects created in the L2S designer.

(I'm actually moving our current L2S objects to run off of views, actually. It's a bit more complicated because the relationships don't come through as well... but it's allowing me to create one set of objects that run across 2 databases, and keep everything nicely abstracted so I can change the underlying table names to fix up naming conventions. )

Answer 7

To me the advantage of views is that you can apply a security context to them. Or in extreme scenarios you can use distributed partitioned views for performance reasons. Otherwise they complexify versioning. You now have to make sure the correct version of the view is deployed as well as the new build of your data access dll.

I used to argue that stored procs were the way to go because they are a) compiled (faster) and b) a security boundary. In the real world this is often a premature optimization. The SQL command text sent from the client is usually peformant enough (as well as easier to version and deploy) and access control at the table level or even the entire database is adequate in many cases.

Answer 8

Views aren't a performance feature. They exist to reduce the number of joins and to allow you to denormalize without actually denormalizing.

In other words, use whichever makes your code the simplest and don't change for performance reasons unless you have good reason to do so. These kinds of optimizations usually aren't worth it.

Answer 9

Unless you fit the profile in the 2nd paragraph, I'd recommend staying away from views. They're deceptively enticing as a good way to abstract out underlying functionality. But the problem is that once developers start creating views, they tend to start creating different views for every possible combination of tables and fields, and it ends up an uncoordinated explosive mess. The DBA then has to maintain all of them because there's no way of telling which ones are actually used and which ones aren't, and developers end up just writing their own joins anyway because it's easier than wading through and figuring out which existing view to use.

IMO, the only good reason to use views (and a fairly common one) is if the DBA needs to have freedom to modify the underlying tables without breaking existing client code. This is obviously only possible if that logic is on the DB side in a view or proc. If that applies to you, go ahead and use a view. Otherwise, I'd recommend a different way of looking at things.

The power of a view is the abstraction it creates. But the fact is, the abstraction is used only by the client, not by the DB itself. Therefore I find it's better to put that abstraction on the client side. So rather than a view, just define a macro or string generator somewhere in the client code that will create your select statement for you. You can make these simple at first and progressively more intricate as your app progresses. That way you keep the abstration and reusability a view would offer, but avoid the explosion of views and the developer-DBA communication bottlenecks.