Results 1 to 7 of 7

Thread: Sending data to SQLite faster using transaction / commit

  1. #1
    Join Date
    Oct 2010
    Posts
    91
    Thanks
    38

    Default Sending data to SQLite faster using transaction / commit

    Hello!

    I want to transmit data to a SQLite database but this is done record
    be record and takes much to long.

    So I decided to use transaction(). But it is still working record by record,
    so something must be wrong.

    Can you tell me what is wrong?

    Kind regards,
    HomeR

    This is the code:



    Qt Code:
    1. QSqlDatabase dbSQL = QSqlDatabase::database();
    2. dbSQL.transaction();
    3. populateSQL(RemoteName,Purpose,DateString,value,currQString);
    4. dbSQL.commit();
    To copy to clipboard, switch view to plain text mode 

    Qt Code:
    1. int Banking::populateSQL(QString RemoteName,QString Purpose,QString Date,double Value ,QString Currency)
    2. {
    3. QSqlQuery query;
    4.  
    5. query.prepare(
    6. "INSERT INTO onlinebankingdata ("
    7. "RemoteName,"
    8. "Purpose,"
    9. "Date,"
    10. "Value,"
    11. "Currency)"
    12. "VALUES ("
    13.  
    14. ":RemoteName,"
    15. ":Purpose,"
    16. ":Date,"
    17. ":Value,"
    18. ":Currency)");
    19.  
    20.  
    21. query.bindValue(":RemoteName", RemoteName);
    22. query.bindValue(":Purpose", Purpose);
    23. query.bindValue(":Date", Date);
    24. query.bindValue(":Value", Value );
    25. query.bindValue(":Currency", Currency);
    26.  
    27.  
    28.  
    29. bool test = query.exec();
    30. if (!test)
    31. {
    32. QMessageBox::warning(0, QObject::tr("Database Error"),query.lastError().text());
    33. }
    34.  
    35. return 0;
    36. }
    To copy to clipboard, switch view to plain text mode 

  2. #2
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,359
    Thanks
    3
    Thanked 5,015 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: Sending data to SQLite faster using transaction / commit

    A transaction is not about batch processing. It's a guarantee that either everything goes to the database or nothing. And in the code here you have just a single record so I'm not sure what exactly you would expect to achieve.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  3. The following user says thank you to wysota for this useful post:

    homerun4711 (17th January 2011)

  4. #3
    Join Date
    Oct 2010
    Posts
    91
    Thanks
    38

    Default Re: Sending data to SQLite faster using transaction / commit

    You are right. Since populateSQL is called within a while-loop
    I have to put transaction and commit outside of the loop.
    Damn monday mornings... Thanks a lot.

  5. #4
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,359
    Thanks
    3
    Thanked 5,015 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: Sending data to SQLite faster using transaction / commit

    It will not make your application faster. If you want it to be faster then either use something like:
    Qt Code:
    1. bool populateSQL(QSqlQuery query, QString RemoteName,QString Purpose,QString Date,double Value ,QString Currency) {
    2. query.bindValue(":RemoteName", RemoteName);
    3. query.bindValue(":Purpose", Purpose);
    4. query.bindValue(":Date", Date);
    5. query.bindValue(":Value", Value );
    6. query.bindValue(":Currency", Currency);
    7. return query.exec();
    8. }
    9.  
    10. q.prepare("INSERT INTO onlinebankingdata ("
    11. "RemoteName,"
    12. "Purpose,"
    13. "Date,"
    14. "Value,"
    15. "Currency)"
    16. "VALUES ("
    17.  
    18. ":RemoteName,"
    19. ":Purpose,"
    20. ":Date,"
    21. ":Value,"
    22. ":Currency)");
    23.  
    24. for(int i=0;i<...;++i){
    25. populateSQL(q, ...);
    26. }
    To copy to clipboard, switch view to plain text mode 

    or use the multi-line insert semantics (passing several VALUES sections in one statement).
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  6. #5
    Join Date
    Oct 2010
    Posts
    91
    Thanks
    38

    Default Re: Sending data to SQLite faster using transaction / commit

    Hm, putting transaction and commit outside ot the while-loop actually did make my application faster. Beforehand it tool about a minute to insert all records into the SQLite database (with loud harddisk noise) now it is not even noticable, done on the spot.

    You suggested a for-loop in your example. I cant use this, because I add an undefined number of records.
    I don't get why your example is even faster, could you please explain it with a few words?

  7. #6
    Join Date
    Jan 2006
    Location
    Warsaw, Poland
    Posts
    33,359
    Thanks
    3
    Thanked 5,015 Times in 4,792 Posts
    Qt products
    Qt3 Qt4 Qt5 Qt/Embedded
    Platforms
    Unix/X11 Windows Android Maemo/MeeGo
    Wiki edits
    10

    Default Re: Sending data to SQLite faster using transaction / commit

    Quote Originally Posted by homerun4711 View Post
    You suggested a for-loop in your example. I cant use this, because I add an undefined number of records.
    For loop is just a different syntax for the while loop semantics. What loop you use is irrelevant. What is relevant is that you prepare the query once and execute it many times.
    I don't get why your example is even faster, could you please explain it with a few words?
    It lets the database optimize things better. If running inside a transaction helps then you can merge the two approaches. You can use QSqlQuery::addBindValue() to bind a series of datasets and execute the statement in one go and you can also use QSqlQuery::execBatch() if you bind lists of values in one go.
    Your biological and technological distinctiveness will be added to our own. Resistance is futile.

    Please ask Qt related questions on the forum and not using private messages or visitor messages.


  8. The following user says thank you to wysota for this useful post:

    homerun4711 (17th January 2011)

  9. #7
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,729
    Thanks
    13
    Thanked 1,610 Times in 1,537 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: Sending data to SQLite faster using transaction / commit

    Sqlite bulk insert performance is substantially improved by performing the inserts within a transaction. Without the explicit transaction each insert becomes a standalone transaction along with all the overhead of file locking and buffer flushing. The encompassing transaction produces just one set of these overheads regardless of the number of inserts. The trade-off is that the bulk inserts either succeed or fail en masse.
    http://www.sqlite.org/faq.html#q19

  10. The following user says thank you to ChrisW67 for this useful post:

    homerun4711 (17th January 2011)

Similar Threads

  1. a faster QSqlQueryModel::data()
    By baray98 in forum Qt Programming
    Replies: 0
    Last Post: 24th September 2009, 01:56
  2. Unable to commit transaction
    By cydside in forum Qt Programming
    Replies: 6
    Last Post: 23rd July 2009, 08:47
  3. SQLite - QSqlDatabase::transaction()
    By whitefurrows in forum Qt Programming
    Replies: 6
    Last Post: 5th May 2009, 17:06
  4. Sending raw data using QextSerialPort [solved]
    By DrDonut in forum Qt Programming
    Replies: 1
    Last Post: 7th January 2009, 14:14
  5. sending data over signal
    By gyre in forum Newbie
    Replies: 1
    Last Post: 17th December 2007, 00:10

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.