Dynamic cursor behaviour

krikikriki Member, Moderator Posts: 9,086
edited 2010-02-15 in SQL Performance
I just wondered about something:
We know that NAV (the latest NAV version and we have some exceptions anyway. But I am not going into this now.) uses dynamic cursors for getting records.
And we know that SQL in this case looks more at the ORDER BY clause to decide which index to use.
And we also know that SQL looks more at the WHERE clause in case of fast forward cursors.

Now my question : wouldn't it be good if we can influence SQL server through a trace-flag to look more at the WHERE clause to decide the index to use in case of a dynamic cursor?

I did some searches (google, MS connect under SQL, SQL server books online) but I couldn't find such a flag.

If someone know such a flag, let me know.

In the other case, I will post a suggestion on connect.
Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


Comments

  • NaviDeveloper_NLNaviDeveloper_NL Member Posts: 42
    A fast forward cursor copies the query result to tempdb and reads the fetches from tempdb.

    The dynamic cursor reflects all changes made to the underlying tables. Each time a fetch
    is called the cursor is re-evaluated.

    For this reason sorting of a dynamic cursor is not practical.
  • strykstryk Member Posts: 645
    Hi!

    Well, actually there is a reason why SQL Server supports different types of cursors, thus, there might also be a real purpose for using "Dynamic Cursors" ...
    So basically it should always be up to the process declaring the cursor to define the appropriate type. To generally overrule this in SQL Server is IMHO a bad idea.
    I think the best way would be to give the NAV developers/administratirs a chance to define the required cursor type within NAV; e.g. by a flag set with FIND or FINDSET, or maybe NAV-globally using the "diagnostics" feature in table "$ndo$dbproperty" (similar to disabling "cursor preparation" or using "repeatableread").

    Cheers,
    Jörg
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • krikikriki Member, Moderator Posts: 9,086
    Well, there is a version in which the fast forward cursors are used for forms and dynamic cursors for the rest.
    But still no way to always force fast forward cursors.

    Probably it would be best to use a NAV-globally. This way we have the best of both worlds. Dynamic cursors out-of-the-box. And the possibility to change to fast forward cursors with heavily SQL-tuned systems.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • pdjpdj Member Posts: 643
    I agree - I want fast forward cursors as well...

    However; it is possible to force it in code like this:

    COMMIT; // For some reason...
    CURRENTTRANSACTIONTYPE := TRANSACTIONTYPE::Browse;

    Haven't used it yet - just saw it in a Microsoft WebCast the other day.
    Regards
    Peter
  • krikikriki Member, Moderator Posts: 9,086
    But the problem is still that you have to change the code.
    Before it was enough to play with the properties of the keys in NAV which is a lot easier and faster to do.
    Searches for all places where the table is used in a certain way is a lot more work and thus costlier.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • pdjpdj Member Posts: 643
    kriki wrote:
    But the problem is still that you have to change the code.
    Yes, and I didn't say I liked the method - it is just the only way to do it, yet :(
    Regards
    Peter
Sign In or Register to comment.