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

Question

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

Champagne Charly

Old Hand

Points: 389
More actions
October 18, 2013 at 8:42 am

#281585

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

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

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

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 1

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)

Champagne Charly Old Hand Points: 389 More actions · Answer 2

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.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

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.

Champagne Charly Old Hand Points: 389 More actions · Answer 4

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.