Query optimization - joining a view and a Table

I am having the following situation - there is a view that aggregates and computes some values and a table that I need the details from so I join them filtering on the primary key of the table. The execution plan shows that the view is executed without any filtering so it returns 140 000 rows which are later filtered by the join operation a hash table match. This hash table match takes 47% of the query cost. I tried selecting the same view but directly giving a where clause without the join – it gave a completely different execution plan. Using the second method is in at least 4 folds faster and is going only through Index Seeks and nested loops.

So I tried modifying the query with third version. It gave almost the same execution plan as the version 1 with the join operation.

It seams that by giving the where clause directly the execution plan chosen by the query optimizer is completely different – it filters the view and the results from it and returns it at the same time, in contrast to the first version where the view is executed and return and later filtered. Is it possible to change the query some how so that it filters the view before been joined to the table.

Any suggestions will be appreciated greatly

Stoil Pankov

"vHCItemLimitUsed" - this is the view

"tHCContractInsured" - this is the table

"ixHCContractInsuredID" - is the primary key of the table

Here is a simple representation of the effect:

Version 1:

select *
from dbo.vHCItemLimitUsed
inner join tHCContractInsured on
vHCItemLimitUsed.ixHCContractInsuredID = tHCContractInsured.ixHCContractInsuredID
where tHCContractInsured.ixHCContractInsuredID in (9012,9013,9014,9015)

Version 2:

select *
from vHCItemLimitUsed
where ixHCContractInsuredID in (9012,9013,9014,9015)

Version 3:

select *

from dbo.vHCItemLimitUsed

where ixHCContractInsuredID in

(select ixHCContractInsuredID

from tHCContractInsured

where ixHCContractInsuredID in (9012,9013,9014,9015))

Are we talking milliseconds, seconds, minutes, or hours? If seconds, then it just might be one of those case where it is too costly to do the whole optimization process and it is just faster to execute the query. What version/edition of SQL Server also?

Another version that might work for you is:

select *
from (select *
from vHCItemLimitUsed
where ixHCContractInsuredID in (9012,9013,9014,9015)) as limitUsed
join tHCContractInsured on
limitUsed.ixHCContractInsuredID = tHCContractInsured.ixHCContractInsuredID

If we are talking about minutes then it might be a bug. Can you post some more information:

The plans of the queries
The structure of the view
The structure of the tables
The amount of data