I have an application that is using ODBC to access a SQL Server 2012 database using SQLSetStmtAttr
to set the SQL_SOPT_SS_CURSOR_OPTIONS
option to SQL_CO_FFO
. This means that its reads from the DB are backed by fast forward-only cursor.
This is Ok in most of the cases, however sometimes the performance of the fast forward-only cursor is significantly lower that it would be with a "usual" static one (the one that we would get with default SQL_CO_OFF
).
Question: Is there a way to force SQL Server not to use fast-forward cursors?
Obviously, the proper way is to change the application, and that process in on the way, however it's going to take time, and meanwhile I am looking for temporary workarounds.
So far the only idea that I have is to use this: (from Fast Forward-only Cursors)
Implicit Conversion of Fast Forward-only Cursors
Fast forward-only cursors are implicitly converted to other cursor types when:
- If the SELECT statement joins one or more tables with trigger tables (INSERTED/DELETED), the cursor is converted to a static cursor
However, that seems a bit too ugly even for a temporary workaround. Are there maybe better approaches?
Side note: Main reason why fast forward-only cursors under-perform in this case is that they do not support parallelism.
For non fast forward sys.dm_exec_cursors gives me:
API | Snapshot | Read Only | Global (0)
while fast forward option is:
API | Fast_Forward | Read Only | Global (0)
The snapshot option is 5x times faster than the fast forward one. If I look at the query plans for both of them, they are not so much different except for DegreeOfParallelism. The snapshot is 16 while fast forward is 0. The problem is that we are reading from the views (that we do not have much control over), and these views are sub-optimal in their design.
sys.dm_exec_cursors
gives meAPI | Snapshot | Read Only | Global (0)
while fast forward option isAPI | Fast_Forward | Read Only | Global (0)
. The snapshot option is 5x times faster that the fast forward one. And if I look at the query plans for both of them then they are not so much different except forDegreeOfParallelism
. The snapshot is 16 while fast forward is 0. The problem is that we are reading from the views (that we do not have much control over), and these views are sub-optimal in their design.