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.
at

2 comments:

  1. AnonymousSeptember 11, 2010 at 9:42 PM

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

    ReplyDelete
  2. AnonymousOctober 14, 2010 at 6:50 PM

    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?

    Thanks,
    Oliver

    ReplyDelete