JohnToddSr
17th April 2009, 17:48
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, :DoD8500, :NIST)");
query.bindValue(":ToolName", tName);
query.bindValue(":ToolID", tID);
query.bindValue(":DoD8500", 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
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, :DoD8500, :NIST)");
query.bindValue(":ToolName", tName);
query.bindValue(":ToolID", tID);
query.bindValue(":DoD8500", 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