Why QSqlDatabase db QOCI very slow when QSqlQuery is calling next() function ?

[Everton Fonseca]

Driver Oracle 12g

QSqlDatabase db = QSqlDatabase::addDatabase("QOCI");
db.setUserName("bomfuturo_dev");
db.setHostName("#########");
db.setPassword("#########");
db.setDatabaseName("AGMQAS");
db.setPort(1521);
db.setConnectOptions("OCI_ATTR_PREFETCH_ROWS=1000;OCI_ATTR_PREFETCH_MEMORY=1000");
db.open();

            QSqlQuery rs(db);
            rs.setForwardOnly(true);
            if(rs.exec(query)){

                while(rs.next()) -----> it's very slow 
                {
                   qDebug() <<"dado" <<  rs.value(0).toString();
                }
            }

[Everton Fonseca]

I've encountered someone else with the same problem I'm having
link: https://bugreports.qt.io/browse/QTBUG-27098

[SGaist]

Hi,

Did you try to use the fix provided in bug report ?

[Everton Fonseca]

@SGaist no

[SGaist]

Then it might be a good idea to do so.

[Everton Fonseca]

@SGaist ok

[Everton Fonseca]

@SGaist how can i do it?

[mrjj]

@Everton-Fonseca
HI
The bug comments says
"Resolution: This issue was resolved by set OCI_ATTR_PREFETCH_ROWS and/or OCI_ATTR_PREFETCH_MEMORY parameter with SqlDatabase::setConnectOptions() function."

so use
https://doc.qt.io/qt-5/qsqldatabase.html#setConnectOptions

and try with
setConnectOptions("OCI_ATTR_PREFETCH_ROWS;OCI_ATTR_PREFETCH_MEMORY"

[Everton Fonseca]

@mrjj I did it

[mrjj]

@Everton-Fonseca
and did it help ?

[Everton Fonseca]

@mrjj yet it isn't working

[Everton Fonseca]

@mrjj look at example db.setConnectOptions("OCI_ATTR_PREFETCH_ROWS=1000;OCI_ATTR_PREFETCH_MEMORY=1000");
it was before open database

[mrjj]

@Everton-Fonseca
ok. odd. the poster said it fixed it for him.
Other poster said ODBC worked better for him.