MySql model/view vs. ODBC model/view speed differences

[ce_nort]

Scenario:
We are going to be moving from retail system using a MySql Database to a new system that uses SQL Server. I am working on rewriting a very simple program that puts the results of a simple SQL select statement into a QSqlQueryModel, which is then used as a source model for a QSortFilterProxyModel (to allow the user to sort on columns) and displayed in a QTableView. With MySql the data is returned and displayed basically instantaneously, while the same amount of data (100 rows by ~20 columns) using ODBC takes about 10 seconds to load into the QTableView. From what I can tell, the database returns results in only about a half a second longer, and it's the model(s) that is extremely slow. If I don't use the QSortFilterProxyModel, the data shows up in the table very quickly, but there's a 5 second or so lockup before I can scroll around the table or click on anything. Also, calling resizeColumnsToContents() on the tableview returns the lag of data appearance back to about 10 seconds. It should also be noted that I do not believe the SQL query itself is the issue here, because when I call the exact same query in Microsoft SQL Server Management Studio it returns instant results.

Questions:
1. Why the difference in speed? I know that the QODBC driver doesn't return a query size, is this the root of the problem?
2. If this is an inherent problem, is there a way around it? I know that people recommend setting setForwardOnly() on the query to true, but this is not allowed with a QSqlQueryModel and I didn't find that it dramatically increased speeds anyway - it is really such a small amount of data.

Of Note:
I didn't include any code because it didn't seem entirely necessary, but please let me know if it would help clarify anything and I can add it.

[ChrisW67]

Where is the extra time spent? In making the connection in the first place, executing the query, retrieving the result of the query, or somewhere else?

[ce_nort]

Where is the extra time spent? In making the connection in the first place, executing the query, retrieving the result of the query, or somewhere else?

As far as I can tell, the extra time is entirely after the results of the query are received. The results are returned within a normal time frame, but any manipulation of the model or view (e.g., filtering, sorting, resizing columns to contents) takes ages. I ended up giving up on using a QSqlTableModel and QTableView approach and simply went to using a QSqlQuery and a QTableWidget. It's about 1 second slower with SQL Server than it was with MySql, but that's manageable for me. In all of the other questions and answers I found related to my situation, everyone just ended up doing this.

[ghorwin]

I can confirm the observation - also moving data from SQL Server DB via ODBC to Postgres Database.

The following code executes fairly quickly:

QSqlDatabase odbcsql = QSqlDatabase::addDatabase("QODBC", "odbcsql");
 
...
 
QSqlDatabase odbcsql = QSqlDatabase::database("odbcsql");
 
// lesen
QSqlTableModel tabModelSource(nullptr, odbcsql);
tabModelSource.setTable(sTableSource);
if (!tabModelSource.select()) {
	qCritical() << tabModelSource.lastError();
	return;
}
 
int r = tabModelSource.rowCount();
while (tabModelSource.canFetchMore()) {
	tabModelSource.fetchMore();
}
qDebug() << "Total number of rows =" << r;

QSqlDatabase odbcsql = QSqlDatabase::addDatabase("QODBC", "odbcsql");

...

QSqlDatabase odbcsql = QSqlDatabase::database("odbcsql");

// lesen
QSqlTableModel tabModelSource(nullptr, odbcsql);
tabModelSource.setTable(sTableSource);
if (!tabModelSource.select()) {
	qCritical() << tabModelSource.lastError();
	return;
}

int r = tabModelSource.rowCount();
while (tabModelSource.canFetchMore()) {
	tabModelSource.fetchMore();
}
qDebug() << "Total number of rows =" << r;

To copy to clipboard, switch view to plain text mode 

This code takes less than a second for 160000 entries - way too many records to transfer in 1 seconds over my slow internet connection. So apparently data is not transferred and cached in the calls to fetchMore(), yet.

Now, when I start to access the data:

qDebug() << "Caching table data";
std::vector<QList<QVariant> > data;
for (int i=0; i<r; ++i) {
	QSqlRecord rec(tabModelSource.record(i));  // <--- very slow access
	QList<QVariant> vals;
	for (int j=0; j<rec.count(); ++j)
		vals.append(rec.value(j));
	data.push_back(vals);
}

qDebug() << "Caching table data";
std::vector<QList<QVariant> > data;
for (int i=0; i<r; ++i) {
	QSqlRecord rec(tabModelSource.record(i));  // <--- very slow access
	QList<QVariant> vals;
	for (int j=0; j<rec.count(); ++j)
		vals.append(rec.value(j));
	data.push_back(vals);
}

To copy to clipboard, switch view to plain text mode 

Every time I access a record, the data is crawling over my internet connection at about 30...56kB/s (and my network really isn't than slow!)

Conclusion:

1. ODBC-driver doesn't do any caching
2. ODBC-driver is basically unusable for real-life data in Qt model/view classes

Questions:

Does anyone know how to tell the ODBC-driver to retrieve the data in larger chunks, instead value-by-value?

-Andreas