Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 1 to 10 of 20 total |
DELETE partial |
Wed, Nov 1 2006 9:36 AM | Permanent Link |
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/ | |
Wed, Nov 1 2006 9:50 AM | Permanent Link |
"Jose Eduardo Helminsky" | 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 |
Wed, Nov 1 2006 10:04 AM | Permanent Link |
"Robert" | "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 |
Wed, Nov 1 2006 10:19 AM | Permanent Link |
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/ | |
Wed, Nov 1 2006 10:19 AM | Permanent Link |
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/ | |
Wed, Nov 1 2006 10:28 AM | Permanent Link |
> 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/ | |
Wed, Nov 1 2006 10:32 AM | Permanent Link |
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/ | |
Wed, Nov 1 2006 2:44 PM | Permanent Link |
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/ | |
Wed, Nov 1 2006 7:39 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Wed, Nov 1 2006 7:42 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |