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();

[Guest]

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?