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??
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??