Results 1 to 8 of 8

Thread: Very slow sqlite performance?

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,327
    Thanks
    317
    Thanked 871 Times in 858 Posts
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: Very slow sqlite performance?

    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.
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

  2. #2
    Join Date
    May 2017
    Posts
    14
    Qt products
    Qt5 Qt/Embedded
    Platforms
    Unix/X11

    Default Re: Very slow sqlite performance?

    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.
    Last edited by while_e; 20th September 2017 at 21:27.

  3. #3
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    5,327
    Thanks
    317
    Thanked 871 Times in 858 Posts
    Qt products
    Qt5
    Platforms
    Windows

    Default Re: Very slow sqlite performance?

    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.
    <=== The Great Pumpkin says ===>
    Please use CODE tags when posting source code so it is more readable. Click "Go Advanced" and then the "#" icon to insert the tags. Paste your code between them.

  4. #4
    Join Date
    May 2017
    Posts
    14
    Qt products
    Qt5 Qt/Embedded
    Platforms
    Unix/X11

    Default Re: Very slow sqlite performance?

    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.

Similar Threads

  1. Replies: 5
    Last Post: 24th December 2015, 15:57
  2. Phonon performance slow down
    By rspock in forum Qt for Embedded and Mobile
    Replies: 4
    Last Post: 10th June 2013, 07:47
  3. Slow SQLite performance
    By themagician in forum Newbie
    Replies: 0
    Last Post: 26th April 2012, 14:23
  4. QT App performance is too slow on OSX
    By joshhhab in forum Newbie
    Replies: 1
    Last Post: 28th May 2011, 08:30
  5. Replies: 1
    Last Post: 8th August 2010, 20:04

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Qt is a trademark of The Qt Company.