Share via

Data Access Redesign

  • Article

The SQL Server interface from Microsoft Dynamics NAV Server has been rewritten for Microsoft Dynamics NAV 2013 to use ADO.NET instead of ODBC. The advantages of the new access layer are described in the following sections.

Simplified Deployment

The new ADO.NET interface is a managed data access layer that supports SQL Server connection pooling, which can dramatically decrease memory consumption by Microsoft Dynamics NAV Server. SQL Server connection pooling also simplifies deployment of the Microsoft Dynamics NAV three-tier architecture for deployments where the three tiers are installed on separate computers. Specifically, administrators are no longer required to manually create SPNs or to set up delegation when the client, Microsoft Dynamics NAV Server, and SQL Server are on separate computers. For more information, see Walkthrough: Installing the Three Tiers on Three Computers.

Collation

SQL Server 2008 is full aligned with the collations in Windows Server 2008. When you upgrade from Microsoft Dynamics NAV 2009 to Microsoft Dynamics NAV 2013, the step to convert the database includes upgrading the database to Windows collation. The collation changes provide users with the most up-to-date and linguistically accurate cultural sorting conventions. For more information, see Collation and Unicode Support.

Decreased Resource Consumption

There is no longer a one-to-one correlation between the number of client connections and the number of SQL Server connections. In earlier versions of Microsoft Dynamics NAV, each SQL Server connection could consume up to 40 MB of memory. Additionally, memory allocation is now in managed memory, which is generally more efficient than unmanaged memory.

In Microsoft Dynamics NAV 2013, the data cache is shared by all users who are connected to the same Microsoft Dynamics NAV Server instance. This means that after one user has read a record, a second user who reads the same record gets it from the cache. In earlier versions of Microsoft Dynamics NAV, the data cache was isolated for each user.

Caching

Microsoft Dynamics NAV 2013 uses an improved cache system. The following functions use the cache system:

  • GET

  • FIND

  • FINDFIRST

  • FINDLAST

  • FINDSET

  • COUNT

  • ISEMPTY

  • CALCFIELDS

There are two types of caches:

  • Global cache – for all users connected to a Microsoft Dynamics NAV Server instance.

  • Private cache – per user, per company, in a transactional scope. Data in a private cache for a given table and company are flushed when a transaction ends.

The cache that is used is determined by the lock state of a table. If a table is not locked than the global cache is queried for data; otherwise, the private cache is queried.

Results from query objects are not cached.

For a call to any of the FIND functions, 1024 rows are cached. You can set the size of the cache by using the Data Cache Size setting in the Microsoft Dynamics NAV Server configuration file. The default size is 9, which approximates a cache size of 500 MB. If you increase this number by one, then the cache size doubles.

You can bypass the cache by using the SELECTLATESTVERSION Function (Database).

Microsoft Dynamics NAV 2013 synchronizes caching between Microsoft Dynamics NAV Server instances that are connected to the same database. By default, the synchronization occurs every 30 seconds.

You can set the cache synchronization interval by using the CacheSynchronizationPeriod parameter in the CustomSettings.config file. For more information, see Configuring Microsoft Dynamics NAV Server.

Improved Performance

Microsoft Dynamics NAV 2013 no longer uses server cursors to retrieve records. Instead, records are retrieved using multiple active result sets (MARS). Functions such as NEXT, FIND('-'), FIND('+'), FIND('>'), and FIND('<') are generally faster with MARS.

Note

Since Microsoft Dynamics NAV 2013 no longer uses server cursors to retrieve records, the Record Set property under Caching on the Advanced tab of the Alter Database page is no longer needed and has been removed.

SIFT indexes have also been improved. For example, COUNT and AVERAGE formulas can now use SIFT indexes. For more information, see CALCSUMS Function (Record) and CALCFIELDS Function (Record). MIN and MAX formulas now use SQL Server MIN and MAX functions exclusively.

RecordIds and SQL Variant columns in a table no longer prevent the use of BULK insert inserts. For more information, see Bulk Inserts.

In most cases, filtering on FlowFields issues a single SQL statement. In earlier versions of Microsoft Dynamics NAV, filtering on FlowFields issued an SQL statement for each filtered FlowField and for each record in the table in order to calculate the filtered FlowFields. The exceptions in Microsoft Dynamics NAV 2013 in which filtering on FlowFields does not issue a single SQL statement are as follows:

  • You use the ValueIsFilter option on a field and the field has a value. For more information about the ValueIsFilter option, see How to: Create, View, and Edit a Calculation Formula.

  • A second predicate is specified on a source field and the field that is used for the second predicate has a value. For example, when you specify the CalcFormula Property for a FlowField, you can specify table filters in the Calculation Formula window. If you specify two or more filters on the same source field, then filtering does not issue a single SQL statement.

  • You specify Validated for the SecurityFiltering Property on a record. This value for the SecurityFiltering property means that each record that is part of the calculation must be verified for inclusion in the security filter.

In most cases, calling the FIND or NEXT functions after you have set the view to include only marked records issues a single SQL statement. In earlier versions of Microsoft Dynamics NAV, calling FIND or NEXT functions that have marked records issued an SQL statement for each mark. There are some exceptions if many records are marked. For more information, see MARKEDONLY Function (Record).

See Also

Concepts

Changes in C/AL Behavior and Support from Earlier Versions of Microsoft Dynamics NAV
SumIndexField Technology (SIFT)
Query Objects and Performance