Category: Performance

Development Tips, Performance

Bulk Inserts – in Navision 2015

Ashwini Tripathi

By default, Microsoft Dynamics NAV automatically buffers inserts in order to send them to Microsoft SQL Server at one time.

By using bulk inserts, the number of server calls is reduced, thereby improving performance.

Bulk inserts also improve scalability by delaying the actual insert until the last possible moment in the transaction. This reduces the amount of time that tables are locked; especially tables that contain SIFT indexes.

Application developers who want to write high performance code that utilizes 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 any of the following conditions are met:

  • The application is using the return value from an INSERT call; for example, “IF (GLEntry.INSERT) THEN”.
  • 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

The following code example cannot use buffered inserts because 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;

If you rewrite the code, as shown in the following example, you can use buffered inserts.

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.

Disabling Bulk Inserts

Disabling bulk inserts can be helpful when you are troubleshooting failures that occur when inserting records. To disable bulk inserts, you set the BufferedInsertEnabled parameter in the CustomSettings.config file of the Microsoft Dynamics NAV Server to FALSE.

Performance

SETAUTOCALCFIELDS Function – in Nav 2015

Ashwini Tripathi

Sets the FlowFields that you specify to be automatically calculated when the record is retrieved from the database.

[OK :=] Record.SETAUTOCALCFIELDS([Field1,Field2, …])

To remove the automatic calculation setting on a field that you previously set with the SETAUTOCALCFIELDS function, you must call SETAUTOCALCFIELDS without parameters.

If you omit the optional return value and if the record cannot be set to automatically calculate, then a run-time error occurs.

If you include a return value, then you must handle any errors.

You can improve performance by using the SETAUTOCALCFIELDS function before looping through records with FlowFields instead of calling the CALCFIELDS function on each record in the loop.

The automatic calculation setting applies to the fields that you specify until the Record parameter goes out of scope or until you call SETAUTOCALCFIELDS without any parameters.

The fields that you specify in the Field parameters are added to any fields that you previously set with the SETAUTOCALCFIELDS function. Previously set fields are not overwritten when you call SETAUTOCALCFIELDS with different parameters. To clear the list of fields that are automatically calculated, call the SETAUTOCALCFIELDS function without parameters.

If you assign a record to another record variable, then the automatic calculation setting on FlowFields is not transferred with the record.

If you copy a record by using the COPY Function (Record), then the automatic calculation setting on FlowFields is copied with the record.

The Rec and xRec system variables are set to always automatically calculate FlowFields. If you call the NEXT function on Rec or xRec, then FlowFields in the record are automatically calculated.

Below example show how to use the CALCFIELDS function.

// Using CALCFIELDS

IF (Customer.FIND(’-’)=true) THEN

REPEAT

Customer.CALCFIELDS(Balance,”Net Change”);

… // your other codes for processing.

UNTIL (Customer.NEXT=0)

Below example show how you can use the SETAUTOCALCFIELDS function instead of the CALCFIELDS function to improve performance.

// Using SETAUTOCALCFIELDS

Customer.SETAUTOCALCFIELDS(Balance,”Net Change”);

IF (Customer.FIND(’-’)=true) THEN

REPEAT

// Customer.Balance and Customer.”Net Change” have been auto calculated.

… //your rest of the code for processing.

UNTIL (Customer.NEXT=0)

The following example shows how to add fields to the list of automatically calculated FlowFields and how to clear the list of automatically calculated FlowFields

Customer.SETAUTOCALCFIELDS(Balance);

Customer.SETAUTOCALCFIELDS(“Net Change”);

// Above lines are equivalent to the following:

Customer.SETAUTOCALCFIELDS(Balance,”Net Change”);

// To clear the list of automatically calculated FlowFields so that no fields are calculated automatically, use the following code.

Customer.SETAUTOCALCFIELDS();

It is a common task to retrieve data and request calculation of associated FlowFields. The following example traverses customer records, calculates the balance, and marks the customer as blocked if the customer exceeds the maximum credit limit.

Note the below Customer record and associated fields are imaginary.

IF Customer.FINDSET() THEN

REPEAT

Customer.CALCFIELDS(Customer.Balance)

IF (Customer.Balance > MaxCreditLimit) THEN BEGIN

Customer.Blocked = True;

Customer.MODIFY();

END  ELSE IF (Customer.Balance > LargeCredit) THEN BEGIN

Customer.Caution = True;

Customer.MODIFY();

END;

UNTIL Customer.NEXT = 0;

In Microsoft Dynamics NAV 2015, you can do this much faster. First, we set a filter on the customer.

This could also be done in Microsoft Dynamics NAV 2009, but behind the scenes the same code as mentioned earlier would be executed.

In Microsoft Dynamics NAV 2015, setting a filter on a record is translated into a single SQL statement.

Customer.SETFILTER(Customer.Balance,’>%1’, LargeCredit);

IF Customer.FINDSET() THEN REPEAT

Customer.CALCFIELDS(Customer.Balance)

IF (Customer.Balance > MaxCreditLimit) THEN BEGIN

Customer.Blocked = True;

Customer.MODIFY();

END   ELSE IF (Customer.Balance > LargeCredit) THEN BEGIN

Customer.Caution = True;

Customer.MODIFY();

END;

UNTIL Customer.NEXT = 0;

In the previous example, an extra call to CALCFIELDS still must be issued for the code to be able to check the value of Customer.Balance.

In Microsoft Dynamics NAV 2015, you can optimize this further by using the new SETAUTOCALCFIELDS function.

Customer.SETFILTER(Customer.Balance,’>%1’, LargeCredit);

Customer.SETAUTOCALCFIELDS(Customer.Balance)

IF Customer.FINDSET() THEN REPEAT

IF (Customer.Balance > MaxCreditLimit) THEN BEGIN

Customer.Blocked = True;

Customer.MODIFY();

END   ELSE IF (Customer.Balance > LargeCredit) THEN BEGIN

Customer.Caution = True;

Customer.MODIFY();

END;

UNTIL Customer.NEXT = 0;