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!

Visual Basic 6 – quickest way to find first/last character in string

When you are parsing large amounts of data, the way you code string matching can make a huge difference. In one case I needed to find if a string was contained within quotes, here are the test results from quickest to slowest.

The test situation was to find if the last character in the string ‘abcdefghijklmnopqrstuvwxyz’ is ‘z’ and iterated 100000000 times.

First, the most intuitive which most would use. TickCount of 49546.

Right("abcdefghijklmnopqrstuvwxyz", 1) = "z"

Function Right takes in a Variant by default, by succeeding it with a dollar sign it accepts Strings by default. TickCount of 22828, a significant saving.

Right$("abcdefghijklmnopqrstuvwxyz", 1) = "z"

Using the equals sign would be seem the norm, but what if the String Compare function was used. TickCount of 18047.

StrComp(Right$("abcdefghijklmnopqrstuvwxyz", 1), "z", vbBinaryCompare) = 0

What if Mid was used to extract the last character instead of Right. TickCount of 28391.

StrComp(Mid$("abcdefghijklmnopqrstuvwxyz", Len("abcdefghijklmnopqrstuvwxyz"), 1), "z") = 0

Now what if we use the In String function. String length binary is faster than standard String length. TickCount of 14516.

InStrRev("abcdefghijklmnopqrstuvwxyz", "z", -1, vbBinaryCompare) = LenB("abcdefghijklmnopqrstuvwxyz") / 2

Finally, with out String length binary. TickCount of 11312.

InStrRev("abcdefghijklmnopqrstuvwxyz", "z", -1, vbBinaryCompare) = Len("abcdefghijklmnopqrstuvwxyz")

So using In String Reverse is 77% faster.

UPDATE next day: Three that I totally forgot about

In String starting at last character, TickCount 11266.

InStr(Len("abcdefghijklmnopqrstuvwxyz"), "abcdefghijklmnopqrstuvwxyz", "z", vbBinaryCompare) = Len("abcdefghijklmnopqrstuvwxyz")

And In String Binary which is naturally fast. TickCount 6672.

InStrB(LenB("abcdefghijklmnopqrstuvwxyz") - 1, "abcdefghijklmnopqrstuvwxyz", "z", vbBinaryCompare) = LenB("abcdefghijklmnopqrstuvwxyz") - 1

So using In String Binary is 70% faster.