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
oCon.open
Set oCmd = CreateObject("ADODB.Command")
set oCmd.ActiveConnection = oCon
oCmd.CommandType = adCmdStoredProc
oCmd.CommandText = "usp_insert_item"
oCmd.Parameters.Refresh()
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")
oRS.close()
'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
oRS.Update()
oRS.Close()
Set oRS = Nothing
Set oCmd = Nothing
set oCon = Nothing
Feel free to drop a few cents in the
tip jar if this post saved you time and money