Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6

    Unanswered: EAV performance and clustered indexes on views

    OK so I have this EAV system on a server that is old enough for kindergarten. Insanely enough, this company that makes more money than any of your gods can not buy me a new box.

    Before you say "redesign", I need funding allocated for that. See my first statement.

    Anywho, I have this page that touches the dreaded Value table and does a clustered index seek on it. Can't search faster than that, right? Well I am getting some funding for "performance tuning". I am wondering if maybe incorporating some clustered index views involving the value table and producing a smaller clustered index for it to seek may alleviate some of this. Any thoughts?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Why does this make me think of attempting to decorate a manure pile with a can of spray paint?

    I would probably attack this using a materialized view if you are on an Enterprise Edition.

    -PatP

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    trust me I feel the same way. no such love. it's a LAN website. standard edition.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    oh yeah and the clustered index seek on my Value table (cringe) is 43% of my query cost.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am about to chase this pink elephant. No comments from mike_Bike_Kite?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Thrasymachus
    oh yeah and the clustered index seek on my Value table (cringe) is 43% of my query cost.
    But that's GOOD right? If a cheap step in the process takes up 43% of the whole process then you should pleased as punch. Is there some problem or something? Is the seek really slow in itself? How many rows returned?

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pat Phelan
    Why does this make me think of attempting to decorate a manure pile with a can of spray paint?

    I would probably attack this using a materialized view if you are on an Enterprise Edition.
    Only recently started using indexed views. We use standard 2005 - only on enterprise 2000?


    Also Sean - is this table highly transactional?.... I imagine that is a dumb question otherwise why design it as an EAV..... Thing is indexed views and lots of data changes don't sit that well together.

    Traversing a clustered index is rapido. Unless it is loads of levels deep you really surely can't improve it much. I mean the best you can do is avoid reading two or three more pages max wouldn't ya think.
    Last edited by pootle flump; 01-28-08 at 17:34.

  8. #8
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    99% of performance tuning problems are are easy to solve. missing indices and miserably written sql. in that order. i think i might actually have some bad data here. there are way too many results coming back when I take this problematic piece of code out of the larger union which means there are dupes somewhere or maybe a bad relationship. i will deal with it tomorrow. I am glad I did not build this thing.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by pootle flump
    But that's GOOD right? If a cheap step in the process takes up 43% of the whole process then you should pleased as punch. Is there some problem or something? Is the seek really slow in itself? How many rows returned?

    what I was trying to say... would I save myself any execution time by having it traverse a smaller clustered, and I mean smaller in terms of the number of keys. Hence the indexed views.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The only difference in traverse time for a seek is the number of index levels, not the number of key values. Of course these are broadly related but the point is that a B-Tree is really efficient. Each additional level to the B-Tree requires an exponentially larger table - doubling the size of the table doesn't mean double the index depth, or even necessarily an additional index level.

    So far I've only used indexed views in response to queries that are scanning BIG tables where there aren't any appropriate index strategies that could prevent it. These aren't transactional though - DSS so arguably a different senario.

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    yeah that is how i was starting to think about it. i had an itching doubt, like I said i think I got something else going on but i leave work at work.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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