Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a database where I load files into a staging table, from this staging table i have 1-2 joins to resolve some foreign keys and then insert this rows into the final table (which has one partition per month). I have around 3.4 billion rows for three months of data.

What is the fastest way to get these rows from staging into the final table ? SSIS Data Flow Task (that uses a view as source and has fast load active) or an Insert INTO SELECT .... command ? I tried the Data Flow Task and can get around 1 billion rows in around 5 hours (8 cores / 192 GB RAM on the server) which feels very slow to me.

share|improve this question
1  
Are the partitions on separate filegroups (and are on those filegroups on different physical disks)? –  Aaron Bertrand Feb 19 at 14:44
3  
A really good resource The Data Loading Performance Guide. This addresses a lot of performance optimization you can do e.g Enabling TF610, Using BCP OUT/IN, SSIS etc. You just have to follow the recommendations and test it out in your environment. –  Kin Feb 19 at 15:17
    
@Aaron yes, per month one filegroup, 12 san lun's attached so all jan go on one lun etc. Not sure how many disks per lun but should be plenty. –  nojetlag Feb 19 at 16:30
    
Yeah I really meant "sets of disks" and probably could have mentioned controllers too, which can get saturated. –  Aaron Bertrand Feb 19 at 16:32
    
@Kin had a look at the guide but it seems outdated, "The SQL Server destination is the fastest way to bulk load data from an Integration Services data flow to SQL Server. This destination supports all the bulk load options of SQL Server – except ROWS_PER_BATCH." and in SSIS 2012 they recommend the OLE DB destination for better performance. –  nojetlag Feb 19 at 16:34

2 Answers 2

up vote 13 down vote accepted

One common approach:

  1. Disable / drop indexes / constraints on target table.
  2. INSERT dbo.[Target] WITH (TABLOCKX) SELECT ...
  3. With credit to JNK of course, you can do the above in batches of n rows, which can reduce the strain on the transaction log, and of course means that if some batch fails, you only have to-start from that batch. I blogged about this (while in reference to deletes, the same basic concepts apply) here: http://www.sqlperformance.com/2013/03/io-subsystem/chunk-deletes
  4. Re-enable / re-create indexes / constraints on target table (and perhaps you can defer some of those, if they are not necessary for all operations, and it is more important to get the base data online quickly).

If your partitions are physical and not just logical, you may gain some time by having different processes populate different partitions simultaneously (of course this means you can't use TABLOCK/TABLOCKX). This assumes that the source is also suitable for multiple processes selecting without overlapping / locking etc., and making that side of the operation even slower (hint: create a clustered index on the source that suits the partitioning scheme on the destination).

You may also consider something a lot more primitive, like BCP OUT / BCP IN.

I don't know that I would jump to SSIS to help with this. There are probably some efficiencies there, but I don't know that the effort justifies the savings.

share|improve this answer
2  
Good answer, and I will just add that batching sometimes will speed this up as well. You can put the INSERT into a WHILE loop to insert only 10m rows at a time, which has the added benefit of being restartable if something fails. –  JNK Feb 19 at 15:13
    
@JNK thanks, added that to my answer –  Aaron Bertrand Feb 19 at 15:16

Looking at your problem from an SSIS perspective I feel the reason this may have taken so long is that you didn't have batching on. This can lead to too many rows filling the SSIS pipeline and can hinder your SSIS performance as a result. What you need to do is alter your rows per batch setting and possibly your maximum insert commit size. Now what you set this too will depend on the amount of memory available to your SSIS server? What the disk speed of your SQL Server instance is? The best way to do this is test. Lets for example use 10,000. This will send a batch to the server 10,000k at time thus keeping your pipeline from overfilling and will help run this process faster. These settings are set in your OLEDB destination.

OLEDB Destination

If it is an issue you can also add an execute SQL task before and after to do as @AaronBertrand suggests and remove/re add any indexes or constraints to the table.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.