Performance problem with ADO, stored procedures, BLOBs and SQL Server
There is a know bug in ADO that causes horrible performance with ADO if you insert a BLOB via stored procedure. Working on a (almost) real time import of data I noticed that importing blobs took forever….
I did the right thing and used stored procedures for all data access. In this case it was a mistake as it took several minutes to insert a small blob of a few hundred KB.
I ended up with the workaround below. It is ugly but the least “dirty” solution I found:
set oCon = CreateObject("ADODB.Connection")
oCon.ConnectionString = CONNECTION_STRONG
Set oCmd = CreateObject("ADODB.Command")
set oCmd.ActiveConnection = oCon
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "usp_insert_item"
oCmd.Parameters("@whatever").value = somedataHre
oCmd.Parameters("@PageData").value = "dummy"
'The stored procedure returns the ID of the new record as newID
Set oRS = oCmd.Execute
BinaryID = oRS("newID")
'Update the binary data field
set oRS = CreateObject("ADODB.Recordset")
oRS.CursorLocation = 3
oRS.CursorType = 1
oRS.LockType = 2
set oRS.ActiveConnection = oCon
oRS.Open "select BlobField from TheTable where ID=" & BinaryID
oRS.Fields("BlogField").Value = TheBinaryData
Set oRS = Nothing
Set oCmd = Nothing
set oCon = Nothing