On 05/08/2013 01:54 AM, Scott Robison wrote:
On Tue, 30 Apr 2013 23:56:35 -0700 (PDT) Paul Vercellotti <[email protected]> wrote:We've got some trouble with FTS4 queries taking too long in which we're looking for a subset of matching records in the FTS table, as narrowed by a non-FTS table. {details snipped}I've recently had to work through a similar issue. My worst case searching performance wasn't as bad as you're describing but was not "optimal" for our needs, but I had other problems. One was the size of my full text index, which could include millions of rows of email message bodies and various textual header lines. "Pure" inserts into the index weren't too bad, but anytime the FTS b-tree structures got "too big" and needed to be merged, sqlite could enter a really slow state allocating tons of memory during the merge when a single inserted row triggered the behavior. By "really slow state" I mean in excess of twenty minutes processing after allocating a single chunk of memory hundreds of megabytes large. When the full text index grew large enough, it would eventually fail to merge when it could not allocate a sufficiently large block of data (in excess of 200 megabytes, likely due to a highly fragmented heap; the nature of the application and the team that has developed it means that the heap is fragmented, and I can't do much to reduce that fragmentation, even though otherwise plenty of memory is available, without re-writing significant other pieces of code; I want to do that eventually, but it is not an option at the moment).
Another way to go is to use the 'merge=X,Y' and 'automerge=0' commands: http://www.sqlite.org/fts3.html#mergecmd _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
