SQL Server Performance

Do Index on table speed up query on View

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by arobadol, May 24, 2006.

  1. arobadol New Member

    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.



    arobadol
  2. satya Moderator

    http://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.
  3. arobadol New Member

    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
  4. nigelrivett New Member

    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.
  5. arobadol New Member

    Tahnks for the info.

    I will create index and see how teh system reacts.



    arobadol
  6. derrickleggett New Member

    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
    derrickleggett@hotmail.com

    When life gives you a lemon, fire the DBA.
  7. samsoft New Member

    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

Share This Page