SQLIte database, How do I improve insert statement speed?

hungchuviet

I have 500000 records.
How to insert them to sqlite databas with less time?
Thanks you!

lyuts

Is that going to be a single insert session? Or do you want it to handle this number of inserts per some period of time?

Felix

what do you mean with less time? can you provide your code that we can perhabs propose some improvments?

But generally you should do it in one transaction, that saves time for commiting and writing the data to disk.

harryF

Use prepared queries, see "Qt docs":http://doc.qt.nokia.com/qt-maemo-4.7/qsqlquery.html for details:

 QSqlQuery query;
 query.prepare("INSERT INTO person (id, forename, surname) "
               "VALUES (?, ?, ?)");
 query.addBindValue(1001);
 query.addBindValue("Bart");
 query.addBindValue("Simpson");
 query.exec(&#41;;

hi harry, can you convert it to a link

hungchuviet

Yes I do.
I've put all insert statments in one transaction and use parameters query, but it is slow.
@
db.exec("BEGIN TRANSACTION;");
qr.prepare("insert into test (f1,f2) values (:f1,:f2)");
for (int i=0;i<500000;i++)
{
qr.bindValue(":f1",QString:number(i));
qr.bindValue(":f2",QString:number(i+1));
qr.exec();
}
db.exec("END TRANSACTION;");
db.close();
@

alexander

hungchuviet
you can try to change some PRAGMA statement: "http://www.sqlite.org/pragma.html":http://www.sqlite.org/pragma.html

harryF

The "BEGIN TRANSACTION" and "END TRANSACTION" blocks look strange - first, you don't check the return value, and second, you shouldn't have semicolons in SQL statements (you only need semicolons when using the sqlite shell tool).

Try using db.transaction() and db.commit() instead.

Felix

can you descripe what you mean with "less time"? how long does your code take?