Skip to main content

Microsoft Dynamics NAV-SQL Challenge: The Curse of the Cursor

by Jörg A.
Consultant,

Consultants and users alike are discussing "issues with cursors in NAV on SQL" on the web, even several Microsoft/NAV teams blog about it. But what the heck is such a "cursor" thingy, and what's the problem with it?

Simplified, an SQL cursor is a temporary declared SQL object - similar to a variable - which is used to store a result-set (= multiple) of records. Such a "cursor" could be used to browse through the records, e.g. within a loop.

Keep in mind that our "goode olde" NAV was originally designed  for its own proprietary platform - the native C/SIDE Server. This "oldtimer" works on "Index Sequential Access Methods" (ISAM), and this is still shown in the NAV code processing:

  1. Define a Key (SETCURRENTKEY);
  2. Apply a Filter (SETRANGE & Co.);
  3. Retrieve the Result-Set (FINDSET);
  4. Loop through the records.

Yet, the C/AL programming language does not provide really "relational" commands, like JOIN or CROSS APPLY etc. with SQL Server, hence we're still stuck with the old algorithms (mostly).

Thus, to transfer the old ISAM algorithms to SQL site it's necessary to use such "cursors".

There are various types and variants of "cursors": static, dynamic, fast-forward, read only, for update, etc.. Each type has different abilities in dealing with the result-set (e.g. a read-only cursor cannot modify records) and consumption of system-resources, like CPU, RAM or "...

FREE Membership Required to View Full Content:

Joining MSDynamicsWorld.com gives you free, unlimited access to news, analysis, white papers, case studies, product brochures, and more. You can also receive periodic email newsletters with the latest relevant articles and content updates.
Learn more about us here

About Jörg A.

Jörg A. Stryk has worked with Microsoft Dynamics NAV since 1997, version 1.20.

Being project-manager, consultant, administrator, developer or supporter on Microsoft partner-sites and NAV customer-sites, he has thoroughly explored nearly all areas of the application and its technology.

Since 2003 he is focusing on "Dynamics NAV & SQL Server Performance Optimization and Troubleshooting", founding his company "STRYK System Improvement" in 2006. As a freelance consultant, he is supporting Microsoft Dynamics partners and customers with their NAV/SQL projects worldwide.

His book "The NAV/SQL Performance Field Guide" (ISBN 978-3-8370-1442-6) is published in Europe, the United Kingdom and North America.

In recognition of his valuable participation in various NAV communities, he received the Microsoft award "Most Valuable Professional" (MVP) for MS Dynamics NAV in 2007, 2008 and 2009.

Visit Jörg Stryk at www.stryk.info or his BLOG http://dynamicsuser.net/blogs/stryk/

More about Jörg A.