SQL Server Bulk Insert operation is very slow

Leave a comment (0) Go to comments


One of my colleague doing a consulting for migrating old legacy data to a new SQL Server table. He was trying to import approximately 5 million rows and this is taking forever. He called me up to check, what could be wrong,Here are the Finding which converted this bacth from a never ending batch to a few minute batch

He was using bulk insert on the fly, whichout any plan.  The table had 13 indexes, so I dropped all of those indexes except the single clustered primary key index. It still took FOREVER (4-5 hours) and it looked like the process is getting slow and slow as longer it ran

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

He was amazed to see this sort of optimisation. This is what a optimisation is, where hours of processing is converted into minutes by using some small tricks. 

EOF - SQL Server Bulk Insert operation is very slow, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000

Leave a Reply

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


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.