Bulk Inserts in Microsoft Dynamics NAV 5.0 SP1

  • Article

Bulk Inserts is a new feature in NAV 5.0 SP1 which is designed to improve performance when inserting multiple records by delaying the physical inserts on the SQL Server.

Before this change, inserts happen in the order that the C/AL code is running. With Bulk Insert, the NAV-client delays the actual inserts until the last possible moment in the transaction. This means that tables get locked later, so it reduces the amount of time that a table is locked.

"The last possible moment", means that the inserts will take place just before COMMIT. Or, if you use the return value of the INSERT command (IF Rec.INSERT THEN;). NAV also has to do the inserts if you run any MODIFY, DELETE or FIND methods on the table. So to take full advantage of this feature, you have to consider this when designing a NAV process.

 

Note: This feature has nothing to do with the Transact SQL command "BULK INSERT". The Bulk Insert all happens on the client. It is not using any special SQL Server features.

The Pseudo-code below illustrates how you will see the effect of Bulk Insert in a SQL Profiler trace. Without Bulk Insert, the SQL updates may look like this:

INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$Cust_ Ledger Entry"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$G_L REgister"

INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$Cust_ Ledger Entry"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$G_L REgister"

INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$Cust_ Ledger Entry"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$G_L REgister"

INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

 

With Bulk Insert, the same C/AL code would look lilke this:

 

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$Cust_ Ledger Entry"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$G_L REgister"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$Cust_ Ledger Entry"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$G_L REgister"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$Cust_ Ledger Entry"

SELECT TOP 1 FROM "NAV5"."dbo"."CRONUS International Ltd_$G_L REgister"

INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

INSERT INTO "NAV5"."dbo"."CRONUS International Ltd_$G_L Entry"

 

So, you will see exactly the same SQL updates. But with Bulk Insert, the inserts will be accumulated and run at the end of the process.

 

 

Lars Lohndorf-Larsen,

Escalation Engineer

These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.