How can I speed up multi-row inserts to SQL server?

Question

I am inserting about 400 rows into a table at a time (SQL Server 2008). I have tried UNION ALL, but that slowed things down considerably. I settled on the following query:

INSERT INTO table VALUES (a, b,...),(c, d,...),...(x,y,...); 

When I make this query through ODBC it takes roughly 400ms (I use C++ and time the single call to sqlExecDirect going to odbc32.dll). However, when I run the query inside MS SQL Server Management Studio wrapped by Getdate():

SELECT Getdate()
INSERT INTO ...
SELECT Getdate()

it takes ~10ms. For comparison, on the same machine, the same query in MySQL takes roughly 20ms.

1. Is SELECT Getdata() a reliable way to time a transaction?
2. Why is MS SQL Server Management Studio so much faster?
3. What can I do to improve the performance? Any alternatives to ODBC? Direct connect to SQL server?

Answer 1

The first connection usually takes a while to set up. This can explain an initial delay: in SSMS, the connection will already be in place when you start the query. Try to do the insert twice from the same C++ program and see if that makes a difference.

Also: what ODBC driver are you using? For ODBC, consider the native driver:

Driver={SQL Server Native Client 10.0};Server=myServerAddress;
    Database=myDataBase;Uid=myUsername;Pwd=myPassword;

Instead of ODBC, You could also use ADO through COM. That's quite easy to do with a generated wrapper. Then you can connect using OLE DB:

Provider=SQLNCLI10;Server=myServerAddress;
    Database=myDataBase;Uid=myUsername; Pwd=myPassword;

Answer 2

the way you are benchmarking, you are not capturing compile time. Do this:

SET STATISTICS TIME ON
GO
INSERT INTO ...