Many thanks for the response. I left the simple delete running overnight,
and it is only at 60% at the moment. Block size was something I considered
- I'm certainly using the default. I'll have to look up how to change the
size. One issue is that some of the data is as little as 128 bytes, but
I'm going to find out how big this stuff is really when I get into the
office (I'm remoting right now).

I'll also switch to a record at a time so that the hit has less impact.
One thing I really like about DBISAM is that there are multiple ways to do
things so I can at least overcome this issue.

/Matthew Jones/

> and it is only at 60% at the moment

Okay, bringing dbsys to the front, I find it had actually run out of
memory and stopped.

/Matthew Jones/

In article <memo.20061102085536.3700K@nothanks.nothanks.co.uk>,
mattjones@cix.co.uk (Matthew Jones) wrote:

> ...problems deleting records with large amounts of blob data...

I recall having a similar problem a while ago. I got around it by doing
it in two stages. First use an update query to set the respective blob
fields to '' (or null) then run the delete query as normal.
Made a *huge* difference to the processing speed. Can't remember which
version of DBISAM it was though.

Steve

Matthew


Tim beat me to the response about blobs. One thing I did (earlier version of DBISAM) was to set the blob fields to null with an update statement first then run the delete. Roughly doubled the speed. Tim fixed the problem but it might be worth a try.

Roy Lambert

Okay, I had 512 byte blob blocks, and I changed it to 10240 bytes as most
of my data was simply massive. Although the theoretical minimum size is
128 bytes, such a data size was uncommon. Although a complete delete kept
running out of memory still, the SQL to delete a day at a time worked. But
I've switched to using a query to get those that need deleting, and then a
table loop to do the actual delete, and that has allowed the concurrency
that I need too. The startup code now checks the table has the big block
size, so the existing sites can auto-upgrade themselves.

Thanks for the detailed info and support.

/Matthew Jones/

> What you might want to consider doing is simply using the
> TDBISAMEngine.LargeFileSupport=True option so that the size limit will
> be higher than 4 Gb.  Although, that won't solve the deletion issue.

I did consider that when it was at 2Gb, but I've decided to go with a 3-
day purge instead of 5 days. The data is actually typically dead after 3
minutes, so this is just a matter of helping me grab "post-mortem" info
really. The automatic purge should keep the whole thing running sweetly
now.

/Matthew Jones/

Hmm, thanks both for the NULL the blobs idea. I didn't think of that. The
solution I now have (see my other post) resolves the whole thing. Hours of
locked database now at tens of seconds with no lock-out.

/Matthew Jones/

As a small additional note, I have an application which stores an organisation's emails,
initially with attachments stored inside the database as BLOBs ... these are often large.

In the end I have moved all the attachments out of BLOB fields & save them to disk as
files, the application then loads them via reference by FileName from a CHAR field in the
database.

It is a lot quicker & honestly I think that is how it ought to be! Databases are
fundamentally about metadata (ID's, linkfields etc) not about huge BLOBs of files ... so I
am very happy to work it that way.

In another application where the user wanted attachment-type files "hidden away" I used
the (now open source) Abbrevia code to create a custom ZIP file format & shoved all the
files in that. It worked really well (though there were only a few 100 meg of files) & was
very quick to save & access, not much slower than standard file-access. I could save a
FileName in the database & reference the file using this (from inside the ZIP). The
Abbrevia code was insanely easy to work with ... I seem to remember the whole thing took
me less than half a day.

If you were to create date-based ZIPs (say 1 per day) you could follow a similar design
... and delete older ZIPs (for specific days), simultaneously SQL DELETEing older rows
when you wanted to "Flush out" older materials.

Adam Brett

Hmm, I'll think about that. Ease of maintenance is one of the requirements
of this system. It was designed so that, as just happened, when the
support person phones up and says it hit 4Gb in two days again, I can just
say "delete the filestore.* files" and on restart it re-creates and off it
goes again. They haven't of course installed the new one I gave them this
morning. With loads of files, it would end up falling over Windows' stupid
delays as Explorer loads up its view with hundreds of files.

/Matthew Jones/

adam


Interesting. I used the Eudora model (attachments stored on disc, separate database (tables) for each email folder) in the first email enabled app I wrote. I'm now moving to storing attachments in a table along with the message and just having one table for all folders. The reason I decided to change was that in a multi user system if an email is moved or deleted then moving or deleting  attachments can go wrong if the attachment is currently open.

It makes admin and searching so much easier if there's just one table.

Roy Lambert