Very slow sqlite performance?

[d_stranz]

D_stranz, try to load data in packs, for example 1000 INSERTs in transaction.

Oh, way ahead of you here. I'm using 10000 entry packs. I -was- very happy to see the performance with that change.

[while_e]

So, I went ahead and built a small test that performed 100x update queries. I first did it using the QSQLITE driver, it was routinely about 20-40ms per, with the occasional ~1000ms, and even one ~9000ms. It seems to happen the first few queries, and then it levels out.. I then changed it to use sqlite3 libraries directly, and it was routinely 2-8ms per, except 1/10 would jump to ~15ms, and 1/50 would jump to ~100ms.


The thing that's bugging me though, I've been using the QSQLITE driver for quite a while now with the BBB platform, and never had an issue. I updated to kernel 4.4, and a newer Debian version, which likely brought about newer sqlite3 libraries, and now I have this issue. Rolling back isn't really an option at this point, so I'd like to track down where QSQLITE could be failing me. Guess my best bet is to dig into it and see what I can come up with, but was hoping someone here would give me the eureka moment I so desired.

[d_stranz]

What else is happening on your BBB system when you are running these tests? Are there background processes with higher priority which could cause suspension of your DB process? Are you writing your DB to disk? Could caching in memory followed by committing to disk cause a delay? SQLite has some tuning parameters that can affect these things; the default implementation of the QSQLITE driver may not be setting them appropriately for your application. You can modify these values through SQL PRAGMA statements.

[while_e]

I have tried a plethora of PRAGMA settings (automatic_index, synchonous, journal_mode, page_size, cache_size, and more). They do affect timing, just not in a meaningful way. When I'm running the test, there's no other applications running that would cause issues. Running a 'top' shows nothing using significant resources at all. I've moved to using sqlite3.h directly, and given up on QSQLITE because it's just not worth the time and energy at the moment. When I have this project finished up, I'll have more time to look into what about QSQLITE could be causing the issue.

The thing that bugs me though is that I've used this same code in the past with Qt 5.3, kernel 3.8, and an older flavor of Debian, without any issues at all. So something about newer Qt, kernel, or OS is the first breadcrumb. Just don't have to time to pull at that thread just yet. Was hoping someone else has seen these issues, but apparently not.

Thanks for suggestions. Will report back if when I can track it down to hopefully assist anyone else that runs into the issue.