Icon View Incident Report

Serious Serious
Reported By: Roy Lambert
Reported On: 8/10/2003
For: Version 3.26 Build 1
# 1449 Setting BLOB Columns to NULL Values in SQL UPDATE Statements Does Not Delete Blob Data

In the process of trying to speed up the deletion of some records that contain BLOB fields, I can reduce the time taken
from 490+ seconds to c2 seconds. All I have to do is set all of the memo fields in the table to null before deleting.

MandN - 90226 records, 869 for _BoxNo = 76
Delphi 6 SP 2, DBISAM 3.24, 1GHz PC with 256MB Ram

DELETE FROM "MandN.Dat" X, "BandA.Dat" Y
WHERE _BoxNo = "Y"._BoxNo
AND ("Y"._BelongsTo = 76
OR "Y"._BoxNo = 76)

497.762 secs - no progress indicator - 869 rows deleted

WHERE _BoxNo = 76

Wizzes along to 77% then goes slow
492.428 secs 869 rows deleted

UPDATE "MandN.Dat"
_Headers = NULL,
_Message = NULL,
_Attachments = NULL,
_InLine = NULL,
_Comments = NULL,
_Specials = NULL
WHERE _BoxNo = 76;

DELETE FROM "MandN.Dat" WHERE _BoxNo = 76;

1.923 secs

Comments Comments and Workarounds
The SQL UPDATE statement was not properly deleting the BLOB data when a BLOB field was set to NULL.

Resolution Resolution
Fixed Problem on 10/20/2003 in version 3.27 build 1