Thanks again for all the replies here; circling back to mark this as solved.
I ended up implementing a custom QAbstractTableModel based on the ModifiedRow class in the QSqlTableModel source code.
The local data is populated via a QSqlQuery with setForwardOnly set to true.
void CachedSqlTableModel::select()
{
if(m_select.isEmpty() || m_tableName.isEmpty()){
qDebug() << "Invalid select statement";
return;
}
//Initialize query
QSqlQuery query;
query.setForwardOnly(true);
query.prepare(selectStatement());
query.exec();
if(query.isActive()){
beginResetModel();
//Reset data structure
m_cache.clear();
//Populate header data
m_record = query.record();
//Populate table data
while(query.next()){
m_cache.push_back(CachedRow(CachedRow::Update, query.record()));
}
endResetModel();
} else {
m_error = query.lastError();
}
All of the related cached database operations are executed in the exact same way as the code in the QSqlTableModel database handlers.
virtual bool updateRowInTable(int row, const QSqlRecord &values);
virtual bool insertRowIntoTable(const QSqlRecord &values);
virtual bool deleteRowFromTable(int row);
A big thank you to @Kent-Dorfman for the additional insights here. I implemented all of the suggestions above (stored procedures, transactions, indexing, limiting datasets, etc.) and there is a noticeable speed difference on all fronts.
