Dbase Access, very slow

[creatron]

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 .. )

// check if the table exist, if not create a new one
        if (check_if_tables_exist ((const INT_8 *)TABEL_STRAP_TABLE) == false)
        {          
            //-----------------------------------------------------
            // create the calibration table
            //-----------------------------------------------------
            query_str.sprintf ("CREATE TABLE %s "
                               "(id INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL, "
                               "comp_index INTEGER NOT NULL DEFAULT(0), "
                               "product_type INTEGER NOT NULL DEFAULT(0), "
                               "temperature INTEGER NOT NULL DEFAULT(0), "
                               "frequency INTEGER NOT NULL DEFAULT(0), "
                               "volume_litre INTEGER NOT NULL DEFAULT(0), "
                               "calibrator INTEGER NOT NULL DEFAULT(0), "
                               "calibrator_number INTEGER NOT NULL DEFAULT(0), "
                               "time_stamp  DATETIME NOT NULL DEFAULT(0000 - 00 - 00), "
                               "calib_delta_const INTEGER NOT NULL DEFAULT(0), "
                               "error_index INTEGER NOT NULL DEFAULT(0)) ;",
                               TABEL_STRAP_TABLE);
            QString  status;
            if (!execute_query (query_str, status))
            {
                if (verbose)
                    gprintf (FLRed"\n> SQLite [%s] Table {%s} ERR[%s] "FGray,
                             get_my_dbase_name().toLatin1().data(),
                             TABEL_STRAP_TABLE,
                             status.toLatin1().data());
                return false;
            }

// check if the table exist, if not create a new one
        if (check_if_tables_exist ((const INT_8 *)TABEL_STRAP_TABLE) == false)
        {          
            //-----------------------------------------------------
            // create the calibration table
            //-----------------------------------------------------
            query_str.sprintf ("CREATE TABLE %s "
                               "(id INTEGER PRIMARY KEY ASC AUTOINCREMENT NOT NULL, "
                               "comp_index INTEGER NOT NULL DEFAULT(0), "
                               "product_type INTEGER NOT NULL DEFAULT(0), "
                               "temperature INTEGER NOT NULL DEFAULT(0), "
                               "frequency INTEGER NOT NULL DEFAULT(0), "
                               "volume_litre INTEGER NOT NULL DEFAULT(0), "
                               "calibrator INTEGER NOT NULL DEFAULT(0), "
                               "calibrator_number INTEGER NOT NULL DEFAULT(0), "
                               "time_stamp  DATETIME NOT NULL DEFAULT(0000 - 00 - 00), "
                               "calib_delta_const INTEGER NOT NULL DEFAULT(0), "
                               "error_index INTEGER NOT NULL DEFAULT(0)) ;",
                               TABEL_STRAP_TABLE);
            QString  status;
            if (!execute_query (query_str, status))
            {
                if (verbose)
                    gprintf (FLRed"\n> SQLite [%s] Table {%s} ERR[%s] "FGray,
                             get_my_dbase_name().toLatin1().data(),
                             TABEL_STRAP_TABLE,
                             status.toLatin1().data());
                return false;
            }

To copy to clipboard, switch view to plain text mode 

and the qeury looks like this

/** ---------------------------------------------------------------------------
 * \brief  This method execute a query that required no return values
 *
 * \param  query_str     The sqlite query to be processed
 * \param  error_message On error the sqlite error message will
 *                       be reported in this variable
 * \return True on success of False on failure
 **/
BOOL sqlite_class::execute_query(const QString        & query_str,
                                 QString              & error_message)
{
    QSqlQuery mysql_query(lite_db);
 
    error_message.clear();
 
    // speed up things
    mysql_query.setForwardOnly(true);
 
    if(!mysql_query.exec(query_str))
    {
        QString status;
        status = mysql_query.lastError().text();
 
        if (verbose)
            gprintf (FLRed"\n  dBase [%s]"
                          "\n  Error : query_str[%s] "
                          "\n        : error    [%s] "FGray,
                     sqlite_file_name.toLatin1().data(),
                     query_str.toLatin1().data(),
                     status.toLatin1().data());
        error_message.sprintf ("  Error : dBase    [%s]\n"
                               "        : query_str[%s]\n"
                               "        : error    [%s] ",
                               sqlite_file_name.toLatin1().data(),
                               query_str.toLatin1().data(),
                               status.toLatin1().data());
        return false;
    }
    return true;
}

/** ---------------------------------------------------------------------------
 * \brief  This method execute a query that required no return values
 *
 * \param  query_str     The sqlite query to be processed
 * \param  error_message On error the sqlite error message will
 *                       be reported in this variable
 * \return True on success of False on failure
 **/
BOOL sqlite_class::execute_query(const QString        & query_str,
                                 QString              & error_message)
{
    QSqlQuery mysql_query(lite_db);

    error_message.clear();

    // speed up things
    mysql_query.setForwardOnly(true);

    if(!mysql_query.exec(query_str))
    {
        QString status;
        status = mysql_query.lastError().text();

        if (verbose)
            gprintf (FLRed"\n  dBase [%s]"
                          "\n  Error : query_str[%s] "
                          "\n        : error    [%s] "FGray,
                     sqlite_file_name.toLatin1().data(),
                     query_str.toLatin1().data(),
                     status.toLatin1().data());
        error_message.sprintf ("  Error : dBase    [%s]\n"
                               "        : query_str[%s]\n"
                               "        : error    [%s] ",
                               sqlite_file_name.toLatin1().data(),
                               query_str.toLatin1().data(),
                               status.toLatin1().data());
        return false;
    }
    return true;
}

To copy to clipboard, switch view to plain text mode 

And the actual insert like this

/** ---------------------------------------------------------------------------
 * @brief calibration_utils::write_strap_table_row
 * @param calib
 * @param sqlite
 */
void calibration_utils::write_strap_table_row(const CALIBRATION * calib,
                                              strap_table_class * sqlite)
{
    QTime        time;
    QString      sqlErrorStr,format = "d MMMM yyyy hh:mm", query_str;
    QDateTime    datetime;
 
    datetime.setMSecsSinceEpoch(calib->time_stamp);
 
     // INSERT INTO KOOS (PIET,BEN) VALUES ('1','2')
    query_str.sprintf ("INSERT INTO %s ("                                // 0
                       "comp_index,product_type,temperature,frequency,"  // 1,2,3,4
                       "volume_litre,calibrator,calibrator_number, "     // 5,6,7
                       "time_stamp, error_index)"                        // 8,9
                       " VALUES ('%d','%d','%d','%d',"
                       "         '%f',\"%s\",'%d',\"%s\",'%d') ;",
                       TABEL_STRAP_TABLE,                                // 0
                       calib->comp_index,                                // 1
                       calib->product_type,                              // 2
                       calib->temperature,                               // 3
                       calib->frequency,                                 // 4
                       calib->volume_litre,                              // 5
                       calib->calibrator,                                // 6
                       calib->calibrator_number,                         // 7
                       datetime.toString(format).toLatin1().data(),      // calib->time_stamp,  // 8
                       calib->error_index                                // 9
                       );
   time.start();
   if (!sqlite->execute_query(query_str,sqlErrorStr))
   {
       gprintf ("\n> insert_dbase_values ERROR ");
   }
   gprintf ("\n>> Query take %d ms", time.elapsed());
}

/** ---------------------------------------------------------------------------
 * @brief calibration_utils::write_strap_table_row
 * @param calib
 * @param sqlite
 */
void calibration_utils::write_strap_table_row(const CALIBRATION * calib,
                                              strap_table_class * sqlite)
{
    QTime        time;
    QString      sqlErrorStr,format = "d MMMM yyyy hh:mm", query_str;
    QDateTime    datetime;

    datetime.setMSecsSinceEpoch(calib->time_stamp);

     // INSERT INTO KOOS (PIET,BEN) VALUES ('1','2')
    query_str.sprintf ("INSERT INTO %s ("                                // 0
                       "comp_index,product_type,temperature,frequency,"  // 1,2,3,4
                       "volume_litre,calibrator,calibrator_number, "     // 5,6,7
                       "time_stamp, error_index)"                        // 8,9
                       " VALUES ('%d','%d','%d','%d',"
                       "         '%f',\"%s\",'%d',\"%s\",'%d') ;",
                       TABEL_STRAP_TABLE,                                // 0
                       calib->comp_index,                                // 1
                       calib->product_type,                              // 2
                       calib->temperature,                               // 3
                       calib->frequency,                                 // 4
                       calib->volume_litre,                              // 5
                       calib->calibrator,                                // 6
                       calib->calibrator_number,                         // 7
                       datetime.toString(format).toLatin1().data(),      // calib->time_stamp,  // 8
                       calib->error_index                                // 9
                       );
   time.start();
   if (!sqlite->execute_query(query_str,sqlErrorStr))
   {
       gprintf ("\n> insert_dbase_values ERROR ");
   }
   gprintf ("\n>> Query take %d ms", time.elapsed());
}

To copy to clipboard, switch view to plain text mode 

[jefftee]

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:

lite_db.transaction();
// do all of your inserts/updates/deletes
lite_db.commit(); // commit changes if successful or lite_db.rollback(); if you encountered an error, etc.

lite_db.transaction();
// do all of your inserts/updates/deletes
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.

[wysota]

Did you actually measure what is the bottleneck of your program?

[ChrisW67]

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