SQL Server TSQL Bulk Insert very slow

I was trying to use the Microsoft SQL Server 2008 BULK INSERT to load about 2.2 million records in a table and it was taking FOREVER . I’m used to bulk inserts flying.

I looked into the details and found out that the table had 15 indexes, so I dropped all of those indexes except the single clustered primary key. It still took FOREVER (2 hours) and it looked like the longer it ran the SLOWER it got.

Then I thought for a minute about what it was doing, trying to cluster that data. Every batch of 10,000 records had to be smushed into where other records used to be. So I sorted the input in primary key order and now it takes 5 minutes.

Live and learn

About Kevin

Just an old guy with opinions that I like to bounce off other people.
This entry was posted in Technology and tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *