[Solved] how to speed up. QSQLite INSERT query?

[shint]

i would like to know. how to speed up. QSQLite?

i'm saved project 100kb. (it takes 10 seconds.)

• click save btn
• drop table (first once)
• create table (first once)
• insert data <-- (too slow...)

i don't know why sql insert. too slow....
please. help me in this regards.

[js_dev]

Do you use a QTableModel or a QSqlQuery?

[shint]

O_O~~ hello. js_dev.
we use. QSqlQuery.

//code
@
QSqlQuery query(db);
query.prepare("DROP TABLE ");
query.exec()
query.prepare("CREATE TABLE ");
query.exec()
query.prepare("INSERT INTO ");
query.exec()
@

[js_dev]

In my experience QSqlQuery is much faster than QTableModel. So that should be fine.
But I gues without some code it is hard to say what the reason is.

Have you started a transaction before inserting data?

[shint]

i was test. two codes.
but. same speed.;;;;;

//not use prepare. addBindValue.
@
QString tmp, str;
for(int row = 0; row < nRowCount; row++)
{
QString qsQuery = qsInsertTable;
int nValueCount = 0;
for(int col = 0; col < nColumnCount; col++)
{
qApp->processEvents();
QModelIndex modelIndex = pModel->index(row, col);
QVariant v = pModel->data(modelIndex);
if(!v.isNull())
{
qsQuery += "'" + v.toString() + "',";
nValueCount++;

        //
        tmp.sprintf("%d %d ", row, col);
        str += tmp + v.toString() + "/ ";
    }
    else
    {
        qsQuery += "'',";
    }
}

if(nValueCount == 0)
{
    str += "\n";
    continue;
}

qsQuery.chop(1);
qsQuery += ")";
if (!query.exec(qsQuery))
{
    qCritical() << query.lastError();
}
str += "\n";

}
qDebug() << str;
@

//use prepare. addBindValue.
@
QString tmp, str;
for(int row = 0; row < nRowCount; row++)
{
query.prepare(qsInsertTable);
int nValueCount = 0;
for(int col = 0; col < nColumnCount; col++)
{
qApp->processEvents();
QModelIndex modelIndex = pModel->index(row, col);
QVariant v = pModel->data(modelIndex);
if(!v.isNull())
{
query.addBindValue(v.toString());
nValueCount++;

        //
        tmp.sprintf("%d %d ", row, col);
        str += tmp + v.toString() + "/ ";
    }
    else
    {
        query.addBindValue(QString(""));
    }
}

if(nValueCount == 0)
{
    str += "\n";
    continue;
}

if (!query.exec())
{
    qCritical() << query.lastError();
}
str += "\n";

}
@

[mabrand]

You should probably check this "faq":http://www.sqlite.org/faq.html#q19.

[shint]

thank you. mabrand.
i will see.

[js_dev]

I do it like described "here":http://doc.qt.nokia.com/4.7/qsqlquery.html#prepare.

This works fine and is fast enough for my software.

Don't forget the @db.transaction()@ and @db.commit()@ before and after your query.

[shint]

O_T js_dev.
thank you for being so caring.

i just do it!

[shint]

thank you. very much. js_dev.
and. with mabrand.

i success.
i applyed transaction() and commit().
and very fast write!.

have a nice day~~ O_O~~;;;;

[js_dev]

Great :-)

[shint]

O_O~~

[goetz]

[quote author="shint" date="1296545085"]
//use prepare. addBindValue.
@
QString tmp, str;
for(int row = 0; row < nRowCount; row++)
{
query.prepare(qsInsertTable);
int nValueCount = 0;
for(int col = 0; col < nColumnCount; col++)
{
@[/quote]

You should prepare your statement once (outside the for row statement) and use it multiple times. You actually do it the other way round: You create a single new prepared statement for each row you insert. This does not save you time, but cost you.

[js_dev]

[quote author="Volker" date="1296556882"]
[quote author="shint" date="1296545085"]
//use prepare. addBindValue.
@
QString tmp, str;
for(int row = 0; row < nRowCount; row++)
{
query.prepare(qsInsertTable);
int nValueCount = 0;
for(int col = 0; col < nColumnCount; col++)
{
@[/quote]

You should prepare your statement once (outside the for row statement) and use it multiple times. You actually do it the other way round: You create a single new prepared statement for each row you insert. This does not save you time, but cost you.[/quote]

Good point! I haven't seen this.

[shint]

O_O'''' oh! i understand.
Volker. good man. thank you.

to prepare() for a worry I will try to use.