SQL very slow
-
Have you tried using QSqlQuery::prepare and QSqlQuery::addBindValue ?
I don't know enough about your data but perhaps you could try something like this:
@
QSqlQuery query;
query.prepare("INSERT INTO table1 VALUES (?,?);for (i =0; i<rows; i++)
{
variable1=column1[i];
query.addBindValue(variable1);
variable2=column2[i];
query.addBindValue( variable2);
}
q.execBatch();
@I myself haven't tried a batch insert on Qt yet. I just read it from other posts and I did something similar in Java.
-
If you are using Sqlite (you don't say) then you want to do all the inserts in a single transaction:
@
db.transaction();
// one prepare() of the query
// many thousands of inserts using bindValue() in your loop
if (no errors)
db.commit();
else
db.rollback();
@
http://sqlite.org/faq.html#q19You should definitely prepare() and bindValue(); it avoids having to think about escape illegal/dangerous characters.
-
puterk> I tried it with the same result. :-(
ChrisW67> Yes, I'm using SQLite. This is exactly what I wanted to do, but didn't know how.
I have one more question. In JavaScript (QML) I coud do
@var dataStr = "INSERT INTO myTable VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
db.transaction(function(tx) {
tx.executeSql(dataStr, dataList);
})
@Is it possible to do something similar? All documentation on bindValue I've seen looks like this:
@ QSqlQuery query;
query.prepare("INSERT INTO person (id, forename, surname) "
"VALUES (:id, :forename, :surname)");
query.bindValue(":id", 1001);
query.bindValue(":forename", "Bart");
query.bindValue(":surname", "Simpson");
query.exec();
@Is there any possibility to bind a complete QStringList or QVariantList? Thank you.
-
Maybe the solution is very easy. You have to use the transactions in this mode:
begin
insert into
insert into
insert into
...
commit -
mrdebug> Could you be more specific, please? I'm quite lost between the commands of SQLite and Qt. :-( I don't need anything complicated, just to fill one table as fast as possible.
-
@ query.exec("begin exclusive transaction;")
query.prepare("insert into MyTable (Name, Surname) values (:Name, :Surname)");
query.bindValue(":Name", "Ciao");
query.bindValue(":Surname", "Ciao Ciao");
query.exec()
query.prepare("insert into MyTable (Name, Surname) values (:Name, :Surname)");
query.bindValue(":Name", "Ciao");
query.bindValue(":Surname", "Ciao Ciao");
query.exec()
query.prepare("insert into MyTable (Name, Surname) values (:Name, :Surname)");
query.bindValue(":Name", "Ciao");
query.bindValue(":Surname", "Ciao Ciao");
query.exec()
query.prepare("insert into MyTable (Name, Surname) values (:Name, :Surname)");
query.bindValue(":Name", "Ciao");
query.bindValue(":Surname", "Ciao Ciao");
...
query.exec("commit;")
@ -
mrdebug> Thanks a lot, it works perfectly.
-
I'm just wondering. Shouldn't this have the same effect (provided that the database driver supports it)?
@
QSqlQuery query;
db.transaction();
query.prepare("INSERT INTO table1 VALUES (?,?)");for (i =0; i<rows; i++) { variable1=column1[i]; query.addBindValue(variable1); variable2=column2[i]; query.addBindValue( variable2); } if ( q.execBatch()) db.commit(); else db.rollback();
@
-
@
var dataStr = "INSERT INTO myTable VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
db.transaction(function(tx) {
tx.executeSql(dataStr, dataList);
})
@
becomes something like (untested):
@
bool massInsert(const QList<QStringList> &valueSets) {
bool success(false);
QSqlDatabase db = QSqlDatabase::database();
QSqlQuery qry(db);
if (qry.prepare("INSERT INTO blah VALUES(?, ?, ?, ... )")) {
db.transaction();
foreach (const QStringList &valueSet, valueSets) {
foreach(const QString &value, valueSet)
qry.addBindValue(value);
if (!qry.exec())
break;
}
if (qry.lasterror().type() == QSqlError::NoError)
success = db.commit();
else {
// log the error
db.rollback();
}
}
return success;
}
@
You could use QVariantList in place of QStringList. -
if you use Mysql change engine from InoDB to ISAM :)
-
ChrisW67> Thanks a lot, I did it like this. Of course some of the values are strings, some are float and some are integers. I pass them all as string. Does it make sense to use QVariantList instead and then convert the values to their types?