Results 1 to 8 of 8

Thread: Very slow sqlite performance?

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

    Default Very slow sqlite performance?

    System Info:
    OS: Debian
    Kernel: 4.4
    Platform: BeagleBoneBlack
    Sqlite3: version (SQLite version 3.8.7.1 2014-10-29 13:59:56).

    DB Info:
    Schema: CREATE TABLE Table1 (rowId INTEGER PRIMARY KEY AUTOINCREMENT, parm varchar(32), value varchar(32));
    Total Rows: 164

    If I drop into terminal, manually open DB, and issue a simple update command like (UPDATE Table1 SET value='45' WHERE parm='Parm3') it is very fast. It will occasionally lag for a second or so. I set pragma synchronous = OFF, and same with journal_mode, just to test. This makes it even faster, and I never see the occasional lag.

    So I use Qt to open the DB, set those same pragma settings, and use something like this:
    Qt Code:
    1. QSqlQuery query;
    2. QString strQuery = QString("UPDATE %1 SET value='%2' WHERE parm='%3'").arg(table).arg(value).arg(setting);
    3.  
    4. QTime *time = new QTime();
    5. query.prepare(strQuery);
    6. time->start();
    7.  
    8. if(!query.exec()) {
    9. qDebug() << "Error with query:: " << strQuery << "|" << query.lastError();
    10. return false;
    11. }
    12.  
    13. qDebug() << "Duration::" << time->elapsed();
    To copy to clipboard, switch view to plain text mode 

    This results in an output of an elapsed time between 2000-3000 with the same simple update command? Not every time, but like 1/5 updates it will hangup for a few seconds. Again, I use the same pragma settings and update command via terminal and it runs like butter, but via Qt it seems to have a ton of overhead on ~1/5 updates. Has anyone run into this issue, or have any insight as to why this might be the case?
    Last edited by while_e; 19th September 2017 at 18:21.

  2. #2
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    4,720
    Thanks
    259
    Thanked 760 Times in 750 Posts
    Qt products
    Qt5
    Platforms
    Windows Android

    Default Re: Very slow sqlite performance?

    I've used SQLite (via the QSqlite driver) to create databases that are 300+ GB in size. When I use SSD (solid state disk) drives as the source and output drives, it takes a few hours to read and process 450 GB of input ASCII data and create the 360 GB database. The DB creation uses "INSERT" rather than "UPDATE", I prepare the query once with placeholders for values, then I bind the values just prior to each insert. The DB has one table and 11 indexes plus the auto index for the table's primary key. The final DB has around 69 million rows in the main table.

    Key-based searching is almost instantaneous after the database has been created. All of this is done using the QSqlite driver and QSqlDatabase / QSqlQuery.

    So, basically SQLite isn't the bottleneck.

    I am doing it on Windows, not on a BeagleBone platform. I am wondering if due to the limited memory on the BeagleBone (at least I assume so) that the QSqlite driver is caching data and is being forced to swap the cache in and out of RAM that doesn't happen when you use the command line and SQLite directly.

    You might be able to verify this by rewriting your program using the SQLite C library API directly instead of going through the Qt driver. All of the same functionality is available at the API level, it is just a bit more tedious to use.
    <=== 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.

  3. #3
    Join Date
    Mar 2008
    Location
    Kraków, Poland
    Posts
    1,521
    Thanked 278 Times in 273 Posts
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Re: Very slow sqlite performance?

    Quote Originally Posted by d_stranz View Post
    I've used SQLite (via the QSqlite driver) to create databases that are 300+ GB in size. When I use SSD (solid state disk) drives as the source and output drives, it takes a few hours to read and process 450 GB of input ASCII data and create the 360 GB database. The DB creation uses "INSERT" rather than "UPDATE", I prepare the query once with placeholders for values, then I bind the values just prior to each insert. The DB has one table and 11 indexes plus the auto index for the table's primary key. The final DB has around 69 million rows in the main table.

    Key-based searching is almost instantaneous after the database has been created. All of this is done using the QSqlite driver and QSqlDatabase / QSqlQuery.

    So, basically SQLite isn't the bottleneck.
    D_stranz, try to load data in packs, for example 1000 INSERTs in transaction. Something like this :
    Qt Code:
    1. db.transaction();
    2. 1000 x INSERT;
    3. db.commit();
    To copy to clipboard, switch view to plain text mode 
    You can be very positively surprised

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

    Default Re: Very slow sqlite performance?

    d_stranz, I totally didn't think of this as a possibility, but it does make sense. Can anyone suggest a method of testing if the driver is caching data on the BBB? I'd prefer not to rework a bunch of code before proving that's the cause. I'm going to look into it, but just hoping someone here may at least have some breadcrumbs to get me going in the right direction.

  5. #5
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    4,720
    Thanks
    259
    Thanked 760 Times in 750 Posts
    Qt products
    Qt5
    Platforms
    Windows Android

    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.

  6. #6
    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 22:27.

  7. #7
    Join Date
    Jan 2008
    Location
    Alameda, CA, USA
    Posts
    4,720
    Thanks
    259
    Thanked 760 Times in 750 Posts
    Qt products
    Qt5
    Platforms
    Windows Android

    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.

  8. #8
    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, 16:57
  2. Phonon performance slow down
    By rspock in forum Qt for Embedded and Mobile
    Replies: 4
    Last Post: 10th June 2013, 08:47
  3. Slow SQLite performance
    By themagician in forum Newbie
    Replies: 0
    Last Post: 26th April 2012, 15:23
  4. QT App performance is too slow on OSX
    By joshhhab in forum Newbie
    Replies: 1
    Last Post: 28th May 2011, 09:30
  5. Replies: 1
    Last Post: 8th August 2010, 21: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
  •  
Digia, Qt and their respective logos are trademarks of Digia Plc in Finland and/or other countries worldwide.