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



  • 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.



  • Do you use a QTableModel or a QSqlQuery?



  • 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()
    @



  • 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?



  • 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&#40;qsQuery&#41;)
    {
        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&#40;&#41;)
    {
        qCritical() << query.lastError();
    }
    str += "\n";
    

    }
    @



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



  • thank you. mabrand.
    i will see.



  • 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.



  • O_T js_dev.
    thank you for being so caring.

    i just do it!



  • 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~~;;;;



  • Great :-)



  • O_O~~



  • [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 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.



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

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


Log in to reply
 

Looks like your connection to Qt Forum was lost, please wait while we try to reconnect.