Tuesday, August 28, 2007


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

No comments:

Post a Comment