SQL Native Client 10 Performance miserable (due to server-side cursors)
- by namezero
we have an application that uses ODBC via CDatabase/CRecordset in MFC (VS2010).
We have two backends implemented. MSSQL and MySQL.
Now, when we use MSSQL (with the Native Client 10.0), retrieving records with SELECT is dramatically slow via slow links (VPN, for example). The MySQL ODBC driver does not exhibit this nasty behavior.
For example:
CRecordset r(&m_db);
r.Open(CRecordset::snapshot, L"SELECT a.something, b.sthelse FROM TableA AS a LEFT JOIN TableB AS b ON a.ID=b.Ref");
r.MoveFirst();
while(!r.IsEOF())
{
// Retrieve
CString strData;
crs.GetFieldValue(L"a.something", strData);
crs.MoveNext();
}
Now, with the MySQL driver, everything runs as it should. The query is returned, and everything is lightning fast.
However, with the MSSQL Native Client, things slow down, because on every MoveNext(), the driver communicates with the server.
I think it is due to server-side cursors, but I didn't find a way to disable them. I have tried using:
::SQLSetConnectAttr(m_db.m_hdbc, SQL_ATTR_ODBC_CURSORS, SQL_CUR_USE_ODBC, SQL_IS_INTEGER);
But this didn't help either. There are still long-running exec's to sp_cursorfetch() et al in SQL Profiler.
I have also tried a small reference project with SQLAPI and bulk fetch, but that hangs in FetchNext() for a long time, too (even if there is only one record in the resultset).
This however only happens on queries with LEFT JOINS, table-valued functions, etc.
Note that the query doesn't take that long - executing the same SQL via SQL Studio over the same connection returns in a reasonable time.
Question1: Is is possible to somehow get the native client to "cache" all results locally use local cursors in a similar fashion as the MySQL driver seems to do it?
Maybe this is the wrong approach altogether, but I'm not sure how else to do this.
All we want is to retrieve all data at once from a SELECT, then never talk the server again until the next query.
We don't care about recordset updates, deletes, etc or any of that nonsense. We only want to retrieve data.
We take that recordset, get all the data, and delete it.
Question2: Is there a more efficient way to just retrieve data in MFC with ODBC?
