Monday, August 25, 2014

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
Buffer Insert functionality will not used if the following conditions are met,
  • If the application is using the return value from an INSERT call,
    • IF (GLEntry.INSERT) THEN
  • If the table that you are going to insert the records into contains any of the following
    • BLOB fields
    • Fields with the AutoIncrement property set to YES
Addition to above mention constrains Dynamics NAV Debug will not stop in the exact INSERT statement but, due to delayed Bulk Insert, in a position right after the INSERT AL Statement sequence.  To read more about this issue use the following link : MSDN

 Following code example cannot use the buffer insert since it contains a FIND call on the GL/Entry table within the loop.

 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;  

Therefore Developer need to redesign the code to maximize the usage of Bulk Insert.
Following is a modified code of above example and this code segment use the bulk insert functionality.
 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.  

To read the original blog of MSDN please refer the following link
MSDN

Thank you and Regards,
Tharanga Chandrasekara.

No comments:

Post a Comment