NAV Sustained Engineering Team Blog: Cursor Types

[Blog bot]

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"


WITH(READUNCOMMITTED)


undefined(("Document No_"=


ORDERBY "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 intset @p1=-1


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


declare @p3 intset @p3=2+4096+8192 -- Dynamic


declare @p4 intset @p4=1


declare @p5 intset @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"


WITH (READUNCOMMITTED)


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


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


'


,@p3


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


'START'


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.



Читать дальше