Thank you for the advice, I'll try it in my application. regards Il 12/10/2010 17.17, Pavel Ivanov ha scritto: > Michele, > > Here is another thought for you to consider. Apparently your > application consistently generates some records, each record is marked > with a timestamp of its creation and after some time you have to > garbage-collect all records that are at least at a certain amount of > time in the past. You can do this with generational gc: split your > full database in parts according to record creation time and then your > garbage collection will consist of deletion of database file from file > system without deleting any records. E.g. let's say you start with one > part, you write your records in it for 15 minutes. Then you create new > part and write all new records in it. Old part just sits there for > querying. After 15 minutes you create new part and so on. Then when > time comes and all records in the first part must be deleted you just > delete that database file and that's it - no interruption in access > for newly created records. Of course this implementation complicates > querying of existing records especially when you need some statistics > pretty often (as opposed to querying individual records) but for some > access patterns it can give you a huge performance improvement (it > actually did for my application). > > > Pavel > > On Mon, Oct 11, 2010 at 11:13 AM, Jay A. Kreibich<j...@kreibi.ch> wrote: >> On Mon, Oct 11, 2010 at 02:08:54PM +0200, Michele Pradella scratched on the >> wall: >>> Ok so the main idea it's always the same: split the DELETE to make the >>> operation on less records, but do it more often. >> Another thought occurs to me... If your insert rates are fairly >> consistent (e.g. the number of records per minute is consistent, or >> at least predictable) then you can just use the table as a circular >> buffer. That is, rather than inserting new data and deleting the >> old data, simply overwrite the older records with the new data. >> Rather than inserting new records, simply find the oldest record and >> update it with the new data. >> >> If the records have similar static length content (e.g. no variable >> length text strings) this should be fairly fast, and will eliminate >> the need to to delete the whole records. The UPDATE might be a tad >> slower than a unencumbered INSERT, but it is still likely to be >> faster than an INSERT plus a later DELETE. >> >> Be careful, however, as even integers are variable-length records in >> SQLite (if their magnitude is different). It might be best to use >> fixed size strings, even for the numeric values. The UPDATE will be >> much faster if the new data fits into the same "slot" as the old >> record, and it can be updated in-place. >> >> You could setup the inserts to find the oldest time and update that >> records (which should be fairly quick if there is an index on your >> timestamp column) or you could just manually create however many >> NULL entries you need and explicitly update incremental records. >> When the application starts up, just find the oldest date and >> continue. >> >> This all depends on being able to predict the number of records >> required to meet you storage needs, however. I suppose you could >> allow the database to expand as needed (that is, get the oldest date, >> and if it is not outside your window, INSERT rather than UPDATE). >> There are lots of ways to do this, the specifics depend on your >> needs. >> >> >> -j >> >> -- >> Jay A. Kreibich< J A Y @ K R E I B I.C H> >> >> "Intelligence is like underwear: it is important that you have it, >> but showing it to the wrong people has the tendency to make them >> feel uncomfortable." -- Angela Johnson >> _______________________________________________ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > >
-- Selea s.r.l. Michele Pradella R&D SELEA s.r.l. Via Aldo Moro 69 Italy - 46019 Cicognara (MN) Tel +39 0375 889091 Fax +39 0375 889080 *michele.prade...@selea.com* <mailto:michele.prade...@selea.com> *http://www.selea.com* _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users