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<[email protected]> 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
>> [email protected]
>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> 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
*[email protected]* <mailto:[email protected]>
*http://www.selea.com*
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
