Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kaprice
Starting Member
10 Posts |
 Posted - 2001-09-13 : 12:27:14
|
| I have a single table SQL Server DB. I'm running a text fileimport routine from MS Access. (I'll post the code as a replyto this.) I'm looping through each line of the text file and using an INSERT statement as opposed to doing a bulk import because I need to handle rejections on a line by line basis (ie write the line to another file, along with the reason for the rejection -- Many are rejected because of dupes (PK violations)).The text file has almost 4 million records. I have errorhandling that checks for Primary Key violations and other errors.On other errors, I write the line to another text file.The import started fairly quickly (appx 100 lines every 3 seconds). But, now at 2 million records, it is going very slow (appx 100 lines every 5 minutes).Modified code that allows me to watch it run shows that it pauseson every 6th line for about 15 to 30 seconds. Then, 5 more linesinstantly, then another pause. Every time, it's the 6threcord.Win2K Server shows CPU hovers between 2 and 10%.Any idea what's causing the pause? How can I speed it up. I'vegot 36 such files that need to be imported, and at this rate, I figure it will be done in the year 3001 !!.-Keith-Keith |
|
|
|
|
|
INSERT slows way down after 2 million recorrds