Speed with large datasets

[JoanneC]

Hi, we’re using QxORM to retrieve data from our database and populate models, and are having a problem with the time it takes to retrieve data into the model.

To query with a select statement and 4 relationships (one of which crosses 3 tables) returning 2920 rows, the query takes ~7.362 seconds. The same query takes 0.046 seconds to execute in MySQL. Using the fetchByQuery method (so without qxModelView) gives me similar results to using the qxModelView so the speed problem seems to be in populating the data classes.

For a qxFetchAll_ (3894 records) on just one table with no joins using qxModelView classes, the ‘query’ takes ~4.229 seconds, so the relationships don’t seem to be the problem.

As a comparison, I used a QSqlQueryModel with the same query (select and joins), and that took 0.134 seconds to get the data and populate the view.

Whilst I wouldn’t expect an ORM to be as fast as directly querying the database, the length of time taken seems to be excessive. Is this what you would expect, and/or is there anything we may have missed when generating the libraries which would speed things up? Obviously we can use threading and lazy loading to hide some of the load times from the user, but this won’t cover all the waiting times!
Do you have any suggestions?

Thanks

[qxorm]

Hello,

About performance : there is a big difference between debug mode and release mode.
Did you build QxOrm library + your own project in release mode to get your time results ?

[JoanneC]

Hi,
Thanks for the reply. Release mode did make a big difference as you suggested, but is still taking around 2.8 seconds.Is there anything else that would improve this, or is this sort of time to be expected?
Thanks

[qxorm]

You could try to disable all traces and checks, something like this :

Code: Select all

qx::QxSqlDatabase::getSingleton()->setTraceSqlQuery(false);
qx::QxSqlDatabase::getSingleton()->setTraceSqlRecord(false);
qx::QxSqlDatabase::getSingleton()->setTraceSqlBoundValues(false);
qx::QxSqlDatabase::getSingleton()->setTraceSqlBoundValuesOnError(false);
qx::QxSqlDatabase::getSingleton()->setVerifyOffsetRelation(false);

You could also try to use another container : by default, QxOrm library uses qx::QxCollection<Key, Value>.
For performance reason, you could try for example : QList<T> (you loose the primary key info in the container, but QList should be faster).

You can test it like this :

Code: Select all

QList<my_class> lst;
qx::dao::fetch_all(lst);