Module 1: DATA AND PROCESS MODEL
Module 2: MASTER TABLES AND PAGES
Module 3: DOCUMENTS
Module 4: POSTING
Module 5: FEATURE INTEGRATION
Module 6: REPORTING
Module 7: STATISTICS
Module 8: DIMENSIONS
Module 9: ROLE TAILORING
Module 10: INTERFACES
Module 11: WEB SERVICES
Module 12: TESTING AND DEBUGGING
Module 13: SQL SERVER OPTIMIZATION
- Lesson 1: SQL Server for Microsoft Dynamics NAV
- Lesson 2: Representation of NAV Tables and Indexes in SQL Server
- Lesson 3: Collation Options
- Lesson 4: SQL Server Query Optimizer
- Lesson 5: SQL Server Query Optimizer
- Lesson 6: Data Access Redesign
- Lesson 7: C/AL Database Functions and Performance on SQL Server
- Lesson 8: Bulk Inserts
- Lesson 9: Locking, Blocking, and Deadlocks
- Lesson 10: SIFT Data Storage in SQL Server
- Lesson 11: SQL Server Profiler
Lesson 6: Data Access Redesign
Data Access Redesign
The SQL Server interface from Microsoft Dynamics NAV Server was rewritten for Microsoft Dynamics NAV 2013 to use ADO.NET instead of ODBC.
The new ADO.NET interface is a managed data access layer that supports SQL Server connection pooling. This can significantly 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.
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. Memory allocation is now in managed memory. This is generally more efficient than unmanaged memory.
In Microsoft Dynamics NAV 2013, all users who are connected to the same Microsoft Dynamics NAV Server instance share the data cache. This means that after one user has read a record, a second user who reads the same record retrieves it from the cache. In earlier versions of Microsoft Dynamics NAV, the data cache was isolated for each user.
Microsoft Dynamics NAV 2013 uses an improved cache system. The following functions use the cache system:
There are two types of caches:
- Global cache – For all users who are connected to a Microsoft Dynamics NAV Server instance.
- Private cache – For each user, for each company, in a transactional scope. Data in a private cache for a given table and company are flushed when a transaction ends.
The lock state of a table determines the cache to use. If a table is not locked, then 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. This 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.
Microsoft Dynamics NAV 2013 synchronizes caching between Microsoft Dynamics NAV Server instances that are connected to the same database. By default, synchronization occurs every 30 seconds.