Just FYI, FTS writes each transaction's index data in a segment, then does segment merges over time. So there's some advantage to bulk updates versus one-at-a-time updates in terms of index fragmentation and write overhead. Having an in-memory FTS table which you spill to the on-disk table(s) as a big transaction can work pretty well.
Note that big transactions can have their own issues, for instance if you end up having to spill the page cache. [Obviously, you'll want to test this for your case.] -scott On Wed, Feb 17, 2016 at 8:20 AM, Dave Baggett <dmb at inky.com> wrote: > That's a great suggestion. One issue, though is that I'd have to run two > FTS searches to search -- one on the disk-based database, and one on the > memory-based one. I also already have the database split into 8 .dat files > for scaling purposes. :) > > But this may be workable -- thanks. (BTW, I am using SQLite via apsw -- > thanks for that too!) > > Dave > > Sent with inky<http://inky.com?kme=signature> > > "Roger Binns" <rogerb at rogerbinns.com> wrote: > > -----BEGIN PGP SIGNED MESSAGE----- > > Hash: SHA1 > > > > On 17/02/16 06:37, Dave Baggett wrote: > > > I'd welcome any suggestions > > > > How about two databases? Create an in memory database for the cache. > > Then whenever it hits a certain size (eg 64MB) or time passed (eg 5 > > minutes), copy/move data from the memory database to the persistent > > (disk) one. This ensures the writes to the disk database are in big > > chunks. > > > > Roger > > -----BEGIN PGP SIGNATURE----- > > Version: GnuPG v2 > > > > iEYEARECAAYFAlbEm9AACgkQmOOfHg372QR9rwCgu+MPM+kJEGYlBTzkKRYhHsOu > > U98AoOZ4kBue7MV6Q8P9+vkljyJGobVu > > =61/4 > > -----END PGP SIGNATURE----- > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users at mailinglists.sqlite.org > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
