QSqlTableModel select() / submitAll() very slow

[mihaibu]

Hi guys,

I have a rather large table (over 3 million records) that has to be accessed remotely. It's part of an invoice and payment application.
I use QSqlTableModel as model. For editing the records, I use DataWidgetMapper, and I also have a QTableView elsewhere for viewing all the records for a customer.

The first issue: pModel->select() is extremely slow - if I load the table locally it freezes the GUI 20 seconds, but if I load the table remotely, it freezes the application for good.
The edit strategy is OnManualSubmit.

The second issue: when adding a new record I tried 2 ways:
a) pModel->insertRecord(-1, &record) followed by pModel->submitAll(), because the record must be viewed immediately. Those operations take some time when appealed locally and almost 30 seconds when accessed remotely;
b) QSqlQuery ->exec("Insert into table ... values (...)) followed by pModel->select() - this brings me to the first issue, the application is blocked when accessed remotely.

Also, I've tried to surround insertRecord() / submitAll(), respectively pQuery->exec() / pModel->select() by transaction() / commit(), with no improvements.

Is there any possible solution for those issues? The application must be very responsive, with no delays when adding or deleting a row.
I'm using Qt 4.8.4 and PostgreSQL 9.2.4.

Thank You in advance.

[wysota]

You have to transfer only those rows that you need and those that have changed. I don't think you need to view all 3M rows at once, so teaching your model to use canFetchMore() and fetchMore() can give a rather significant improvement. The same goes with submiting changes to the remote model. Relying on QSqlTableModel that uses a generic SQL driver is not a good idea as different SQL drivers have different capabilities and can take a rather inefficient code path.