Results 1 to 4 of 4

Thread: Dbase Access, very slow

  1. #1
    Join Date
    Aug 2008
    Posts
    15
    Qt products
    Qt4
    Platforms
    Unix/X11 Windows

    Default Dbase Access, very slow

    I used to think Qt is slow accessing MYSQL (Maria) because I operates over the network, but now I tried a SQLIte dBase that should be blinking fast, but it take like 50ms per dbase exec. I tried to remove some parameters but still it is slow, write 6000 records and you can go and make coffee., just to be in time to wait for it to finish.
    How can one speed up things.

    The Table was created with, (so it has a primary key, no relations at all, and one can drop it and create it with nothing .. )
    Qt Code:
    1. // check if the table exist, if not create a new one
    2. if (check_if_tables_exist ((const INT_8 *)TABEL_STRAP_TABLE) == false)
    3. {
    4. //-----------------------------------------------------
    5. // create the calibration table
    6. //-----------------------------------------------------
    7. query_str.sprintf ("CREATE TABLE %s "
    8. "(id INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL, "
    9. "comp_index INTEGER NOT NULL DEFAULT(0), "
    10. "product_type INTEGER NOT NULL DEFAULT(0), "
    11. "temperature INTEGER NOT NULL DEFAULT(0), "
    12. "frequency INTEGER NOT NULL DEFAULT(0), "
    13. "volume_litre INTEGER NOT NULL DEFAULT(0), "
    14. "calibrator INTEGER NOT NULL DEFAULT(0), "
    15. "calibrator_number INTEGER NOT NULL DEFAULT(0), "
    16. "time_stamp DATETIME NOT NULL DEFAULT(0000 - 00 - 00), "
    17. "calib_delta_const INTEGER NOT NULL DEFAULT(0), "
    18. "error_index INTEGER NOT NULL DEFAULT(0)) ;",
    19. TABEL_STRAP_TABLE);
    20. QString status;
    21. if (!execute_query (query_str, status))
    22. {
    23. if (verbose)
    24. gprintf (FLRed"\n> SQLite [%s] Table {%s} ERR[%s] "FGray,
    25. get_my_dbase_name().toLatin1().data(),
    26. TABEL_STRAP_TABLE,
    27. status.toLatin1().data());
    28. return false;
    29. }
    To copy to clipboard, switch view to plain text mode 
    and the qeury looks like this
    Qt Code:
    1. /** ---------------------------------------------------------------------------
    2.  * \brief This method execute a query that required no return values
    3.  *
    4.  * \param query_str The sqlite query to be processed
    5.  * \param error_message On error the sqlite error message will
    6.  * be reported in this variable
    7.  * \return True on success of False on failure
    8.  **/
    9. BOOL sqlite_class::execute_query(const QString & query_str,
    10. QString & error_message)
    11. {
    12. QSqlQuery mysql_query(lite_db);
    13.  
    14. error_message.clear();
    15.  
    16. // speed up things
    17. mysql_query.setForwardOnly(true);
    18.  
    19. if(!mysql_query.exec(query_str))
    20. {
    21. QString status;
    22. status = mysql_query.lastError().text();
    23.  
    24. if (verbose)
    25. gprintf (FLRed"\n dBase [%s]"
    26. "\n Error : query_str[%s] "
    27. "\n : error [%s] "FGray,
    28. sqlite_file_name.toLatin1().data(),
    29. query_str.toLatin1().data(),
    30. status.toLatin1().data());
    31. error_message.sprintf (" Error : dBase [%s]\n"
    32. " : query_str[%s]\n"
    33. " : error [%s] ",
    34. sqlite_file_name.toLatin1().data(),
    35. query_str.toLatin1().data(),
    36. status.toLatin1().data());
    37. return false;
    38. }
    39. return true;
    40. }
    To copy to clipboard, switch view to plain text mode 
    And the actual insert like this
    Qt Code:
    1. /** ---------------------------------------------------------------------------
    2.  * @brief calibration_utils::write_strap_table_row
    3.  * @param calib
    4.  * @param sqlite
    5.  */
    6. void calibration_utils::write_strap_table_row(const CALIBRATION * calib,
    7. strap_table_class * sqlite)
    8. {
    9. QTime time;
    10. QString sqlErrorStr,format = "d MMMM yyyy hh:mm", query_str;
    11. QDateTime datetime;
    12.  
    13. datetime.setMSecsSinceEpoch(calib->time_stamp);
    14.  
    15. // INSERT INTO KOOS (PIET,BEN) VALUES ('1','2')
    16. query_str.sprintf ("INSERT INTO %s (" // 0
    17. "comp_index,product_type,temperature,frequency," // 1,2,3,4
    18. "volume_litre,calibrator,calibrator_number, " // 5,6,7
    19. "time_stamp, error_index)" // 8,9
    20. " VALUES ('%d','%d','%d','%d',"
    21. " '%f',\"%s\",'%d',\"%s\",'%d') ;",
    22. TABEL_STRAP_TABLE, // 0
    23. calib->comp_index, // 1
    24. calib->product_type, // 2
    25. calib->temperature, // 3
    26. calib->frequency, // 4
    27. calib->volume_litre, // 5
    28. calib->calibrator, // 6
    29. calib->calibrator_number, // 7
    30. datetime.toString(format).toLatin1().data(), // calib->time_stamp, // 8
    31. calib->error_index // 9
    32. );
    33. time.start();
    34. if (!sqlite->execute_query(query_str,sqlErrorStr))
    35. {
    36. gprintf ("\n> insert_dbase_values ERROR ");
    37. }
    38. gprintf ("\n>> Query take %d ms", time.elapsed());
    39. }
    To copy to clipboard, switch view to plain text mode 

  2. #2
    Join Date
    Dec 2009
    Location
    New Orleans, Louisiana
    Posts
    791
    Thanks
    13
    Thanked 153 Times in 150 Posts
    Qt products
    Qt5
    Platforms
    MacOS X

    Default Re: Dbase Access, very slow

    If you don't perform inserts, updates, and deletes in a transaction, each insert, update, and delete will be committed using an implicit transaction which will definitely slow down your program.

    Try wrapping your CRUD statements with a transaction as shown below:

    Qt Code:
    1. lite_db.transaction();
    2. // do all of your inserts/updates/deletes
    3. lite_db.commit(); // commit changes if successful or lite_db.rollback(); if you encountered an error, etc.
    To copy to clipboard, switch view to plain text mode 

    Another optimization would be to prepare your insert statement and use QSqlQuery::bindValue() to bind values to the named or unnamed query parameters, etc. Preparing the insert statement allows the db engine to optimize the query.

    Hope that helps.
    Last edited by jefftee; 25th March 2015 at 19:28.

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

    Default Re: Dbase Access, very slow

    Did you actually measure what is the bottleneck of your program?
    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.


  4. #4
    Join Date
    Mar 2009
    Location
    Brisbane, Australia
    Posts
    7,546
    Thanks
    13
    Thanked 1,578 Times in 1,507 Posts
    Qt products
    Qt4 Qt5
    Platforms
    Unix/X11 Windows
    Wiki edits
    17

    Default Re: Dbase Access, very slow

    Further information regarding jthomps comment:
    http://sqlite.org/faq.html#q19

Similar Threads

  1. Very slow startup by the first run
    By denizlitr in forum Qt for Embedded and Mobile
    Replies: 14
    Last Post: 2nd November 2016, 07:30
  2. Slow Microsoft Access QSqlQuery (possible bug)
    By yeikos in forum Qt Programming
    Replies: 0
    Last Post: 12th May 2014, 10:55
  3. Qt goes slow?
    By Cucus in forum Qt Programming
    Replies: 2
    Last Post: 20th July 2011, 04:43
  4. QT App performance is too slow on OSX
    By joshhhab in forum Newbie
    Replies: 1
    Last Post: 28th May 2011, 08:30
  5. QGraphicsScene too slow
    By samsam in forum Qt Programming
    Replies: 1
    Last Post: 10th July 2009, 16:18

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.