Tuesday, April 1, 2008

Clustered Index slows down BCP signiificantly

Our 19 hour job was reduced to about 30 minutes using this technique.

We had to import 110 million rows into a SQL Server table containing 5 ints during 4 import sessions. There was a clustered index over 4 of those ints.

We used the System.Data.SqlClient.SqlBulkCopy class to do the import (class mirroring the sql server bcp utility). The initial import took ~19 hours. On a new table we removed the clustered index and inserted all the rows again. This took about 12 minutes. Applying the clustered index took about 20 minutes.


Anonymous said...

Sweet blog, I had not noticed blog.davidsilvasmith.com before during my searches!
Continue the fantastic work!

Anonymous said...

Hello there,

I have a inquiry for the webmaster/admin here at blog.davidsilvasmith.com.

May I use some of the information from this blog post right above if I give a link back to your site?