Which is faster, Split or InstrRev?

Lately I've been using Split to parse strings. Here are some examples:

• Google URL Shortener API
• Get your Google PageSpeed score using VBA

I'm curious to see if Split is really the way to go. So I came up with a couple of non-scientific tests to see if parsing a string (in this case, a filepath) using Split and InstrRev to see which is faster. My assumption is that Split will be faster, but we'll find out.

Test Procedure

The following procedure takes a filepath and uses both Split and InstrRev to return just the filename.

Sub TestSplitInstrRev1()
  
  Dim i As Long
  Dim stringToParse As String
  Dim fileName As String
  Dim starttimeSplit As Single
  Dim endtimeSplit As Single
  Dim starttimeInstrRev As Single
  Dim endtimeInstrRev As Single
  Dim msg As String
  
  Const numberOfLoops As Long = 100000

  stringToParse = "C:\Users\Jimmy Pena\Desktop\Documents\inventory.xls"

  ' use Split
  starttimeSplit = Timer
  For i = 1 To numberOfLoops
    fileName = _
   Split(stringToParse, "\")(UBound(Split(stringToParse, "\")))
  Next i
  endtimeSplit = Timer

  ' use InstrRev
  starttimeInstrRev = Timer
  For i = 1 To numberOfLoops
    fileName = Mid$(stringToParse, InStrRev(stringToParse, "\") + 1)
  Next i
  endtimeInstrRev = Timer
  
  msg = "Number of iterations: " & numberOfLoops & vbCrLf
  msg = msg & "Using Split: " & _
  Format(endtimeSplit - starttimeSplit, "#.###") & " seconds" & vbCrLf
  msg = msg & "Using InstrRev: " & _
  Format(endtimeInstrRev - starttimeInstrRev, "#.###") & _
   " seconds" & vbCrLf
  MsgBox msg
End Sub

As you can see, in order to parse the filename we have to call the Split function twice on one line. I'm worried about how this will affect performance.

The InstrRev Function returns only a number, which requires the Mid$ Function to do the actual parsing.

Results

We started with 100,000 iterations, and there's already a significant difference. Relatively speaking, Split is much slower.

At 1,000,000 iterations, I don't even want to proceed further. Split takes over 90% of the overall time.

Conclusion

Well that was unexpected. So maybe Split was just slightly slower than InstrRev. What about another test? After all, it wasn't fair that we had to run Split twice. Let's try another way of using Split that only requires us to use it once.

Modified Test Procedure

The following function is the same as above, except we use the Split function once, then parse the array programmatically. Let's see how this modification affects the running time.

Sub TestSplitInstrRev2()
  
  Dim i As Long
  Dim stringToParse As String
  Dim fileName As String
  Dim pathParts As Variant
  Dim starttimeSplit As Single
  Dim endtimeSplit As Single
  Dim starttimeInstrRev As Single
  Dim endtimeInstrRev As Single
  Dim msg As String
  
  Const numberOfLoops As Long = 1000000

  stringToParse = "C:\Users\Jimmy Pena\Desktop\Documents\inventory.xls"

  ' use Split
  starttimeSplit = Timer
  For i = 1 To numberOfLoops
    pathParts = Split(stringToParse, "\")
    fileName = pathParts(UBound(pathParts))
  Next i
  endtimeSplit = Timer
    
  ' use InstrRev
  starttimeInstrRev = Timer
  For i = 1 To numberOfLoops
    fileName = Mid$(stringToParse, InStrRev(stringToParse, "\") + 1)
  Next i
  endtimeInstrRev = Timer
  
  msg = "Number of iterations: " & numberOfLoops & vbCrLf
  msg = msg & "Using Split: " & _
   Format(endtimeSplit - starttimeSplit, "#.###") & " seconds" & vbCrLf
  msg = msg & "Using InstrRev: " & _
   Format(endtimeInstrRev - starttimeInstrRev, "#.###") & _
    " seconds" & vbCrLf
  MsgBox msg
End Sub

Results

We start with our base 100,000 iterations. Much better! Only one Split call, even with the added array parsing, takes less than half the time of the original. But InstrRev is still the winner.

At 1,000,000 iterations, Split is still the loser but overall much faster than before …

… so much so that we are able to test out 10,000,000 iterations. Unfortunately, even with a slight optimization Split is still slower than InstrRev.

Incidentally, putting only the Split Function into the loop and removing the statement that extracts the filename produces only a superficial speed increase.

Conclusion

It looks like Split didn't stand a chance. However, I will continue to use it on strings that have a delimiter (ex: JSON strings) because the code is shorter and easier to understand. I'm still uncomfortable with string parsing using functions like Instr, Mid and Left like this:

startPosition = Instr(stringToParse, "<") + 1
endPosition = Instr(startPosition, stringToParse, "/>") + 2
tagName = Mid$(stringToParse, startPosition, endPosition - startPosition)

But for parsing things like filepaths, InstrRev is the way to go (assuming I remember, of course).