ODBC bulk insert is slow with MSSQL
Posted on 2009-05-08
I have a C++ program, built with Visual C++ 2005, populating a table on a MS SQL server. It creates a table:
create table main_table (loadorder int not null identity, primary key (loadorder), day_of_week int, hour_of_day int, messages_delivered int, messages_queued int)
and then populates it with a prepared query:
insert into main_table (day_of_week, hour_of_day, messages_delivered, messages_queued) values (?, ?, ?, ?)
using SQLAllocStmt(), SQLPrepare(), SQLSetStmtAttr(), SQLBindParameter(). It loads 10,000 rows at a time. On one SQL Server, it loads it at about 1500 rows per second, which is (barely) acceptable; on another it loads it at about 100 rows per second, which is not acceptable. My question is: what is the difference between the two, and how can I speed up the slow one?
* SQL Server and application are both running on the same x64 Windows system, in both cases (local access to the SQL Server).
* The application is 64-bit.
* The server with fast queries is running Windows XP x64 Professional, Version 2003, SP2; the server with slow queries is running Windows Server 2003 Enterprise x64, SP2.
* The server with fast queries is running Microsoft SQL Server Enterprise Edition (64-bit). The server with slow queries is running SQL Server Express 2005.
* In both cases, I am using a DSN with all defaults, using the SQL Native Client 10 driver.
* Both servers are physical hardware (not virtual).
* The server with faster queries has 6GB RAM; the slower one has 8GB.
* The server with faster queries has 2GHz processors; the slower one has 3GHz.
* Both servers have similar hard drives--internal drives. The server with faster queries has slower drives.
* Both databases have transaction logging set to SIMPLE.
* The server with slower queries is running a German version of Windows; the fast one is English.
* When I run the Activity Monitor within the SQL management console (Enterprise Manager?), it shows 950ms+ out of every second is waiting on Logging, on the server with slow queries; on the server with fast queries, logging is 11ms of every second. I imagine this is the issue, but can't figure out how to change it. I tried moving the transaction log to a different drive, but it does not affect performance. Just for kicks, I tried changing transaction logging to FULL, but it also does not affect performance. Again, logging is already SIMPLE for both databases.
* Both databases are using the same charset / collation (SQL_Latin1_General_CP1_CI_AS)
I'm pretty well out of my depth with this one--I haven't configured or optimized MS SQL much. There must be some setting here that I just don't know to check. I know I could try BCP, but I've got this code working reasonably well on one server, and I'd just like it to work the same on the other. I'm not sure BCP would help anyway, if it's the transaction log that's the problem. It seems like it's a logging issue, but why doesn't affect both servers? Is it reasonable to expect 1000+ rows per second to a SQL Server Express, or is there some sort of built-in limit there? (I also saw 100rps to another Enterprise SQL Server, on a third box). Is there a charset conversion going on here because of the German?