I created an indexed view that joined a number of tables to get better performance, but when I use the indexed view, the performance is not better than before. When I survey the execution plan, I don't see any change between the view and the indexed view on these joined tables.
Asked
Active
Viewed 1,380 times
2
GarethD
- 68,045
- 10
- 83
- 123
mehdi lotfi
- 11,194
- 18
- 82
- 128
-
1Put (noexpand) hint after view name in from: `select * from a_view (noexpand)` – Nikola Markovinović Jun 13 '12 at 08:53
-
Maybe the columns you chose for index are not optimal, you could review the Database Engine Tuning Advisor's recommendations for your view. – Ivan Golović Jun 13 '12 at 08:55
-
@Nikola Markovinovic, Tanks a lot. But I use this index view in Entity Framework. How I can put hint for index view in Entity Framework. – mehdi lotfi Jun 13 '12 at 08:58
-
I don't know. Hint is only needed in a query involving joins, though. – Nikola Markovinović Jun 13 '12 at 09:00
-
2One workaround suggested [here](http://stackoverflow.com/a/3135695/15498) (for Linq to SQL, but should work the same) is to create a second view that just selects from the first view and specifies the `NOEXPAND` hint. – Damien_The_Unbeliever Jun 13 '12 at 09:55
1 Answers
6
If you are not using sql server enterprise edition (see feature Automatic use of indexed views by query optimizer), sql server query optimizer is not taking in account indexed views..
In other versions you can make sql server use it- With (NoExpand):
Select col1, col2, col3
From dbo.vw_MyView With (NoExpand)
