Query is faster then query on view

Jem 2013-09-18 09:15:41

I have query which joins few tables and is performing fine. I have now created view on the query and now when I query some columns on view it is 4X slower then actual query.

Query (Faster):
select * from few joins
where a=this
b =that

(2 seconds)

View (Slower): view is created with same select statement as above query but without where clause
then calling view
set @a=this
set @b=that
set @c=blabla

select * from view
where a=@a
and b=@b
and c=@c

(9 Seconds)
SQL Plans are attached for reference.

SQLkiwi 2013-09-18 09:28:22
The query with constant literals gives the optimizer much better information to estimate cardinality. The query using variables results in guessing or using average values.

Also, one of your variables is using a different type from the view column it is compared to resulting in a dynamic seek (the Constant Scan and Compute Scalar).

Using correct types and constants instead of variables should give the same execution plan and performance. Alternatively, add OPTION (RECOMPILE) to the view statement (and fix the variable type) to allow the optimizer to see the value of the local variable.

Jem 2013-09-18 12:08:34
Changing variable type didn't help a lot but OPTION (RECOMPILE) has fixed now it is choosing better execution plan.Thanks for help.