Could it be a sensible practice to question sights rather than the raw tables in saved methods? (even when the vista does not provide different data)
I usually figured it may be advisable, because its an additional layer of abstraction and it is much like using qualities rather than member variables at school functions.
However I had been searching in the saved methods produced through the ASP.Internet Membership Provider plus they always query from the tables directly.
I know that you simply cant place data easily when utilizing sights, but when the saved procedure only queries data, in the event you still use tables directly? If so, what is the primary reason? (performance?)
A view is simply a macro that grows into an outer query.
In case your view consists of several joins, when you join if with other sights you all of a sudden possess a 20 or 30 way JOIN whenever you really see 3 JOINs within the SQL from the saved procedure. You may have that every totally different: why keep joining exactly the same 20 or 30 tables for each query?
Generally, there's no benefit unless of course the vista is indexed/materialised and also the optimiser may use it.
Ideas for example getting information on one table masked with a view ought to be inside a calculated column: why keep calculating it? For any calculation on multiple tables inside a view, it ought to be indexed.
Utilizing a saved procedure already means no base table access (possession chaining).
You will find good uses of sights to prevent direct table access by customers, in order to mask schema changes, or provide some fundamental security (eg according to SUSER_SNAME), although not for performance or idealogy
Different database optimizers optimize queries diversely, so it is not a simple answer. But generally adding a layer of abstraction can (not certainly) steer clear of the optimizer by using indexes properly.
In Sql Server for those who have a where clause that consists of calls like:
- IS NULL
- LIKE '%something'
- NOT Is available
it causes it to be a non-sargable query, ie a question that doesn't use indexes - or uses them sub-brilliantly.
I'd reckon that utilizing a view will even modify the sarg. I'll go and test this (in Sql Server) - I'm going to be in a few minutes.
I suppose that the reply is 'it depends' which you have to switch on your query execution plan to be certain, the next test which i did demonstrated no distinction between a querying an easy view with different table and just querying the actual table. However it needs further testing like a complex view could act in a different way.
I have tried personally sights in saved methods during these two cases:
1 - for complex joins or conditions that are required in multiple methods
2 - to substitue for any re-named table. For instance I'd two tables, known as 'member' and 'non_member'. I later made the decision to mix these right into a 'user' table. To be able to avoid modifying every proc I'd ever written, I produced sights known as 'member' and 'non_member', which used where clauses to filter the 'user' table properly. All of the procs went because they accustomed to without changes. I'm able to change these to access the brand new table directly after i have enough time.