Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM General » View Thread |
Messages 11 to 20 of 20 total |
DELETE partial |
Thu, Nov 2 2006 2:35 AM | Permanent Link |
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/ | |
Thu, Nov 2 2006 4:00 AM | Permanent Link |
> 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/ | |
Thu, Nov 2 2006 5:19 AM | Permanent Link |
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 | |
Thu, Nov 2 2006 5:34 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Nov 2 2006 6:46 AM | Permanent Link |
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/ | |
Thu, Nov 2 2006 6:46 AM | Permanent Link |
> 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/ | |
Thu, Nov 2 2006 6:58 AM | Permanent Link |
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/ | |
Thu, Nov 2 2006 12:58 PM | Permanent Link |
adam | 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 |
Thu, Nov 2 2006 1:58 PM | Permanent Link |
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/ | |
Fri, Nov 3 2006 4:11 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
« Previous Page | Page 2 of 2 | |
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 |