Using views instead of tables in stored procedures?

Question

Is it a good practice to query views instead of the raw tables in stored procedures? (even if the view doesnt provide any different data)

I always figured it might be a good idea, because its an extra layer of abstraction and its similar to using properties instead of member variables in class functions.

But now I was looking at the stored procedures created by the ASP.NET Membership Provider and they always query against the tables directly.

I am aware that you cant insert data easily when using views, but if the stored procedure only queries data, should you still use tables directly? If yes, whats the main reason? (performance?)

Solution 1

A view is just a macro that expands into an outer query.

If your view contains several joins, then when you join if to other views you suddenly have a 20 or 30 way JOIN when you actually see 3 JOINs in the SQL of the stored procedure. You'll also find that each query is different: why keep joining the same 20 or 30 tables for every query?

Generally, there is no benefit unless the view is indexed/materialised and the optimiser can use it.

Ideas such as having calculations on a single table masked by a view should be in a computed column: why keep calculating it? For a calculation on multiple tables in a view, it should be indexed.

Using a stored procedure already means no base table access (ownership chaining).

There are good uses of views to avoid direct table access by users, or to mask schema changes, or provide some basic security (eg based on SUSER_SNAME), but not for performance or idealogy

Solution 2

Different database optimizers optimize queries in different ways, so its not an easy answer. But generally adding a layer of abstraction can (not definitely) stop the optimizer from using indexes correctly.

In Sql Server if you have a where clause that contains calls like:

• IS NULL
• LIKE '%something'
• NOT EXISTS

it makes it a non-sargable query, ie a query that does not use indexes - or uses them sub-optimally.

I would guess that using a view will also affect the sarg. I'll go and test this (in Sql Server) - I'll be back in 5 minutes.

EDIT

I guess that the answer is 'it depends' and that you need to turn on your query execution plan to be sure, the following test that I did showed no difference between a querying a simple view based on a table and simply querying the underlying table. But it needs further testing as a complex view could act differently.

Comments

[magnattic]

"I'll go and test this (in Sql Server) - I'll be back in 5 minutes." - heh, cool thanks. I will wait right here. :D