Hi there.

I have a a query that I use to delete certain records from a database when the program is
closed down, which is now suddenly giving an 'Out of Memory' error after a number of
trouble free years use. It is running in local mode on a stand-alone computer with 256MB
of memory and plenty of hard disk space. The table is nothing special, record size is 144
bytes, although it does have a blob field (Blob Size 512) that stores an Async Pro fax
image. It uses the following SQL statement ...

DELETE FROM mnfax
WHERE (status = 'S') and ((type = 'MAIL') or (type = 'ENQY'))

There are currently around 104,000 records on the table and the query would delete about
800 records.

Does anyone know if the amount of memory required by a SQL DELETE statement increases as
the number of records deleted increases? Do I need to write a delete loop in Delphi to
replace the query or is there some way to get the query to work in this limited amount of
memory?

Many thanks,
Ian Turner

DBISAM 4.25 build 3
Delphi v7
Windows XP

Ian


I've certainly used SQL to delete more records than that in one go. My first guess would be its something weird in one of the blob fields. My approach for many years, since I used full text indexing a lot and it made it faster, was to have a small script the first part of which set the blob fields to ''. Might be worth a try.

Roy Lambert [Team Elevate]

Ian,

<< I have a a query that I use to delete certain records from a database
when the program is closed down, which is now suddenly giving an 'Out of
Memory' error after a number of trouble free years use. It is running in
local mode on a stand-alone computer with 256MB of memory and plenty of hard
disk space. The table is nothing special, record size is 144 bytes, although
it does have a blob field (Blob Size 512) that stores an Async Pro fax
image. It uses the following SQL statement ...

DELETE FROM mnfax
WHERE (status = 'S') and ((type = 'MAIL') or (type = 'ENQY'))

There are currently around 104,000 records on the table and the query would
delete about 800 records. >>

I would check those 800 records and make sure that there isn't a huge BLOB
in there that you're not aware of.  After that, I would go ahead and repair
the table to make sure that it isn't a situation where the BLOB blocks are
corrupted.

Also, for fax images I would suggest a higher BLOB block size than 512
bytes.  I've posted a little application in the Binaries newsgroup that
gives a recommendation for an ideal block size for a givent table.  It was
originally written by me, but Chuck Todd was kind enough to make it really
cool with a histogram.

--
Tim Young
Elevate Software
www.elevatesoft.com

Thank you Roy and Tim for your kind help.

I have now visited the customer's site and the problem was purely a lack of system memory.
Using   Commit Interval 30 in the SQL Delete statement solved the problem. The reason why
it flared up at this point was totally my fault (as is usually the case). I had recently
allowed another program to use the fax database to send mailshot faxes and whilst the
original design only had fax sizes of 20-30K, the mailshot was generating fax data in the
region of 500-800K!! Old age creeping up on me I think.

I should add a further thanks to Tim and Chuck. The BlobSize app was really useful in
looking at the spread of sizes.

Many thanks,
Ian