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 8: Bulk Inserts
Bulk Inserts : Microsoft Dynamics NAV automatically buffers inserts to send them to Microsoft SQL Server at one time.
By using bulk inserts, the number of server calls is reduced. This improves performance.
Bulk inserts also improve scalability by delaying the actual insert until the last possible moment in the transaction. This reduces the time that tables are locked, especially tables that contain SIFT indexes.
Software developers who want to write high performance code that uses this feature should understand the following bulk insert constraints.
Bulk Insert Constraints
If you want to write code that uses the bulk insert functionality, you must be aware of the following constraints.Records are sent to SQL Server when the following occurs:
- You call COMMIT to commit the transaction.
- You call MODIFY or DELETE on the table.
- You call any FIND or CALC methods on the table.
- Records are not buffered if one of the following conditions is TRUE:
- The application is using the return value from an INSERT call, for example, “IF (GLEntry.INSERT) THEN”. o The table that you insert the records into contains any of the following:
- BLOB fields
- Fields that have the AutoIncrement property set to Yes
The following code example cannot use buffered inserts because it contains a FIND call on the GL/Entry table within the loop.
INSERT, MODIFY, DELETE, and LOCKTABLE
Each call to INSERT, MODIFY, or DELETE functions requires a separate SQL statement. If the table that you modify contains SumIndexes, then the operations are significantly slower. As a test, select a table that contains SumIndexes and execute one hundred INSERT, MODIFY, or DELETE operations to measure how long it takes to maintain the table and all its SumIndexes.
The LOCKTABLE function does not require any separate SQL statements. It only causes any successive reading from the table to lock the table or parts of it.