Optimizing view to read rows as per usage statistics

This is one (of many) downsides of EAV designs.

You can't really improve the JOIN: because of the necessary complexity, a cost based optimiser won't get to the perfect plan. It finds "good enough"

Suggestions:

• don't use a view: use aggregate type queries (eg COUNT(*) = 2 if I match both height and weight)
• use a trigger to maintain a real (or sparse) table and query that

The first option scales better becauses a few indexes on the main EAV fact table can cover all queries nicely.

Your thinking is correct. Take the below example on a non-indexed view:

To create the test view:

use AdventureWorks2012;
go

create view dbo.DepartmentView
as

    select
        Name,
        GroupName
    from HumanResources.Department;

go

Now if you were to look at the execution plan of a SELECT on the view:

select *
from dbo.DepartmentView;

You can see below that the index of the underlying table is referenced (HumanResources.Department):

Likewise, when you update the view, you will see similar behavior:

update dbo.DepartmentView
set GroupName = 'QA'
where GroupName = 'Quality Assurance';

You can also write a few queries against the system catalog views to show that the only thing that is persisted is the definition of the view:

-- see the object in sys.objects
select *
from sys.objects
where object_id = object_id('dbo.DepartmentView');

-- ensure there are no indexes related to this view object
select *
from sys.indexes
where object_id = object_id('dbo.DepartmentView');

-- the definition is persisted
select
    object_name(object_id) as object_name,
    definition
from sys.sql_modules
where object_id = object_id('dbo.DepartmentView');