SQL 10 Native Client Performance is unbearable (due to server cursors)

Question

We have an application that uses ODBC through CDatabase / CRecordset in MFC (VS2010). We have two backends. MSSQL and MySQL.

Now that we use MSSQL (with Native Client 10.0), retrieving records using SELECT is significantly slower using slow links (like VPN). The MySQL ODBC driver does not exhibit this unpleasant 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 works as it should. The request is returned, and everything is lightning fast. However, with the help of the MSSQL native client, the situation slows down, since on each MoveNext () the driver interacts with the server.

I think this is due to server cursors, but I have not found a way to disable them. I tried using:

 ::SQLSetConnectAttr(m_db.m_hdbc, SQL_ATTR_ODBC_CURSORS, SQL_CUR_USE_ODBC, SQL_IS_INTEGER); 

But that didn't help either. SQL Profiler still has a lengthy exec for sp_cursorfetch () and others. I also tried a small help project with SQLAPI and bulk sampling, but it also freezes in FetchNext () for a long time (even if there is only one entry in the result set). However, this only happens with queries with LEFT JOINS, table functions, etc. Please note that the query does not require much time - the execution of the same SQL through SQL Studio via the same connection returns in a reasonable amount of time.

Question1: Is it possible to somehow make the local client localize the local cursor in "cache" locally just as does the MySQL driver?

This may not be the right approach, but I'm not sure how to do it.

All we want is to immediately extract all the data from SELECT, and then never talk to the server until the next query. We do not care about record set updates, deletion, etc. Or any of these nonsense. We only want to get the data. We take this set of records, get all the data and delete it.

Question2: Is there a more efficient way to just get data in MFC using ODBC?

Answer 1

I worked a bit with the problem and found these two links:

MSDN Link

MSDN Blog

The first link describes that server cursors are used by Native Client 10 only when changing the default settings:

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

Link 2 is a blog that is a SQL Dev blog, it says the following:

It turned out that the developer does not explicitly request the server cursor. But when it blocked selections as a side effect, the SQL Server ODBC driver requested the server cursor ... this is unexpected!

Yes, of course, this is unexpected ...

How to make block samples by default job set (fire hose pointer) instead of server cursor?

Now the implementation of the solution is as follows:

Instead:

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

Do it:

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

This simple change will not create a server cursor and mimics the behavior of the MySQL driver.

The downside is that now you cannot get the number of rows using (Microsoft recommended):

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

In any case, this is a bad idea. In addition, a :: SQLGetRowCount () will no longer work all the time.

I solved this problem 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.