SQL Server Quickest way to Insert Multiple Rows with VB

Having to parse huge amounts of data from xml files to an SQL Server database, I needed to greatly optimise my code. Here are some tests to insert 10000 rows into table TABLETESTER.

First, using the standard INSERT INTO without specifying column names. TickCount of 70547.

For i = 1 To 10000
	Connection.Execute "INSERT INTO TABLETESTER VALUES (1, 'abcdefghijklmnopqrstuvwxyz')"
Next i

Specifying the column names is actually marginally faster. TickCount of 66782.

For i = 1 To 100000
    Connection.Execute "INSERT INTO TABLETESTER (NUMBERVAL, STRINGVAL) VALUES (1, 'abcdefghijklmnopqrstuvwxyz')"
Next i

Removing the optional INTO is a little faster still. TickCount 64843.

For i = 1 To 100000
	Connection.Execute "INSERT TABLETESTER (NUMBERVAL, STRINGVAL) VALUES (1, 'abcdefghijklmnopqrstuvwxyz')"
Next i

Now lets try some of the methods to combine multiple inserts.

Bunching multiple statements in a single Execute increases the speed by 2. TickCount 35391.

For i = 1 To 100000 / 100
	s = vbNullString
	For j = 1 To 100
		s = s & "INSERT TABLETESTER (NUMBERVAL, STRINGVAL) VALUES (1, 'abcdefghijklmnopqrstuvwxyz');"
	Next j
	Connection.Execute = s
Next i

Using UNION ALL increases it by a whopping 10 fold. TickCount 2781.

For i = 1 To 100000 / 100
	s = "INSERT INTO TABLETESTER (NUMBERVAL, STRINGVAL) SELECT  1, 'abcdefghijklmnopqrstuvwxyz'"
	For j = 2 To 100
		s = s & " UNION ALL SELECT  1, 'abcdefghijklmnopqrstuvwxyz'"
	Next j
	Connection.Execute = s
Next i

Increasing the inner loop count. TickCount 3125.

For i = 1 To 100000 / 1000
	s = "INSERT INTO TABLETESTER (NUMBERVAL, STRINGVAL) SELECT  1, 'abcdefghijklmnopqrstuvwxyz'"
	For j = 2 To 1000
		s = s & " UNION ALL SELECT  1, 'abcdefghijklmnopqrstuvwxyz'"
	Next j
	Connection.Execute = s
Next i

Decreasing the inner loop count. TickCount 8235.

For i = 1 To 100000 / 10
	s = "INSERT INTO TABLETESTER (NUMBERVAL, STRINGVAL) SELECT  1, 'abcdefghijklmnopqrstuvwxyz'"
	For j = 2 To 10
		s = s & " UNION ALL SELECT  1, 'abcdefghijklmnopqrstuvwxyz'"
	Next j
	Connection.Execute = s
Next i

SQL Server 2008 has a new method of combining multiple inserts. TickCount 3282.

For i = 1 To 100000 / 100
	s = "INSERT TABLETESTER (NUMBERVAL, STRINGVAL) VALUES (1, 'abcdefghijklmnopqrstuvwxyz')"
	For j = 2 To 100
		s = s & ", (1, 'abcdefghijklmnopqrstuvwxyz')"
	Next j
	Connection.Execute = s
Next i

Increasing the inner loop count, note 1000 is the maximum allowed. TickCount 2859.

For i = 1 To 100000 / 1000
	s = "INSERT TABLETESTER (NUMBERVAL, STRINGVAL) VALUES (1, 'abcdefghijklmnopqrstuvwxyz')"
	For j = 2 To 1000
		s = s & ", (1, 'abcdefghijklmnopqrstuvwxyz')"
	Next j
	Connection.Execute = s
Next i

Finally, not a SQL syntax change, but compacting the strings. TickCount 2453.

For i = 1 To 100000 / 1000
	s = "INSERT TABLETESTER (NUMBERVAL,STRINGVAL) VALUES (1,'abcdefghijklmnopqrstuvwxyz')"
	For j = 2 To 1000
		s = s & ",(1,'abcdefghijklmnopqrstuvwxyz')"
	Next j
	Connection.Execute = s
Next i

So from our original method at 66782 to our optimised method at 2453, we get a 97% speed saving!