2

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.

Improve this question
3
  • So your assertion is that parallelism helps improve the speed of your cursor, more so than not using a cursor in the first place, and parallel scrollable beats serial forward? Can you provide an example cursor (by way of an actual execution plan) that (a) even uses parallelism, and (b) performs better than a serial equivalent? How much better is it, really?
    – Aaron Bertrand
    Apr 5, 2016 at 14:56
  • 1
    And no, IIRC the only default cursor option you can change is global/local. Look for the word "default" here, but it is spelled out better in this ancient SQL Server 2000 doc. The optimization you're talking about sounds to me like trying to get better gas mileage by scraping bugs off the bumper for better aerodynamics, but leaving the big couch strapped to the roof.
    – Aaron Bertrand
    Apr 5, 2016 at 15:08
  • @AaronBertrand, 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 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 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.
    – 0x416e746f6e
    Apr 6, 2016 at 6:55

1 Answer 1

Reset to default
6

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.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.