1 Reply Latest reply on Dec 22, 2009 10:58 AM by Polama

    SQLite Inserts Taking Longer and Longer

    Polama

      The application I'm working on makes repeated calls to a webservice to get data that is then cached in a local sqlite db for the user. Once the db hits ~5mb it starts taking painfully long to run each set of inserts. Calls to the webservice remain quick. There had been an issue with XML not being garbage collected, but I fixed that and now the profiler shows consistent memory usage.

       

      I've tried running the inserts with indexes on and off. I've tried batching the inserts in transactions of 100, or the entire set. The db calls are synchronous.

       

      Running the queries against the database directly (not through the AIR application) suggest that there isn't a slowdown at the 5mb mark there, which is consistent with my experiences with SQLlite. Restarting the application and continuing to download data into an existing project does not resolve the issue, it starts off slow.

       

      So...does anyone have any ideas of other things to try to get insert performance up to a reasonable level? Has anyone else run into similiar issues? Is anyone inserting into 20mb+ dbs and not seeing degrading performance?

       

      Thanks for the help!

        • 1. Re: SQLite Inserts Taking Longer and Longer
          Polama Level 1

          Guess I posted prematurely. Looking closer I realized there was a select happening during this process against a text column without an index. The slowdown was just the increasing cost of looping through the the entire dataset looking at strings that often shared a fairly sizable starting substring. Chalk another problem up to the importance of appropriate indexes in your db!