qsqlite waaay to slow

[JohnToddSr]

I am writing a program and I'm reading in data from an xml string and loading that data into sqlite tables. I'm taking a HUGE performance hit when I do this. for example, to load about 1000 records into a table where each record has four fields takes well over 4-5 minutes.

Here is the code where i create the table:
bool DBManager::createControlsTable()
{
Logger* pLog = Logger::getInstance();

QSqlQuery query(db);

if(!query.exec("create table Controls( \
ToolName varchar(20) \
, ToolID varchar(20) \
, DoD8500 varchar(50) \
, NIST varchar(1024))"))
{
pLog->ERR("DBManager::createControls - Could not create DyneticsIAControls Table");
QSqlError err = query.lastError();
QString estr = err.text();
pLog->ERR("DBManager::createControls() - DB error", estr);
errorsFound = true;
return false;
}

return true;
}


This is the code that I write the insertion query for the table:
bool DBManager::storeDataToIAControlsTable(QString tName, QString tID, QString DoD, QString Nist)
{
Logger* pLog = Logger::getInstance();

QSqlQuery query(db);

query.prepare("INSERT INTO Controls(ToolName, ToolID, DoD8500, NIST)" "VALUES(:ToolName, :ToolID, oD8500, :NIST)");
query.bindValue(":ToolName", tName);
query.bindValue(":ToolID", tID);
query.bindValue("oD8500", DoD);
query.bindValue(":NIST", Nist);

if(!query.exec())
{
pLog->ERR("DBManager::storeDataToIAControlsTable - Could not populate IAControls Table");
QSqlError err = query.lastError();
QString estr = err.text();
pLog->ERR("DBManager::storeDataToIAControlsTable - DB error", estr);
errorsFound = true;
return false;
}

return true;
}

And here is where I call this function...
First I open the xml document, read it in, etc. Here's the meat of the code...

QDomNode n = root.firstChild();

QString tName;
QString tID;
QString DoD;
QString Nist;

while(!n.isNull())
{
QDomElement e = n.toElement();
if(!e.isNull())
{
QDomNode a = n.namedItem("ToolName");
QDomElement ae = a.toElement();
tName = ae.text();

a = n.namedItem("ToolID");
ae = a.toElement();
tID = ae.text();

a = n.namedItem("DoDSecurityControl");
ae = a.toElement();
DoD = ae.text();

a = n.namedItem("FederalSecurityControl");
ae = a.toElement();
Nist = ae.text();

}

if(!storeDataToIAControlsTable(tName, tID, DoD, Nist))
{
pLog->ERR("DBManager::readInIAControlData - Failed to populate IAControls Table");
errorsFound = true;
return false;
}
n = n.nextSibling();
}

return true;

I hope this have given you enough to understand the kinds of things I'm doing. I really need to figure out how to speed things up. I appreciate any help you can give me in advance.

Thanks

[caduel]

search the forums, there were posts on that before iirc.
The solution was to wrap all this inside a transaction, I think.

[JohnToddSr]

I've been looking through the posts and I can't find anything relating to my problem. I'm also not sure how to use transactions, (especially when using bindValue()). Any help would be appreciated. Thanks.

[caduel]

e.g.
http://www.qtcentre.org/forum/f-gene...lite-4180.html

in short:
wrap your insert calls into a transaction and see if that brings an improvement.

HTH

[mm78]

If I read your code correct ... For every row you insert you create a QSqlQuery object and prepare a query. That's not very efficient.

You should create only one QSqlQuery, prepare the query only once, and reuse it. Also, wrap everything up in a single transaction. That should speed up things considerably.

[Bogdan]

Hi !

I have something like:

QSqlQuery query;
 
     for (int i=0 ; i <= 10000  ;  i++)
      {
 
          query.prepare("INSERT INTO  table ( id, name )    
                                              VALUES ( :id, :name  )"     );
          query.bindValue(":id",         QStringList_row[i]          );
          query.bindValue(":name",    QStringList_row[i]          );
 
          query.exec();
 
        }

QSqlQuery query;

     for (int i=0 ; i <= 10000  ;  i++)
      {

          query.prepare("INSERT INTO  table ( id, name )    
                                              VALUES ( :id, :name  )"     );
          query.bindValue(":id",         QStringList_row[i]          );
          query.bindValue(":name",    QStringList_row[i]          );

          query.exec();

        }

To copy to clipboard, switch view to plain text mode 

inserting into a mysql db.
Could anyone show me how to use transactions in this case ? (if possible)

Thank you !

[ChrisW67]

Untested, but something like this:

QSqlQuery query;
 
// Prepare only once
query.prepare("INSERT INTO  table ( id, name )
                       VALUES ( :id, :name  )"     );
 
// Start a transaction on the database
query.driver()->beginTransaction();
 
// Do the bulk insert
     for (int i=0 ; i <= 10000  ;  i++)
      {
          query.bindValue(":id",         QStringList_row[i]          );
          query.bindValue(":name",    QStringList_row[i]          );
          query.exec();
        }
 
// Commit if all is well
query.driver()->commitTransaction();
// or roll back if not
query.driver()->rollbackTransaction();

QSqlQuery query;

// Prepare only once
query.prepare("INSERT INTO  table ( id, name )
                       VALUES ( :id, :name  )"     );

// Start a transaction on the database
query.driver()->beginTransaction();

// Do the bulk insert
     for (int i=0 ; i <= 10000  ;  i++)
      {
          query.bindValue(":id",         QStringList_row[i]          );
          query.bindValue(":name",    QStringList_row[i]          );
          query.exec();
        }

// Commit if all is well
query.driver()->commitTransaction();
// or roll back if not
query.driver()->rollbackTransaction();

To copy to clipboard, switch view to plain text mode 

[Bogdan]

Could you test that, I am getting an error..
passing `const QSqlDriver' as `this' argument of `virtual bool QSqlDriver::beginTransaction()' discards qualifiers

[ChrisW67]

I'm still not somewhere I can run a Qt compile. Try:

QSqlDatabase::database().transaction();

QSqlDatabase::database().transaction();

To copy to clipboard, switch view to plain text mode 

in place of line 8 and similar changes for 19 & 21 . You may also need to move the transaction() call before the prepare. Have a look under "Transactions" in the docs for QtSql.

[vladozar]

I have ran into a similar problem where I had to insert thousands of records at a time and with this post I have found a solution.

Below is a general idea of what I have done based on your code

QSqlQuery query;
 
// Start a transaction on the database
QSqlDatabase::database().transaction();
// Prepare only once
query.prepare("INSERT INTO  table ( id, name )
                       VALUES ( :id, :name  )" );
 
// Do the bulk insert
     for (int i=0 ; i <= 10000  ;  i++)
      {
          query.bindValue(":id", QStringList_row[i]);
          query.bindValue(":name", QStringList_row[i]);
          query.exec();
        }
 
// Commit if all is well
QSqlDatabase::database().commit();

QSqlQuery query;
 
// Start a transaction on the database
QSqlDatabase::database().transaction();
// Prepare only once
query.prepare("INSERT INTO  table ( id, name )
                       VALUES ( :id, :name  )" );
 
// Do the bulk insert
     for (int i=0 ; i <= 10000  ;  i++)
      {
          query.bindValue(":id", QStringList_row[i]);
          query.bindValue(":name", QStringList_row[i]);
          query.exec();
        }
 
// Commit if all is well
QSqlDatabase::database().commit();

To copy to clipboard, switch view to plain text mode 

Thanks for the post