PDA

View Full Version : Slow insert with Transaction?!



codeman
6th October 2010, 20:03
Hello friends,

in my app I have to insert many rows into a mssql table, but surprisingly it take many time to finish. I have to wait for example for 1000 records nearly 3 minutes, I think that is to much isn´t it.

my code for the insert block looks like this:


QFile myreadfile("inputdata.txt");
if (!myreadfile.open(QIODevice::ReadOnly | QIODevice::Text))
{
qDebug() << "Fehler beim Öffnen der Datei:" << "inputdata.txt";
}
else
{
QSqlDatabase db_mydatabase = QSqlDatabase::database("MSSQL_DB");
QString qstr_wholeFile = myreadfile.readAll();
QStringList qstrL_WholeFile = qstr_wholeFile.split("\n");
qDebug() <<"Transaction :" << db_mydatabase.transaction();
qDebug() << "Is DB Open ?: " << db_mydatabase.isOpen();

bool sql_ok = true;
qDebug() << "Start insert: " << QDateTime::currentDateTime().toString(Qt::ISODate) << endl;
QSqlQuery q_myquery(db_mydatabase);
q_myquery.prepare("INSERT INTO tbl_mytable ([col1],[col2],[col3],[col4],"
"[col5],[col6],"
"[col7],[col8],[col9],[col10],[col11],[col12],"
"[col13],[col14],[col15],[col16],[col17],"
"[col18],[col19],[col20],[col21],[col22],[col23],[col24],[col25],[col26],"
"[col27],[col28],[col29],[col30],[col31],[col32]) "
"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?,"
"?, ?, ?, ?, ?, ?, ?, ?, ?, ?,"
"?, ?, ?, ?, ?, ?, ?, ?, ?, ?,"
"?, ?)");

int i(0);
QStringList::const_iterator constIterator;
for (constIterator = qstrL_WholeFile.constBegin(); constIterator != qstrL_WholeFile.constEnd();++constIterator)
{
if(!(*constIterator).isEmpty())
{

QStringList myline = (*constIterator).split(";");
q_myquery.addBindValue(myline.at(0).toInt());
q_myquery.addBindValue(myline.at(1).toInt());
q_myquery.addBindValue(myline.at(2).toInt());
q_myquery.addBindValue(3);
q_myquery.addBindValue(myline.at(3).toInt());
q_myquery.addBindValue(myline.at(4).toInt());
q_myquery.addBindValue(0);
q_myquery.addBindValue(myline.at(5).toInt());
q_myquery.addBindValue(myline.at(6).toInt());
q_myquery.addBindValue(QDateTime::currentDateTime( ).toString(Qt::ISODate));
q_myquery.addBindValue(myline.at(7));
q_myquery.addBindValue(myline.at(8));
q_myquery.addBindValue(myline.at(9));
q_myquery.addBindValue(myline.at(10).toDouble());
q_myquery.addBindValue(myline.at(11));
q_myquery.addBindValue(myline.at(12));
q_myquery.addBindValue(myline.at(13).toInt());
q_myquery.addBindValue(myline.at(14));
q_myquery.addBindValue(myline.at(15));
q_myquery.addBindValue(myline.at(16));
q_myquery.addBindValue(myline.at(17));
q_myquery.addBindValue(myline.at(18));
q_myquery.addBindValue(myline.at(19));
q_myquery.addBindValue(myline.at(20));
q_myquery.addBindValue(myline.at(21));
q_myquery.addBindValue(myline.at(22));
q_myquery.addBindValue(myline.at(23));
q_myquery.addBindValue(0);
q_myquery.addBindValue(0);
q_myquery.addBindValue(0);
q_myquery.addBindValue(0);
q_myquery.addBindValue(0);
sql_ok = q_myquery.exec();

}

}
q_myquery.clear();
if(sql_ok)
{
sql_ok = db_mydatabase.commit();
}

if(!sql_ok)
{
qDebug() <<"Query lastError :"<< q_myquery.lastError();
qDebug() << "lastError().text()"<<q_myquery.lastError().text();
db_mydatabase.rollback();
}
}

Do I make some mistakes or why it take soo long for insert??

ChrisW67
6th October 2010, 23:46
Where is the delay? Is it in executing 1000 inserts and all the associated parameter binding, committing the transaction, or rolling back the transaction? Is the connection to the database local or across a network? Does temporarily switching to an Sqlite database change the speed? You can use the QTime::start() and QTime::elapsed() methods to time longer running events like these.

You can avoid some work in the loop by coding the constant column values (0, 3, and possibly the current time) into the query.

BTW: The way you are capturing the return from QSqlQuery::exec() you will only capture the result of the last SQL statement and commit/rollback on the basis of that. Are you sure that you are not executing 999 failing SQL statements?

Yakuza_King
19th October 2010, 15:44
I have a same problem with a SQLite-DB.
When I rollback or commit a transaction, the system keeps busy for 3-4 minutes.
The whole application is frozen at this time.
I'm using a local database.

I think the rollback or commit statement starts a new thread for it's execution. So the function will finish it's work, but before the function returns it waits for the database-thread to finish.

ChrisW67
20th October 2010, 00:43
Without more information or an example displaying the issue we cannot really help. If you have inserted or updated 100 million records it is going to take some time to commit or rollback no matter what.

saa7_go
20th October 2010, 11:38
I have a same problem with a SQLite-DB.
When I rollback or commit a transaction, the system keeps busy for 3-4 minutes.
The whole application is frozen at this time.
I'm using a local database.


Maybe, this SQLite FAQ (http://www.sqlite.org/faq.html#q19) explains your problem.