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
