|
|
|
Forum Newbie
Group: Forum Members
Last Login: 10/28/2009 7:32:16 AM
Posts: 1,
Visits: 6
|
|
Hi all,
I've got a large table with some NUSIs und run regularly (mass) delete/inserts on this table. In order to accelerate the inserts, my idea is to drop the NUSI's before the insert statement and rebuild them afterwards.
Do you have any information on performance behavior ? How do you think, this might affect the performance and is this really significant?
In my case the delete/insert operations affect about 20-30% of the tables overall row count.
I've already searched the documentation and this forum for information, but couldn't find a lot. Any opinions/facts on this idea in general or in my specific case are much appreciated!
Thank you very much for you help in advance.
Regards,
Andreas
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 2 days ago @ 7:45:03 AM
Posts: 39,
Visits: 169
|
|
|
how many times delete/insert happens on this table daily ? many times or only once in a day ?
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: Yesterday @ 3:55:54 AM
Posts: 111,
Visits: 263
|
|
Secondary indices will definitely slow down performance on high volume delete/ insert.
If you are regularly affecting 20-30% of the table, I would look at building a new table - Insert the rows you want to keep, and any new rows into an identical structure (without SI's), build the indices/ stats and then switch the tablenames over. Make sure that all inserts to the new table are in a Multi-Statement Request to ensure a fastpath insert is used.
It means the old data is available until everything is ready. The switch could take some time if the old table is in use, but it is the quickest way (by far) to rebuild a table with so many changes.
|
|
|
|