Cursor Types

One of the changes in Microsoft Dynamics NAV version 5, was to change from primarily making use of Fast-Forward cursor types to Dynamic cursors. The same change was implemented in version 4 from build 26410, which is Version 4, SP3 Update6(940718) + KB950920.

The change of cursor type can also mean a change in behaviour. With Dynamic cursors, SQL Server more often optimises for the ORDER BY - part of a SQL query than is the case with Fast Forward cursors. This is because a result set based on a dynamic cursor has to include new rows. IF SQL Server were to choose an index that fits the WHERE clause then it would have to sort all rows according to the ORDER BY before returning the first row to the client and that by definition is a STATIC result-set.


Take this query as an example:

SELECT * FROM "Demo Database NAV (6-0)"."dbo"."CRONUS International Ltd_$Item Ledger Entry"


undefined (("Document No_"=

ORDER BY "Item No_","Posting Date","Entry No_"

With Fast-Forward cursors, in this example SQL Server is likely to try to optimise for the WHERE clause, which is "Document No.". But with the ORDER BY clause specifying a different sorting, SQL Server may then chose a clustered index scan instead.

With Dynamic cursors, SQL Server is more likely to optimise for the ORDER BY clause, which is a valid and existing index. So in this exampe SQL server would chose an index scan on this index. You can see this by running the query from SQL Server Management Studio like this:


declare @p1 int set @p1=-1

--declare @p3 int set @p3=16+4096+8192 -- Fast Forward

declare @p3 int set @p3=2+4096+8192 -- Dynamic

declare @p4 int set @p4=1

declare @p5 int set @p5=49

--declare @p5 int set @p5=15 – FAST 15

exec sp_cursoropen @p1 output,N'

SELECT * FROM "Demo Database NAV (6-0)"."dbo"."CRONUS International Ltd_$Item Ledger Entry"


WHERE (("Document No_"=@P1))

ORDER BY "Item No_","Posting Date","Entry No_"



output,@p4 output,@p5 output,N'@P1 varchar(20)',


select @p1, @p3, @p4, @p5

exec sp_cursorclose @p1

You can enable / disable the 2nd and 3rd line to switch between Fast-Forward or Dynamic cursor, and see the result in the query plan that gets generated.


How does this affect NAV?
The change in behaviour can mean that certain queries that ran without problems may be slow, after a customer upgrades the platform to the builds mentioned above. In cases

that we have seen, the problem has been limited to one or very few specific queries, typically on filtered forms, that were slow after upgrading the platform. Use the query from here:

Simple query to check the recent performance history II - now including Query Plan information

to help identifying which queries to trouleshoot. Note that the query shows the Cursor Type in the rightmost column. Then look at whether SQL Server has an index to match the ORDER BY clause.

Also, be extra careful using the SQLIndex-property on keys. By setting this property, the ORDER BY-clause may not match a SQL index anymore, and a Dynamic cursor will have

to scan the table.


Why this change?
Internal performance tests show that overall, Dynamic cursors give better performance and fewer blocks. So while the change may cause unforeseen changes in behaviour when a customer upgrades, overall we have seen better performance with Dynamic cursors.


Lars Lohndorf-Larsen

Escalation Engineer

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

Comments (3)

  1. says:

    Hi Lars,

    so in effect, this means we should be using SETCURRENTKEY again in our NAV code to "help" SQL choosing the best key? Before we were thinking that a SETCURRENTKEY was just adding overhead to the query as SQL chose its own key anyway, but then was forced to do a sort of the result as well.

    Btw, do you know if the Index hinting has been turned off with the latest builds for NAV5SP1? I have not seen any notes about that, but it does not seem NAV adds the Index property to the query when I look at it in the profiler.



  2. stryk says:


    Actually I encountered a lot of issues with this new cursor type; e.g. if a certain sorting is crucial, but various different filters – not necessarily on "Key" fields – have to be aplied. Here almost always the index related to the "Key" (ORDER BY) is used – and scanned – even though an optimized index – for the actual filter (WHERE) – exists …

    In a recent case the difference is 2.000.000 Reads and 2.500 msec vs. 6 Reads and 0 msec (w/o dynamic cursor).

    How to deal with this? Is there any way to change back to the old cursor type, e.g. as disabling the "cursor preparation" with special "diagnostics" in table "$ndo$dbproperty"?

    Best regards,


  3. stryk says:

    P.S.: I could solve this specific case – and some others – by adding an index which contained first all the fields from the WHERE clause then all the fields from the ORDER BY. This index was picked then, thus the query could be processed with few Reads and no time …

    But this cannot be the solution – I hope – because these kinds of indexes get pretty (too) large …

Skip to main content