4346.aspx

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
 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