Exceptionally slow row addition or modification on table associated to indexed view.

  • I have an index view (9M rows) based upon 2 cross joined tables.

    The storage of the index is in filegroup and is not partitioned.

    No pages aprox = 205k 60% full.

    Creating the index took aprox 3mins.

    Entering of data into the smaller table. takes around 3mins (just to change one date)

    It seems clear that the reason for this grinding to a halt is that the index is being recreated as the data is being entered.

    Is this the downside of indexed views or is there a way around this mass slowdown caused no doubt by the index recreation/ modification process from the transaction? Would partitioning help and if so to what degree? I have as yet not attempted and form of partitioning.

    Any help would be appreciated. Thanks

  • Champagne Charly (10/18/2013)


    I have an index view (9M rows) based upon[font="Arial Black"] 2 cross joined tables[/font].

    The storage of the index is in filegroup and is not partitioned.

    No pages aprox = 205k 60% full.

    Creating the index took aprox 3mins.

    Entering of data into the smaller table. takes around 3mins (just to change one date)

    It seems clear that the reason for this grinding to a halt is that the index is being recreated as the data is being entered.

    Is this the downside of indexed views or is there a way around this mass slowdown caused no doubt by the index recreation/ modification process from the transaction? Would partitioning help and if so to what degree? I have as yet not attempted and form of partitioning.

    Any help would be appreciated. Thanks

    If your index view is truly based on a [font="Arial Black"]CROSS JOIN[/font], you're pretty much toast (and I've never heard of doing such a thing). Please verify that the view is actually based on a CROSS JOIN.

    Regardless, it's actually a rare thing that partitioning actually helps with performance. The normal purpose of partitioning is to ease up on certain maintenance tasks and to make recovering a database in pieces possible.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Thanks.. Seem like I'm toast then.

    So if its flawed by design /cross join, where did i go wrong?

    Basically what I want to achieve is a weekly snapshot of products and the associated storage costs Created from

    how much was received (t1),

    What went out (t2)

    Storage rates for pack size(t3)

    (cross join to) YEarWeek Calender T4

    This creates a row/week/product.

    I couldn't think of any way other than cross join to achieve this. Suggestions would be appreciated.

  • The problem with giving you any suggestions at this point is we don't know enough to actually provide you with any viable alternatives. We can't see from here what you see there. We don't know your application, database structure, or data.

  • Understood.

    I think I will just drop the index. So far the overhead of querying the view is not so great and data is returned within 400ms compared to 30ms with an index. I just like the performance effect of index and so use where possible. However in this case it doesn't pay off.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply