First of all, where the idea to do the speed comparison came from?
Some time ago I created a tool, which is downloading the files from URL’s. And there I used WinHttp method. Recently I was told, that it is working so slow, that it took days to download all needed files.
The download speed was really slow. At first I thought, that is all about the files size, but decided to read something about it.
I found the post, where is said, that XMLHttp method is much faster, so I checked that immediately.
First of all, You need to set the reference for early binding to XML, in my case this is version 6, and to WinHTTP Services, version 5.1.
To do the speed comparison I used code from my old article about downloading from URL. For the first attempt I used MSXML2.XMLHTTP60 and for the second WinHttp.WinHttpRequest. Also, I prepared 10 hyperlinks with files around 1Mb each.
Option Explicit Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" _ Alias "URLDownloadToFileA" (ByVal pCaller As Long, _ ByVal szURL As String, ByVal szFileName As String, _ ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long Sub download_picture() Dim wk As Workbook Dim ws1 As Worksheet Dim counter As Long Dim xmlhttp As New MSXML2.XMLHTTP60 Dim xmlhttp As New WinHttp.WinHttpRequest Dim myURL As String, typ As String, name0 As String, name2 As String Dim name1 As String, xstate As String, dlpath As String Dim pos As Integer Dim xstatus As Long, i As Long, lastRow As Long Dim time_ctn As Double time_ctn = Timer Application.ScreenUpdating = False Set wk = ThisWorkbook Set ws1 = wk.Sheets(1) With ws1 dlpath = .Range("E2").Value counter = 0 lastRow = .Cells(Rows.Count, 2).End(xlUp).Row For i = 2 To lastRow myURL = .Range("B" & i).Value xmlhttp.Open "GET", myURL, False xmlhttp.send name0 = xmlhttp.getResponseHeader("Content-Disposition") If name0 <> "" Then pos = InStr(1, name0, "=") name2 = Replace(Mid(name0, pos + 1, (Len(name0) - _ (pos))), """", "") Else name2 = FileNameFromPath(myURL) End If xstatus = URLDownloadToFile(0, myURL, dlpath & "\" & _ name2, 0, 0) Debug.Print xstatus Next i End With Debug.Print Format(Timer - time_ctn, "0.00") Application.ScreenUpdating = True End Sub Function FileNameFromPath(strFullPath As String) As String FileNameFromPath = Right(strFullPath, Len(strFullPath) _ - InStrRev(strFullPath, "/")) End Function
I read about the difference, but did not believe it that way.
XML method went almost 40 times faster!
It was immediate versus 6 seconds of waiting. It was amazing discovery.
So why would I use WinHttp at all?
It may seem that XML is much better than Win method.
It is true, but not always…
Disadvantages of XMLHttp method
1.Sensitive for re-directions
You may think it is weird point – why would I want to put the link for my code, which is not direct link? I may agree with that, but if You receive that from someone else, different things can happen. XMLHttp will not download anything at best case scenario, at bad it will cause an error.
2.Http with/WITHOUT secure
I know that it is almost the same as point above, but wanted to mention it separately.
In most cases I saw the same link structure. There was no redirection, but I received package of links, which in the end appears to have no s in the end of http. Web browser is automatically hiding this beginning of link, so You can’t really see the difference, but XML method does.
It fails on it.
3.No server status
Following above points You can’t get the server status or its answer for your request, like in WinHttp. This was really helpful in one of my cases.
Now You know who won the XMLHttp vs WinHttp speed comparison. Remember, that the fastest doesn’t always mean the best. Everything depends on the situation. Keep that in mid. Use it wisely.