Sql-server – Is it possible to disable use of fast forward-only cursor on server side

cursorssql serversql-server-2012

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.

Best Answer

No, it is not possible to disable fast forward cursors on the server side, as far as I know.

If your application requests a fast forward cursor, that is what it will get, at least in 'modern' versions of SQL Server (2005+). From Using Implicit Cursor Conversions:

Fast forward cursors are never converted.

I have long since forgotten what might or might not have worked on SQL Server 2000 and earlier in this scenario. You might find one of the implicit conversion cases in the document you reference can be made to work for you temporarily.

Back to the modern world. The fast forward cursor may compile to a static-like or dynamic-like plan, but the restriction to only generate a non-parallel plan will remain in either case.

There is generally no single class of cursor or set of options that will perform acceptably well in all situations. For best performance, the type of cursor and the options set need to be evaluated for each case.

Related Question