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;
}

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;
}

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());
}

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


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