Optimizing the D365FO & AX Database performance using RecordInsertList

Scenario:

As we work with data, we usually have a database. So, all the actions which we perform for customization should be in such a way that it should not impact the performance of the system. We should be very careful while writing any piece of code.

For example, if we are working on SSRS reports and if the data retrieved by report is huge. Let’s assume the report is a DP based, where we will have a query which accepts inputs from the user and retrieve the data as a report. Generally speaking, user wants the report to run within no time even the report has huge data. If it takes huge time to run, it would be frustrating right?

As a developer we can understand the scenario if it is a query where user gives input, then we will have the X Query classes like QueryRun, Query etc. We generally write the code here like below to get the data from the user input.

Let's assume I have a "query" which sets based on user input.

InventTable             inventTable;
InventTrans             inventTrans;
QueryRun                queryRun;
RecordInsertList        myTableTmpList;
MyTable                 myTable;

queryRun            = new QueryRun(query);

while(queryRun.next())
{
    
    inventTable = queryRun.get(tableNum(InventTable));
    inventTrans = queryRun.get(tableNum(InventTrans));
    
    myTable.clear();
    myTable.ItemId = inventTable.ItemId;
    myTable.PostedDate = inventTrans.DatePhysical;
    myTable.ItemCreatedDate  = any2Date(inventTable.CreatedDateTime);
    myTable.insert();

}   

This code looks good and perfect, but it will call the database for each insert which will actually impact the report performance. I mean if it has 10000 lines, it will call database for each insert. Right? 

So, how to tackle this?

Also, in this particular scenario we only need to use Query builder classes which are actually slower than the normal select statement in X but performance should be improved. Also, we cannot use insert_recordset here. So, is there any way to call the database only once with all the data to insert?

Solution:

One best way is to use Query builder classes along with RecordInsertList.

So, what is RecordInsertList?

As the name suggests it makes all the records which are retrieved by while or query builder into a list and finally calls the database once for the bulk insert of data. In this way we can speed up the performance of database. Simply a kind of List class.

Below is the code which explains the functionality of this using only a while loop.

Just like a normal while select, we will select all the records where the invoice date range is between given from and to date. So whatever data is retrieved it is added as an element into list and finally the list will call the insertDatabase() method which will insert all the listed data at once to the table.

class testRecordInsertList
{
    public static void main(Args  _args)
    {
        ProjInvoiceJour         projInvoiceJour;
        RecordInsertList        myTableTmpList;
        MyTable                 myTable;
        
        //initiating the list with temp table buffer
        myTableTmpList = new RecordInsertList(tableNum(MyTable), false, false, false, false, false, myTable);
            
        while select InvoiceDate InvoiceId from projInvoiceJour 
            where projInvoiceJour.InvoiceDate >= _fromdate
                && projInvoiceJour.InvoiceDate <= _todate
            {
                myTable.InvDate = projInvoiceJour.InvoiceDate;
 	            myTable.Invoice = projInvoiceJour.InvoiceId;
                myTableTmpList.add(myTable);
            }
        myTableTmpList.insertDatabase();
    }
}

So, with this all the data is retrieved and made as a list for each record and finally will call database for insertion of all records as a list.

Below is the code where I am using Query classes with while.

Let’s assume that I have a complex query called “query” which sets based on user input. For demonstration purpose I only used 2 tables for the query.

The code will be as below.

InventTable             inventTable;
InventTrans             inventTrans;
QueryRun                queryRun;
RecordInsertList        myTableTmpList;
MyTable                 myTable;

queryRun            = new QueryRun(query);
myTableTmpList      = new RecordInsertList(MyTable), false, false, false, false, 
                    false, myTable);        

while(queryRun.next())
{
    
    inventTable = queryRun.get(tableNum(InventTable));
    inventTrans = queryRun.get(tableNum(InventTrans));
    
    myTable.clear();
    myTable.ItemId = inventTable.ItemId;
    myTable.PostedDate = inventTrans.DatePhysical;
    myTable.ItemCreatedDate  = any2Date(inventTable.CreatedDateTime);
    myTableTmpList.add(myTable);

}   
myTableTmpList.insertDatabase();

When compared to speed between normal insert() and RecordInsertList, there will be huge difference. RecordInsertList is much faster than insert().