SQL Native Client 10 Performance miserable (due to server-side cursors)

Question

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?

Answer 1

I have tinkered with the problem for a bit more and found these two links:

MSDN Link

MSDN Blog

In the first link, it describes that server-side cursors are used by Native Client 10 only when the default options are changed:

When these options are set to their defaults at the time an SQL statement is executed, the SQL Server Native Client ODBC driver does not use a server cursor to implement the result set; instead, it uses a default result set.

Link 2 is a blog, which is a SQL Dev blog, it says this:

It turned out that the developer did not explicitly ask for a server cursor. But when he did block fetches, as a side effect, the SQL Server ODBC driver asked for a server cursor...that is unexpected!

Yes, certainly that is unexpected...

How can I do block fetches over a Default Result Set (fire hose cursor) instead of a server cursor?

Now, the implementation of the solution is this:

Instead of:

// crs is a CRecordSet
crs.Open(CRecordset::snapshot, L"SELECT something...");

Do this:

// crs is a CRecordSet
crs.Open(CRecordset::forwardOnly, L"SELECT something...");

This simple change will not trigger the creation of a server-side cursor, and mimics the behavior of the MySQL driver.

The disadvantage is that now you cannot retrieve the row count via the (Microsoft recommended) way:

while(crs.MoveNext()) nCount++;

Which is a bad idea anyway. Also, a ::SQLGetRowCount() will no longer work all of the time.

I have solved that issue like this (This should work on any ANSI-Compatible SQL source):

//strQuery is the random query passed to CountRows()
std::wstringstream ssQuery;
ssQuery << L"SELECT COUNT(*) AS Count FROM (" << strQuery << L") AS t";
CRecordset crs(&m_Database);
crs.Open(CRecordset::forwardOnly, ssQuery.str().c_str());
// Now retrieve the only "Count" field from the recordset.

I hope this helps someone else in the future.