| Author |
Topic |
|
Daze
Starting Member
42 Posts |
 Posted - 2008-08-21 : 11:47:03
|
| Help!A colleague of mine has written a CSV to SQL importer application, in C# 3.5. It's a very simple affair. Basically, it reads a line from a CSV file and issues and INSERT statement to SQL Server 2005.On our local (test/development) machines, it takes between 11 and 25 minutes to import 1.2 million records into a table. The table has no primary keys or indexes. We were more than happy with this performance.However, we've pushed the application to our live server and it's taking 3.25 hours to import the same 1.2 million records! The databases are exactly the same on both machines, and the server (Windows 2003) is more than up to the job (4GB RAM, Intel Pentium D 3.0Ghz). CPU usage is negligable, but the hard disk appears to be working hard (from what I can tell).I've tried profiling various counters, and the average hard disk seek time is good. The average INSERT statements per second is 100 on the server, and 2000+ per second on our local machines. We're baffled.I changed the code so that it issued bulk INSERT statements (100 or more at a time), but it made no difference on the live server.Can anyone suggest what could be causing the huge difference in execution time? What is likely to be the bottleneck?Thanks in advance for any help.Daze.Sorry if this is not the correct forum for this post. I wasn't sure where best to place it. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2008-08-21 : 22:57:38
|
| Is the file on sql server's local disk? Does table have many indexes? Do you put bulk insert in sp? Did you try rows_per_batch option? Tried with different batch size like 1000 or 10000? |
 |
|
|
Daze
Starting Member
42 Posts |
Posted - 2008-08-22 : 05:12:47
|
| Hi,The file is on SQL Server's local disk. The same is true of our local machines. The server itself is under almost no load at all times.The table has no primary keys or indexes.Each insert statement is issued via ADO.Net's command object, and not a stored procedure.I have tried sending batches of 1000 and 10000 INSERT statements (10000 was too much, as the whole process seemingly hung).Oh, and the Recovery Model setting is set to simple.ThanksDaze. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-08-22 : 05:32:05
|
| I wrote something very similar a while ago in java and run against lots of different performance issues. My first try performed an INSERT for each line in the file. That sucked.My latest version does 1 INSERT then a set number of UNION SELECTS (I've found 50 to be a good number) then the 2nd insert followed by another 50 UNION SELECTs. This gave me *much*, *much* better performance.However, I've got no clue why it takes longer on your production server unless you use simple backup model in test but a complete log history on live? That would introduce a *lot* of extra logging for a few million insert statements.-------------Charlie |
|
|
|
Daze
Starting Member
42 Posts |
Posted - 2008-08-22 : 07:39:34
|
| Oddly, the UNION SELECT approach actually slowed things down for me (on my local machine and the server). It's way slower than simply issuing individual INSERT statements. Weird.This really has me puzzled...Daze. |
|
|
|
Daze
Starting Member
42 Posts |
Posted - 2008-08-22 : 09:01:23
|
| INSERT statements just appear to be really slow to execute on the server...Daze. |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2008-08-22 : 10:07:06
|
| What happens if you try and bcp / bulk insert the file direct rather than trying to convert it into an insert script?-------------Charlie |
|
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2008-08-22 : 11:50:55
|
this is exactly what bcp.exe is for.
elsasoft.org |
|
|
|
Daze
Starting Member
42 Posts |
Posted - 2008-08-22 : 11:54:26
|
| Got it!"Enable Advanced Performance" (under the disk drive's "Policies" tab) was disabled. When enabled, performance was comparable. It *really* makes a diffence.Thanks for the help anyway. I appreciate it.Edit: Will take a look at BCP anyway - Thanks.Daze. |
|
|
|
|
Import Taking 3.25 Hours On Server - Bottleneck?