When are SQL views appropriate in ASP.net MVC?

I've got a table called Protocol, a table called Eligibility, and a Protocol_Eligibilty table that maps the two together (a many to many relationship). If I wanted to make a perfect copy of an entry in the Protocol table, and create all the needed mappings in the Protocol_Eligibility table, would using an SQL view be helpful, from a performance standpoint? Protocol will have around 1000 rows, Eligibility will have about 200, and I expect each Protocol to map to about 10 Eligibility rows and each Eligibility to map to over 100 rows in Protocol.

Here's how I'm doing this with the view:

var pel_original = (from pel in _documentDataModel.Protocol_Eligibility_View
                                where pel.pid == id
                                select pel);

Protocol_Eli开发者_开发技巧gibility newEligibility;

foreach (var pel_item in pel_original)
{

    newEligibility = new Protocol_Eligibility();

    newEligibility.Eligibility = (from pel in _documentDataModel.Eligibility
                                              where pel.ID == pel_item.eid
                                              select pel).First();

    newEligibility.Protocol = newProtocol;

    newEligibility.ordering = pel_item.ordering;

    _documentDataModel.AddToProtocol_Eligibility(newEligibility);

}

And this is without the view:

var pel_original = (from pel in _documentDataModel.Protocol_Eligibility
                                where pel.Protocol.ID == id
                                select pel);

Protocol_Eligibility newEligibility;

foreach (var pel_item in pel_original)
{
    pel_item.EligibilityReference.Load();

    newEligibility = new Protocol_Eligibility();

    newEligibility.Eligibility = pel_item.Eligibility;

    newEligibility.Protocol = newProtocol;

    newEligibility.ordering = pel_item.ordering;

    _documentDataModel.AddToProtocol_Eligibility(newEligibility);

}

---

Views have no performance impact in SQL Server. Selecting from a view or selecting from the query on which the view is based is, in every aspect, identical. In fact the view is expanded into its definition when the query plan is built.

The only time when views can impact performance is when an indexed view exists, or a materialized view as they are called in Oracle. But a indexed view performance gain come form the index, not from the view. In fact, selecting from any query that can leverage the indexed view will leverage it, not only selecting from the view. Indexed views are not considered in non-Enterprise editions (Standard, Express).

In fact, whenever discussing performance in SQL Server, or any relational database for the matter, the question is never about how to design the queries, the queiston is always about how to design the schema. Proper clustered index and proper non-clustered indexes, that what will give performance, not views.

The fact that I do not mention linq, nor MVC, nor ASP in the response is simply because the are completely irrelevant when discussing SQL performance.

---

With row counts that small, performance optimizations are not going to make any appreciable difference on modern desktop or server hardware. Unless this is an embedded system on extremely constrained resources, you're better off allocating your development time elsewhere.