Accelerating Oracle Database Interface in Qt (QOCI)

Question

I have a project that includes integrating an Oracle database into a Qt desktop application. Since Qt has a really simple interface for interacting with the database, I compiled the QOCI driver to support Oracle connections.

Everything works fine, but request processing time is incredibly slow. How to increase productivity?

My code for handling an arbitrary request looks like this:

QList< QList<QString> > DBWrapper::executeQuery(QString const& queryString)
{
    QList< QList<QString> > results;
    if (_db.isOpen()) {
        QSqlQuery qry(queryString);
        if(qry.exec()) {
            while(qry.next()) {
                QList<QString> row;
                results.append(row);
                for(int i = 0; i < qry.record().count(); ++i) {
                    results.last().append(qry.value(i).toString());
                }
            }
        } else {
            qDebug() << queryString;
            qDebug() << "FAILURE: cannot handle query.";
            qDebug() << "  > " << qry.lastError();
        }
    } else {
        qDebug() << "Error Opening Database = " << _db.lastError();
    }

    return results;
}

Answer 1

There were some resources describing the same problem with other DBMSs in Qt. All of them offered to add qry.setForwardOnly(true)and qry.prepare(queryString);to configure the query.

, , Qt, . , QSql.

//...
QSqlQuery qry(queryString);
qry.setForwardOnly(true);
qry.prepare(queryString);

QElapsedTimer timer;
timer.start();
if(qry.exec()) {
    qDebug() << "time to execute query: " << timer.elapsed() << "ms";
    timer.start();
    while(qry.next()) {
         // don't do anything here
    }
    qDebug() << "time to iterate through query results: " << timer.elapsed() << "ms";
}

, 50 , 3000 , 100 SELECT.

, . , , . OCI OCI_ATTR_PREFETCH_ROWS, 1. .

_db = QSqlDatabase::addDatabase("QOCI");
_db.setHostName(host);
_db.setDatabaseName(dbName);
_db.setPort(port);
_db.setUserName(user);
_db.setPassword(pwd);

_db.setConnectOptions("OCI_ATTR_PREFETCH_ROWS=1000");

_db.open();

, - , , , select Qt. , OCI_ATTR_PREFETCH_ROWS. qry.value(...) (QSqlRecord), , OCI_ATTR_PREFETCH_ROWS = 1 , qry.next() - , . , .

, -, , .