are Views useful for efficiency?

using a view to do common JOINs

[httpwebwitch]

I have some tables that are always being joined together. Products, JOINed to Manufacturers and Categories and Vendors, and a few other supplemental tables. Seems like every time I ask for a product, I am always JOINing it with at least three other tables to get the Manufacturer name, Category ID, etc.

So I was wondering whether it might be worthwhile to create a View of all that - a View which joins all the tables in a typical way. Then my Stored Procedures, instead of Joining Joining Joining, can just do their selection from that View.

My question: Does a View make my query more efficient? If the View is somehow pre-rolled, pre-joined and waiting for a simple SELECT WHERE query, or does the View do the joining at the time it is being accessed?

Any suggestions as to the proper and intelligent use of Views to facilitate complex INNER JOINed queries?

[FalseDawn]

I normally consiser views as an aid to organization rather than a means to increase performance.

For simple inner joins, my experience is that the performance gain (if any) will be minimal at best, since you really only gain the time it takes for the query optimizer to do its job, which is usually insignificant in relation to the time needed for actual lookup and retrieval of data.

If you find that every query involves multiple table joins, you might consider doing some degree of denormalizing to speed up frequently executed queries.

[ixyst]

In SQL Server, you can create an indexed view. This will really speed up the joins, because it is essentially a denormalized table that you can create indexes on...

The problem is, there are very strict requirement when creating such a view. Do a search for "SQL Server indexed views" and you will get a lot of results explaining them... In some cases, they can increase the performance quite a bit, but this comes with some effort and some changes to the db.

[aspdaddy]

Views also increase database security. If you have common tables being joined, make it a view and deny SELECT permissins on the base tables.

Views are nearly always the entities you identified during logical design (if you did one!). Indexed views are usually done after first release for optimisation.