It turned out that the developer did not explicitly ask for a server cursor. Link 2 is a blog, which is a SQL Dev blog, it says this:
#Windows 10 sql server native client 10 driver
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. In the first link, it describes that server-side cursors are used by Native Client 10 only when the default options are changed: I have tinkered with the problem for a bit more and found these two links: Question2: Is there a more efficient way to just retrieve data in MFC with ODBC? We take that recordset, get all the data, and delete it. We don't care about recordset updates, deletes, etc or any of that nonsense. Maybe this is the wrong approach altogether, but I'm not sure how else to do this.Īll we want is to retrieve all data at once from a SELECT, then never talk the server again until the next query. 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?
![windows 10 sql server native client 10 windows 10 sql server native client 10](https://cleverstaff623.weebly.com/uploads/1/2/4/8/124883073/977084358.png)
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. This however only happens on queries with LEFT JOINS, table-valued functions, etc. 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). There are still long-running exec's to sp_cursorfetch() et al in SQL Profiler. I have tried using: ::SQLSetConnectAttr(m_db.m_hdbc, SQL_ATTR_ODBC_CURSORS, SQL_CUR_USE_ODBC, SQL_IS_INTEGER) īut this didn't help either. I think it is due to server-side cursors, but I didn't find a way to disable them. However, with the MSSQL Native Client, things slow down, because on every MoveNext(), the driver communicates with the server. The query is returned, and everything is lightning fast. Now, with the MySQL driver, everything runs as it should. R.Open(CRecordset::snapshot, L"SELECT a.something, b.sthelse FROM TableA AS a LEFT JOIN TableB AS b ON a.ID=b.Ref") Ĭrs.GetFieldValue(L"a.something", strData) The MySQL ODBC driver does not exhibit this nasty behavior. Now, when we use MSSQL (with the Native Client 10.0), retrieving records with SELECT is dramatically slow via slow links (VPN, for example). (In programming terms, read committed transaction isolation with row versioning is the same as Read-Committed transaction.)įor information about the differences between SQL Server Native Client and MDAC, see Updating an Application to SQL Server Native Client from MDAC.We have an application that uses ODBC via CDatabase/CRecordset in MFC (VS2010).
![windows 10 sql server native client 10 windows 10 sql server native client 10](https://s3.amazonaws.com/cdn.freshdesk.com/data/helpdesk/attachments/production/5130787387/original/FjuwMGzUphrzEy6Q02XB5_CiXkAl7C-ddw.png)
But if you do need to access those new features, such as the xml data type, you should use SQL Server Native Client.īoth SQL Server Native Client and MDAC support read committed transaction isolation using row versioning, but only SQL Server Native Client supports snapshot transaction isolation. If you have a mature application that does not need the new features of SQL Server, you can continue to use WDAC.
#Windows 10 sql server native client 10 windows
If you don't need access to the new features of SQL Server, you can continue to use Windows Data Access Components (WDAC).įor existing OLE DB and ODBC applications, the primary issue is whether you need to access the new features of SQL Server. If you are developing a COM-based application and need to access the new features introduced in SQL Server, you should use SQL Server Native Client. NET Framework Data Provider for SQL Server, which is part of the. When deciding whether to use SQL Server Native Client as the data access technology of your application, you should consider several factors.įor new applications, if you're using a managed programming language such as Microsoft Visual C# or Visual Basic, and you need to access the new features in SQL Server, you should use the. For a discussion of the different data-access technologies, see Data Access Technologies Road Map SQL Server Native Client is one technology that you can use to access data in a SQL Server database. Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)