Page 1 of 3 123 LastLast
Results 1 to 15 of 44
  1. #1
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51

    Unanswered: Index a view or a table

    I have a table that has thousands of rows inserted daily (rows are seldom updated or deleted)

    The table is also involved in frequent non-simple select statements. It currently has about a million rows.

    Out of the 15 odd columns in the table, I can see about 6 that would benefit being indexed to speed up the select statements.

    Before I do this, I was wondering if people think that perhaps I should create an indexed view that all select statements use, rather than adding indexes directly to the table.

    Can anyone advise me the performance benefits/disadvantages of indexed views over indexed tables?

    Thanks

  2. #2
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734

    Re: Index a view or a table

    Originally posted by mattkrevs
    I have a table that has thousands of rows inserted daily (rows are seldom updated or deleted)

    The table is also involved in frequent non-simple select statements. It currently has about a million rows.

    Out of the 15 odd columns in the table, I can see about 6 that would benefit being indexed to speed up the select statements.

    Before I do this, I was wondering if people think that perhaps I should create an indexed view that all select statements use, rather than adding indexes directly to the table.

    Can anyone advise me the performance benefits/disadvantages of indexed views over indexed tables?

    Thanks
    If you use the six in your join and where clauses but you still need all 15, then you should go with indexes on the table. Otherwise, look at an indexed view.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  4. #4
    Join Date
    Oct 2003
    Location
    Newcastle, Australia
    Posts
    51
    Originally posted by Satya
    http://www.sqlteam.com/item.asp?ItemID=1015 for more information.
    nice article.
    thanks

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't see that you gain anything by using an indexed view based on a single table, especially if you aren't even aggregating the recordset.

    Plus, an indexed view will slow down your inserts.

    Just add indexes to your table. Composite indexes may help specific querys as well. Have you run a showplan against your queries to see where delays are occuring?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Sep 2003
    Posts
    522
    Originally posted by blindman
    I don't see that you gain anything by using an indexed view based on a single table, especially if you aren't even aggregating the recordset.
    you don't see, blindman, that's right (pun intended). do you even know the rules of creating indexed views? aggregation? you can't aggregate an indexed view with anything other than count_big(*)!!! do you even know what you are talking about before making such suggestions?
    Originally posted by blindman
    Plus, an indexed view will slow down your inserts.
    man, you just spit out guessing after guessing. how do you figure? so you're saying that if inserts are coming in in a certain order and the poster builds an index on a view (clustered to start with) that accomodates for this order, then the index will slow down the inserts?

    and at the same time you're giving suggestions to "just add indexes to the table"???

    i love this forum, unsupported ambitions are all over the place
    Last edited by ms_sql_dba; 04-10-04 at 20:19.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    holy flame war, batman

    look, ms, i don't care what you and blindman have going on between yourselves, there's no reason to do what you just did

    please, keep your personal bitterness out of your posts

    refute the facts but please keep the vitriol to yourself

    when you insult someone out of the blue like that, it makes you look like an idiot
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    I thought that was "an ASS out of "U"and "ME"" ?


  9. #9
    Join Date
    Sep 2003
    Posts
    522
    Originally posted by r937
    holy flame war, batman

    look, ms, i don't care what you and blindman have going on between yourselves, there's no reason to do what you just did

    please, keep your personal bitterness out of your posts

    refute the facts but please keep the vitriol to yourself

    when you insult someone out of the blue like that, it makes you look like an idiot
    before jumping on me you should read his posts

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by ms_sql_dba
    before jumping on me you should read his posts
    I can't speak for anyone else, but I already did.

    I'm with r937, dispute the facts if you will, but I'm not interested in what appears to be a vicious response to a civil posting. I don't care whether the original posting was technically accurate or not, that isn't relevant to this observation. I don't think that your response was appropriate.

    -PatP

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Originally posted by ms_sql_dba
    before jumping on me you should read his posts
    i did, there's only one post of his in this thread, and it was friendly

    if you are talking about other threads, they do not matter to this one

    this thread will be found independently by people unaware of your own personal vendetta

    please, be more civil
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Sep 2003
    Posts
    522
    ok, then i'll just restate what i said in my original post while omitting "personal vendetta" comments:

    using indexed views is more efficient than creating indexes on the underlying tables.

    does this look better to you all?

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Originally posted by ms_sql_dba
    ok, then i'll just restate what i said in my original post while omitting "personal vendetta" comments:

    using indexed views is more efficient than creating indexes on the underlying tables.

    does this look better to you all?
    Way more gooder, yet even!

    Now, all that one of you two needs to do is to come up with something (an URL, a reference to readily available printed material, etc) to support your opinions, then we'll all have something to discuss!

    Pretty quick I need to prepare to pig out! You guys go on and debate stuff without me, I'll join in later if I can waddle to the tube!

    -PatP

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Let' start at the top...

    "you can't aggregate an indexed view with anything other than count_big(*)!!!"
    Wrong. You can also aggregate with SUM, and while you can't directly use the AVG, STDEV, STDEVP, VAR, or VARP functions, you can reproduce them using combinations of SUM and COUNT_BIG. Look it up yourself; its easy to find in Books Online.

    "do you even know what you are talking about before making such suggestions?"
    Yes.

    "so you're saying that if inserts are coming in in a certain order and the poster builds an index on a view (clustered to start with) that accomodates for this order, then the index will slow down the inserts?"
    Yes, I am. Indexed views are stored in the database, and it stands to reason that since they reflect any updates on their underlying tables then the process of updating the indexed view will require processor time. From Books Online:
    "You should create indexes only on views where the improved speed in retrieving results outweighs the increased overhead of making modifications. This usually occurs for views mapped over relatively static data, that process many rows, and are referenced by many queries."

    "and at the same time you're giving suggestions to "just add indexes to the table"???"
    Yes. I have found, after long years of experience, experiment and investigation, that indexes tend to speed up query processing. What makes no sense is to create an indexed view of a single base table, thus creating a copy of it, and then index the copy.

    "using indexed views is more efficient than creating indexes on the underlying tables."
    Yes, it does look better. But it is still wrong if you are only dealing with a single table without aggregation, as a stated in my post. I hope you don't create indexed views on all of your base tables instead of simply indexing the base tables themselves.

    'Nuff said.
    Last edited by blindman; 04-12-04 at 00:56.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    100 human (hu-man)
    200 noun.
    300 A carbon based device that allows the user to quickly and
    400 efficiently repeat the same mistake 50,000 times.
    500 goto 100
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •