October 12, 2009 at 10:13 am
Hi,
i have to pump much data into a big table wich is partitioned and has 3 nonclustered indexes which are partitioned as well.
I'm using SSIS and the fastload option. Because the transactionlog is growing to much if I load all data at once I split data to more than one transactions (commit size). This way I observed that the load will become slower and slower depending how much data the partition already contains. I believe this is, because after every commit the indexes are rebuild?
So, my next try is to disable the three nonclustered indexes first, load data and then rebuild them. Indeed this is much faster! But I would like to rebuild the indexes only for the one partition, as i only changed data of one partition. But this is not possible if the index was disabled.
Can you give me some advice?
Thank you!
Tobe
October 12, 2009 at 2:10 pm
Maybe you could create staging table - with the structure exactly same as the partitioned table you have. Load data into staging table and then switch the table into the partitioned one.
The other option is to use partitioned view instead of partitioned table - they have pretty different characteristics, but can be pretty good alternative to partitioned tables.
Regards
Piotr
...and your only reply is slàinte mhath
October 13, 2009 at 12:10 am
That are good points. I will try. Thank you
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply