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.
is pronounced ree-bar and is a Modenism for R
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs