Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Exceptionally slow row addition or modification on table associated to indexed view. Expand / Collapse
Author
Message
Posted Friday, October 18, 2013 8:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 20, 2014 4:47 PM
Points: 11, Visits: 122
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
Post #1506200
Posted Friday, October 18, 2013 2:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 11:28 PM
Points: 35,263, Visits: 31,750
Champagne Charly (10/18/2013)
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


If your index view is truly based on a CROSS JOIN, 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1506328
Posted Saturday, October 19, 2013 6:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 20, 2014 4:47 PM
Points: 11, Visits: 122
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.
Post #1506391
Posted Saturday, October 19, 2013 8:06 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:03 AM
Points: 20,703, Visits: 32,344
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.


Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1506401
Posted Saturday, October 19, 2013 8:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Sunday, July 20, 2014 4:47 PM
Points: 11, Visits: 122
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.
Post #1506403
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse