SQL Server Performance Forum – Threads Archive
Do Index on table speed up query on View
Hi all, I’m currently tweaking my sql server 2005 DB. I’m not an SQL Server expert so forgive me if my question sounds "stupid". I have a view running on top of several table. My question is can I speed up queries on the view if I create additional index on the column used for the query on the table the columns belongs? I have already considered index on the views but my veiws is too complex to allow me to create index on it. thanks for your help. arobadolhttp://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Thanks for the document but I knew it already… 🙁 Unfortunatelly it does not reply to my question. Or maybe a skiped the section talking about it. My problem is that my views:
are using functions wich access data.
use other views. I cannot create index on the view then… I just want to know if an index on table will impact the performance when querying a view based on the table filtering ont the column include in the table index?. Any idea? arobadol arobadol
The optimiser will look at the underlying tables when creating the plan so it should end up the same as if the view wasn’t there and you coded everything in the query. Doesn’t mean that the optimiser is perfect though so don’t rely on it.
Whether or not the index is used will depend on the query and whether the view confuses the optimiser.
Tahnks for the info. I will create index and see how teh system reacts. arobadol
After you create the index, let us know the results. If you could post a specific example with sample table structure and queries, we could help you out a lot easier. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
this topic may help u. index your view instead of table
http://technet.microsoft.com/en-us/library/cc917715.aspx
you should check your situations with large amount of data.
what i gained is when your indexed column in table is a foreign key, it may improve your speed
]]>