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
