The questions coming to mind at the moment: What is the schema of the table holding the BLOBs? What is the normal size for the blobs? How are you doing the inserts and deletes? What journal mode are you using?
I would think normal way to delete a record is the simple delete from blob_table where primary_key_id = ?; My understanding is that incremental vacuum basically says: "I want to shrink the file size by filling free pages at the front with data from the back to be able to truncate the file." It doesn't do any re-ordering or sorting, it just moves data from the end of the file into any free space closer to the front. Since you're adding every 250ms and only deleting every 600, then the file size should be progressively increasing, and any free pages should be used up relatively quickly, so I don't think incremental vacuum would do a lot. -----Original Message----- From: sqlite-users <sqlite-users-boun...@mailinglists.sqlite.org> On Behalf Of Zhu, Liang [AUTOSOL/ASSY/US] Sent: Monday, July 15, 2019 3:39 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] how to delete BLOB object from the data base Sqlite Experts, I have a table contains the BLOB object, I am inserting to the BLOB data into the table at every 250ms, I delete the oldest row at every 600ms, also I am reading the data from the database at every 10ms. After almost of 100,000 insert, delete and select operations, I am getting the SQLite_locked error on delete, and my data from the select statement are junk. To prevent the database fermentation, I tried PRAGMA incremental_vacuum(1000) and PRAGMA incremental_vacuum; The performance improved some when I starting using PRAGMA incremental_vacuum but I am still getting the junk data. Am I using the incremental_vacuum correctly? And is there any optimal way to delete the BLOB object in the database table Thank you, Liang Zhu | Lead Software Engineer | Branson Ultrasonics Emerson Automation Solutions | 41 Eagle Road | Danbury, CT 06810 | USA T (203) 796-2235 | F (203) 796-0380 liang....@emerson.com<mailto:liang....@emerson.com> The information contained in this message is confidential or protected by law. If you are not the intended recipient, please contact the sender and delete this message. Any unauthorized copying of this message or unauthorized distribution of the information contained herein is prohibited. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
