24) How to Improve Performance of SSRS Reports in D365 FO

Performance has been one of the huge bottlenecks for SSRS Reports since AX 2012 RTM.

Even in D365 FO if the reports are not optimized properly there can be heavy lags in the overall processing.

There are several ways through which we perform optimization in SSRS Reports :-


1) SRSReportDataProviderPreProcessTempDB - When we are creating Report Data Provider
Logic based report we should always extend our DP class with SRSReportDataProviderPreProcessTempDB. This class extension has been proved very helpful for optimization especially when we are dealing with huge amount of data in the report.

Here is an example :-

class ABCDP extends SRSReportDataProviderPreProcessTempDB
{
}


2) RecordInsertList - RecordInsertList object helps to perform bulk insertion in one go without bothering the back end to go for multiple round trips for record insertion.

Here is an example :-

RecordInsertList              insertList;
ABCTMP                         abcTmp;


insertList = new RecordInsertList(tableNum(ABCTMP), false, false, false, false, false, abcTmp);

abcTmp.Field1= "A";
abcTmp.Field2="B";

insertList.add(abcTmp);

insertList.insertDatabase();


3) RecordViewCache - RecordViewCache is one of the caching mechanisms provided by x++ in the form of a class. It helps to cache the records so that upon running the data can be fetched right away from cache rather than generating from the Report Dataset.

This mechanism is useful only for those reports which have to be executed very frequently in a day.

Here is an example :-

RecordViewCache      viewCache;
ABCTMP                    abcTmp;

abcTmp.Field1= "A";
abcTmp.Field2="B";

viewCache = new RecordViewCache(SOSummaryWithInvTmp);

abcTmp.insert();


4)  Refrain Use of While Select and multiple select statements :- Avoid using while select and multiple select statements as it causes multiple roundtrips with Database thereby degrading the performance in several ways. Try using views or queries.

In case you have to use While select make sure that you are fetching only those fields are fetched which are required. Field List is while select is always helpful.

Also if possible replace while select statements with select statements using aggregate functions.

Here is an example :-

while select  SalesQy from Salesline where salesline.salestatus = = SalesStatus::BackOrdered
{
   totalqty = totalqty + salesline.SalesQty ;
}

The above statement will consume lot of system resources while executing.

We can simply replace it with the below statement thereby reducing the system usage to a great extent :-

select sum(salesqty) from SalesLine where salesline.salesstatus = =  SalesStatus::BackOrdered ;

totalqty = salesline.SalesQty ;



In case multiple select statements without field list make sure you are using firstonly in front of the table buffer.

Here is an example :-

select firstonly custtable;


5) Use insert_recordset as much as possible ( even instead of while select) :- insert_recordset and update_recordset are the two statements in x++ which gives optimum performance in terms of data fetch.

We can even use it instead of while select statement as well.

Let us take a look at the below statement using while select statement :-

while select InventTransOrigin, inventDimId, DatePhysical,itemId, Qty, StatusReceipt,
                StatusIssue, CostAmountPosted  , CostAmountAdjustment from inventtrans
            join RecId from inventtransorigin
            where inventtransorigin.RecId = = inventtrans.InventTransOrigin
            join inventDimId, InventLocationId, InventSiteId from inventdim
            join InventLocationType, InventLocationId, Name from inventLocation
            order by inventLocation.InventLocationType,inventLocation.InventLocationId
            where inventdim.inventDimId = = inventtrans.inventDimId
               && inventtrans.DateFinancial != dateNull()
               && inventtrans.DateFinancial <= AsonDate
               && inventtrans.Qty != 0
               && (!itemId || inventtrans.itemId = = itemId)
               && inventLocation.InventLocationId = = inventdim.InventLocationId
               && (!inventLocationId || inventdim.InventLocationId = = inventLocationId)
               && (!inventsiteid || inventdim.InventSiteId = = inventsiteid)
            exists join ItemId, DivisionId, CategoryId, SubCategoryId from InventTable
            where InventTable.ItemId = = inventtrans.ItemId
               && (!DivisionId || InventTable.DivisionId = = DivisionId)
               && (!CategoryId || InventTable.CategoryId = = CategoryId)
               && (!SubCategoryId || InventTable.SubCategoryId = = SubCategoryId)
            exists join DisplayProductNumber, ProductType from ecoResProduct
            where ecoResProduct.DisplayProductNumber = = inventtrans.ItemId
                && ecoResProduct.ProductType = = EcoResProductType::Item

{
                        reporttmp.ItemId                     = inventtrans.ItemId ;
                        reporttmp.InventSiteId            = inventdim.InventSiteId ;
                        reporttmp.InventLocationId    = inventdim.InventLocationId ;
                        reporttmp.LocationName        = inventLocation.Name ;
}


The above statement will consume lot of system resources and will be a huge performance bottleneck.

Instead of this we can simply use insert_recordset.

Here is an example :-


insert_recordset reporttmp(ItemId,InventTransRecId,InventTransId,InventSiteId,InventLocationId,LocationName,InventLocationType)
            select ItemId,RecId from inventtrans
            join InventTransId from inventtransorigin
            where inventtransorigin.RecId = = inventtrans.InventTransOrigin
            join  inventdim
            join  InventSiteId,InventLocationId,Name,InventLocationType from inventLocation
            order by inventLocation.InventLocationType,inventLocation.InventLocationId
            where inventdim.inventDimId = = inventtrans.inventDimId
               && inventtrans.DateFinancial != dateNull()
               && inventtrans.DateFinancial <= AsonDate
               && inventtrans.Qty != 0
               && (!itemId || inventtrans.itemId = = itemId)
               && inventLocation.InventLocationId = = inventdim.InventLocationId
               && (!inventLocationId || inventdim.InventLocationId = = inventLocationId)
            exists join InventTable
            where InventTable.ItemId = = inventtrans.ItemId
               && (!DivisionId || InventTable.DivisionId = = DivisionId)
               && (!CategoryId || InventTable.CategoryId = = CategoryId)
               && (!SubCategoryId || InventTable.SubCategoryId = = SubCategoryId)
            exists join ecoResProduct
            where ecoResProduct.DisplayProductNumber = = inventtrans.ItemId
                && ecoResProduct.ProductType == EcoResProductType::Item;


           

If we follow the above 5 principles properly we will be able to see significant improvement in the performance of our SSRS Report we are working upon.