slow bulk insert perf on 2.5 gb txt file

a new bulk insert project(project2) is taking far too long to complete. we're loading 2.5 gigs from a tilda delimited txt file. it's taking 2.5 hrs. as a benchmark we have an existing project (project1) with 2.7 gigs that takes 40mins.

we think the slowness must be due to more columns, large decimal data type and/or page size for project2 (the slow one). trying to test this.

does anyone have any clues on this slowness? or any ideas on the three problems in the previous paragraph?

here is a checklist of the stats ...

Project1Project2
1. serversamesame
2. bulk insert from drivesame (D drive)same (D drive)
3. bulk insert into drivesame (G drive)same (G drive)
4. data file size2.7 gb2.5 gb
5. stored proc to run BIsamesame
6. index creation with BInono
7. BI parameterssamesame
8. primary key created bybulk insertbulk insert
9. loggingoffoff
10. constraint checkingoffoff
11. data file pre-orderedyesyes
12. tempdb growthnormalhigh (upto 10gb)
13. columns1634
14. predominant data typessmall intdecimal (19,2)
15. LOAD TIME40 MINS2.5 HRS

thanks

oh there's actually another problem. the tempdb is increase in size until the dba clears it. any ideas why?

There are 2 perfmon counters

Bulk Copy Rows/sec
Bulk Copy Throughput/sec

They are located in the Databases object.

What do they report for the two different executions?

What do you mean by #8 "primary key created by bulk insert bulk insert"

Based on what you described, and as you are suggesting, I would not expect to see this much difference between the two bulk loads, assuming that virtually everything else is identical. Obviously, there must be some difference, and I don't think the different data types or differences in the number of colums can explain most of the time difference. The goal now is to find some difference between the two BI, as there must be something.

As rortloff has suggested, you need some more data to help you find out what is going on. I would try Performance Monitor first, using the counter already suggested, plus the standard counters used to look for hardware bottlenecks, and see if there are any obvious differences. If that doesn't work, then I would do a Profiler trace of both BI to check for differences. One or the other of these will give you a good clue to what is happening.

Also, the tempdb should always clear itself when the connection that created the tempdb tables is disconnected. Be sure to check that the connection used is properly closed.

Let us know what you find out.


------------------
Brad M. McGehee
Webmaster
SQL-Server-Performance.Com