0

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?

Community
  • 1
  • 1
Stephen H. Anderson
  • 978
  • 4
  • 18
  • 45
  • Have you already read http://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite?rq=1 which I suspect may offer some things to try? – Mark B Jul 20 '15 at 15:01

2 Answers2

1

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.

Colonel Thirty Two
  • 23,953
  • 8
  • 45
  • 85
0

if you are having multiple threads i would not suggest you to turn the synchronous off.I doubt if the speed will be increased by just moving the lock outside to your class

What i would suggest is that you think of noramalization of database so that you dont have to read a huge data every time.

Pradheep
  • 3,553
  • 1
  • 27
  • 35