February 7, 2012 at 8:23 am
Hi,
Today one of the proc in DB is running slow and when dug into it i found that the proc is using "index View" and the two columns in the view doesnt have any index(these two columns are used in "where" clause in proc). So i was thinking to have index on those two columns but i m confused is it better to have index on underlying "table" or index on view. Please let me know which one prefereble.
Thanks,
Sudheer
February 7, 2012 at 1:54 pm
Can you please post/attach your query execution plan?
It is my understanding (others than correct my ignorance if needed) that an indexed view is just that, it's "indexed", the entire view is indexed...including all the columns within it. So a SELECT statement with a WHERE clause should already be included in that.
How large is this indexed view? If it's large then you may be better off creating a non-clustered index on the actual table and dropping the view (if it's not needed elsewhere) to free up resources on the drive. Try both...compare the execution plans and then choose which one works best...
Also, have you recently rebuilt the indexed view? If not it could very well be that the data has changed so much in the underlying tables which make up the view that the statistics need updating. Bad statistics can often result in SQL choosing a "not so great" execution plan...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
February 7, 2012 at 2:28 pm
You're not off Jessie, but it will depend on the version of the SQL Server if it will use the view indexing or bypass to the tables depending on your hinting. See WITH NOEXPAND for more information.
OP, can you post the view definition, your query definition, schema if possible for the underlying tables (with indexes) and the .sqlplan? The second link down on the left in my signature will explain how to get them if you're not sure.
Also, are you on Express, Standard, or Enterprise?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply