SQL very slow
-
Hi,
last two days I spend a lot of time on finding the right solution for my problem. Everything I did was unusable for some reason in the end.
I think it will be better to be specific about what I want to do. I'm working on a music player and I want to have a library (database). I already made a first version in PySide (Python) which works well. But now I'm converting it to C++/QML. The database has about 30 columns and possibly ten thousands of rows. I use TagLib to scan the media files and then I want to write the information to my database.
As I'm not really familiar with C++, I want to have as much as possible in QML. My first idea was to call a slot in QML and pass all the tags to it. This works fine but unfortunately it freezes the QML GUI for quite a long time, because I can not run the query in a background thread. QML simply doesn't allows it, AFAIK.
So I tried it in C++. I create a QList<QStringList>. Each item of the QList is a QStringList containing information about one media file (genre, artist, album, title etc.) Then I run in a background thread following code:
@bool Database::storeLibrary() {
QSqlQuery query;
QString queryString;
for (int i=0; i<mediaTagsList.size(); i++) {
queryString = QString("INSERT INTO MainLibrary VALUES ('");
for (int j=0; j<mediaTagsList[0].size(); j++) {
if (j != 0)
queryString += "', '";
queryString += mediaTagsList[i][j].replace("'","''");
}
queryString += "')";
query.exec(queryString);
}
}
@Unfortunately this code is extremely slow. It takes a whole minute to store only 500 records. The code is very similar to what I am using in Python and it is fast. What am I doing wrong? I see a high activity of HDD during the operation. Maybe I should keep the db in memory and then store it when everything is done.
Could you please advise me what am I doing wrong? What would be the best way to scan media files and store them to database?
-
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.
-
@ 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;")
@ -
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 :)
