View Incident Report

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

DELETE FROM "MandN.Dat"
WHERE _BoxNo = 76

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

UPDATE "MandN.Dat"
SET
_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 and Workarounds

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

Resolution

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

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap