Bulk Inserts – Dynamics NAV

Microsoft has introduced the bulk insert functionality which will allow the developers to write high performance code. 

What is Bulk Insert : 
Microsoft Dynamics NAV automatically keep all the records in a buffer and then send them to Microsoft SQL server at a one time. Before this modification, insert happen in the order of the C/AL code running and had lot of server calls which will slowdown the application and also lock the tables many number of times before completing a one transaction.

Advantages of Bulk Insert : 
 1. Number of server calls are been reduced and it directly improve the performance of the application. 
 2. Improve the scalability. This was achieved by delaying the actual insert until the last possible moment in the transaction and by doing this Mictrosoft was able to reduce the amount of time that tables are locked. 

However this functionality has few constrains and it is a must to understand them before using this functionality. 

Client will send the records to SQL server when the following occurs, 

• When the COMMIT was called to commit the transaction
• Call MODIFY or DELETE on the table
• Call and FIND or CALC method on the table

 IF (JnlLine.FIND('-')) THEN BEGIN  
  GLEntry.LOCKTABLE;  
  REPEAT  
   IF (GLEntry.FINDLAST) THEN  
    GLEntry.NEXT := GLEntry."Entry No." + 1  
   ELSE  
    GLEntry.NEXT := 1;  
   // The FIND call will flush the buffered records.  
   GLEntry."Entry No." := GLEntry.NEXT ;  
   GLEntry.INSERT;  
  UNTIL (JnlLine.FIND('>') = 0)  
 END;  
 COMMIT;  

 IF (JnlLine.FIND('-')) THEN BEGIN  
  GLEntry.LOCKTABLE;  
  IF (GLEntry.FINDLAST) THEN  
   GLEntry.Next := GLEntry."Entry No." + 1  
  ELSE  
   GLEntry.Next := 1;  
  REPEAT  
   GLEntry."Entry No.":= GLEntry.Next;  
   GLEntry.Next := GLEntry."Entry No." + 1;  
   GLEntry.INSERT;  
  UNTIL (JnlLine.FIND('>') = 0)  
 END;  
 COMMIT;  
 // The inserts are performed here.