Search code examples
c++performanceqtsqlitedatabase-optimization

Is it a recommended practice to improve performance using PRAGMA synchronous=OFF

From this question: SQLite updating ONE record is very (relatively) slow

I see he got a big speed gain using PRAGMA synchronous=OFF.

I'm facing very slow sqlite update times (250ms) and I need to do many updates from different threads.

I have many open connections to the database from different threads. Would it be better to have just a central DataBase class wrapping the DB with locks that all threads call and use PRAGMA synchronous=OFF in order to have such speed improvement?

Solution

  • PRAGMA synchronous only affects disk synchronizations; ie. pausing to make sure that the data given to the OS is written to the disk. Moving the lock won't help with that.

    Right now it seems like you're just guessing; you need to do some profiling before you optimize. Where are your slow points? What queries are slow (use EXPLAIN QUERY PLAN)? Are you ANALYZEing?

    Also note that SQLite is not very concurrency-friendly; only one connection may write to the database at a time. If you need high concurrency, consider a different database.