I've got an app that runs 24/7. It just filled up 4Gb of blob file, so
I've reduced the number of days that it keeps records for and it is in the
middle of a purge of old data now. But it has so far taken over 30 minutes
to delete, and while this is happening the other thread that adds records
seems to be stalled. I presume the stall is due to the delete locking
something.

Hence me wondering if there is a way to delete records, but a bit at a
time. Sort of a:
DELETE FROM MyTable WHERE DateCol < "<two-days-ago>" LIMIT 100

Where the LIMIT specifies the maximum number to delete. I'd use rows-
affected to see if there is more to do, and would Sleep() for a while
between them.

Any other workarounds are also welcome.

/Matthew Jones/

Matthew

Do you need to use SQL ?
If not, my suggestion will be

Table.SetRange([YourDate],[YourDate]);
Table.First;
nCount := 0;
while nCount < 100 do begin
  Table.Delete;
  inc(nCount);
end;

It is simple but absolutelly faster than SQL, according your description of
the problem.

Eduardo

"Matthew Jones" <mattjones@cix.co.uk> wrote in message
news:memo.20061101143126.3700A@nothanks.nothanks.co.uk...
>
> Any other workarounds are also welcome.
>

If you have an index on date, you can loop something like this

for j := 12 downto 2 do begin
 Query.Date := IncMonth(date, 0 - j);
 Query.ExecSQL;
 delay some
end;

Robert

I think I may well end up with that. I've discovered the OnQueryProgress
event which I presume will kick in with DELETE, but my main purpose for
that for now is to allow the thread to kill a long query if the thread has
been terminated. What I can't determine from the documentation is whether
the whole query is stopped and undone or not. That is, if I stop the query
at 50%, are 50% of the records deleted still, or does it have to start
again each time.

/Matthew Jones/

Thanks for the suggestion. Your "Month" timescale is too long - hours
would be better, but the idea is sound. I could just find out the oldest,
and then do it for each hour until I reach the target.

Reading the manual on DELETE, I see there is a COMMIT clause - I'll read
further to see if that is what I need.

/Matthew Jones/

> COMMIT clause

Hmm, I can see that a transaction is being automatically created, and that
I can get it to commit according to a number of rows, but I'm not sure
whether that releases the transaction lock temporarily. If I could know
that committing every 10 rows would allow the rest of the system to nip in
and do its stuff, that would be excellent. But I suspect that this is just
a "be sure it is all good on disk" periodically facility.

I note also that I have a join on the DELETE as the date is in another
table. I'll bet that doesn't make it pretty!

Now, I only noticed this due to filling the 4Gb limit, and I changed a
parameter to tell it to delete two days worth instead of 4 hours, but I
suspect that having my own table stepping code is the only way to achieve
the "low locking" I need.

/Matthew Jones/

One final thought - I figure that I can still use the SQL query to find
the records (sorry, rows!) to delete and then use a table to find and
delete them by the row identifier. This way I keep the convenience of SQL,
while using the table for low locking.

/Matthew Jones/

Okay, now I'm intrigued! I just created a SELECT that represented the
DELETE to see if it was related to the plan or something being non-
optimal. The SELECT says "563 rows affected in 0.141 seconds". But delete
the first line and make it a DELETE, and even DBSYS goes AWOL for ages. I
grabbed the databases from the troublesome server, and I can repeat it
here on my super-duper PC (4Gb ram, 4 x Xeon cores), though I gave up
after 10 mins of waiting for my app to do it, and about 2 mins of dbsys.
DBISAM 4.22 Build 4 by the way.

The SQL is:

SELECT cqItemID, fsQueueID, fsReferenceInt
FROM FileStore
INNER JOIN ChangeQueue ON (cqItemID = fsQueueID)
WHERE cqTimeStamp < '2006-10-30'

or

DELETE
FROM FileStore
INNER JOIN ChangeQueue ON (cqItemID = fsQueueID)
WHERE cqTimeStamp < '2006-10-30'

Perhaps some more info on the columns is needed. The main thing is I have
a FileStore database with a queue identifier and a Blob to contain the
file contents. The ChangeQueue table has information about a group of
files, each of which is stored in the FileStore. Thus there is a many to
one reference. The FileStore has grown to 4Gb, all of which is fairly
efficiently used by the blobs - optimising when it was 2Gb didn't reduce
it at all significantly so it isn't a block management thing.

Okay, I'm just trying a new tack - removing the JOIN.

DELETE
FROM FileStore
WHERE fsStoreDate < '2006-10-30'

This is now very slowly working away - the database has 1702 records. At
about 7 minutes it is not yet showing the 15% point. Okay, killed that to
preserve my data and try another. Quick verify shows nothing got deleted
and it is okay.

Next test: (Can you tell this is on a copy? 8-)

DELETE
FROM FileStore

How long would this be expected to run for on 1702 records? Actually, 1698
as I deleted some using the table editor just to be sure there was some
leeway in the file.

Hmm, 4 minutes so far, and not yet got to 5%. Okay, 6 mins and 5%. Kill
that again. Go for a full repair with index rebuild. Let's be really sure
before I give the database to Tim for comment. Hmm, no log messages from
that. I'll try an optimize by date. Hmm, the blob file went down from 3.72
GB (4,000,000,512 bytes) to 2.66 GB (2,867,474,944 bytes). The simple
DELETE is still taking a long time though.

Tim, would you like a large database to play with? The zip is 104Mb, so
I'd upload it to a web site somewhere.

/Matthew Jones/

Matt,

<< Hmm, I can see that a transaction is being automatically created, and
that I can get it to commit according to a number of rows, but I'm not sure
whether that releases the transaction lock temporarily. >>

Unfortunately, it doesn't.  The lock hangs around.

<< If I could know that committing every 10 rows would allow the rest of the
system to nip in and do its stuff, that would be excellent. But I suspect
that this is just a "be sure it is all good on disk" periodically facility.
>>

Yep, you are 100% correct - it is a "gotta flush some of this data to disk"
facility.

<< Now, I only noticed this due to filling the 4Gb limit, and I changed a
parameter to tell it to delete two days worth instead of 4 hours, but I
suspect that having my own table stepping code is the only way to achieve
the "low locking" I need. >>

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.

--
Tim Young
Elevate Software
www.elevatesoft.com

Matt,

<< How long would this be expected to run for on 1702 records? Actually,
1698 as I deleted some using the table editor just to be sure there was some
leeway in the file. >>

DBISAM has to delete the BLOB blocks also, and that is what is taking so
long.  If the BLOB block size for the table is 512 bytes, then that is the
main reason for the slowdown.  Try increasing the BLOB block size to
something bigger like 2k or 4k.

For the record, ElevateDB doesn't have this issue because it handles BLOB
block recycling differently, mainly due to this phenomenon.  The time it
takes to delete 1700 rows in ElevateDB is not affected by the size of any of
the BLOBs.

--
Tim Young
Elevate Software
www.elevatesoft.com