Improve performance of grouping operations in Acumatica

Hello everybody,

today I want to write a note about interesting case I faced recently. 

I was asked to load from Acumatica database Sales orders joined with PO Receipts by inventory, but I had one issue. It was needed to join only by latest PO Receipt, not by allo PO Receipts. In other words it was needed to take only one element of each group. 

As usually such kind of implementation is done with help of overloading delegate, which loads all data from SQL Server into memory and then does grouping operaions in memory. Such approach is workable on relatively small cases, but if database of customer 

is around 130 Gb, then we faced timeout issue. 

How to deal with it. 

After some trial and error I made interesting discovery. If to create view in terms of MS SQL view, and create corresponding DAC Class Acumatica will be able to work with View as it was MS SQL table. 

Then I've decided to create SQL view, with CTE. It looked something like this:

SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
create view [dbo].[POReceiptLineView] as
 
	WITH cte AS
	(
	   SELECT *,
			 ROW_NUMBER() OVER (PARTITION BY inventoryid ORDER BY ReceiptDate DESC) AS rn
	   FROM POReceiptLine
	)
	SELECT [CompanyID]
      ,[ReceiptNbr]
      ,[LineNbr]
      ,[SortOrder]
      ,[BranchID]
      ,[InventoryID]
      ,[VendorID]
      ,[ReceiptDate]
      ,[SubItemID]
      ,[SiteID]
      ,[LotSerialNbr]
      ,[POType]
      ,[PONbr]
      ,[POLineNbr]
      ,[POLineComplete]
      ,[UOM]
      ,[ReceiptQty]
      ,[BaseReceiptQty]
      ,[CuryUnitCost]
      ,[UnitCost]
      ,[CuryMultExtCost]
      ,[MultExtCost]
      ,[ManualPrice]
      ,[ManualDisc]
      ,[DiscPct]
      ,[CuryDiscAmt]
      ,[DiscAmt]
      ,[CuryLineAmt]
      ,[LineAmt]
      ,[GroupDiscountRate]
      ,[DocumentDiscountRate]
      ,[DiscountID]
      ,[DiscountSequenceID]
      ,[ReceiptType]
     FROM cte WHERE rn = 1

As you can see nothing fance. Ordinary SQL view with CTE.

And then I've created very simple DAC class:

       [Serializable]
       public class POReceiptLineView : IBqlTable
       {
           public abstract class inventoryID : IBqlField
           {
           }
 
           [POLineInventoryItem(Filterable = true)]
           [PXDefault()]
           [PXForeignReference(typeof(Field<inventoryID>.IsRelatedTo<InventoryItem.inventoryID>))]
           public virtual int? InventoryID
           {
               get;set;
           }
 
           public abstract class receiptType : IBqlField
           {
           }
 
           [PXDBString(IsKey = true)]
           public virtual string ReceiptType
           {
               getset;
           }
 
           public abstract class receiptNbr : IBqlField
           {
           }
 
           [PXDBString(IsKey = true)]
           public virtual string ReceiptNbr
           {
               getset;
           }
 
           public abstract class siteID : IBqlField
           {
           }
 
           [PXDBInt()]
           public virtual int? SiteID
           {
               getset;
           }
 
           public abstract class receiptDate : IBqlField
           {
           }
 
           [PXDBDate()]
           public virtual DateTime? ReceiptDate
           {
               getset;
           }
 
           [PXDBDate()]
           public virtual DateTime? CreatedDateTime
           {
               getset;
           }
 
           #region CuryUnitCost
           public abstract class curyUnitCost : PX.Data.IBqlField
           {
           }
           
 
           [PXDBCurrency(typeof(Search<CommonSetup.decPlPrcCst>), typeof(POReceiptLine.curyInfoID), typeof(POReceiptLine.unitCost))]
           [PXUIField(DisplayName = "Unit Cost", Visibility = PXUIVisibility.SelectorVisible)]
           [PXDefault(TypeCode.Decimal, "0.0")]
           public virtual Decimal? CuryUnitCost
           {
               getset;
           }
           #endregion
 
           public abstract class receiptQty : PX.Data.IBqlField
           {
           }
 
           [PXDBQuantity(typeof(POReceiptLine.uOM), typeof(POReceiptLine.baseReceiptQty), HandleEmptyKey = true, MinValue = 0)]
           [PXDefault(TypeCode.Decimal, "0.0")]
           [PXFormula(nulltypeof(SumCalc<POReceipt.orderQty>))]
           [PXUIField(DisplayName = "Receipt Qty.", Visibility = PXUIVisibility.Visible)]
           public virtual Decimal? ReceiptQty
           {
               get;set;
           }
       }

And finally I've used following BQL query:

var query = new PXSelectJoinGroupBy<SOOrder,
    InnerJoin<SOLineOn<SOOrder.orderNbrEqual<SOLine.orderNbr>, And<SOOrder.orderTypeEqual<SOLine.orderType>>>>,
    Aggregate<GroupBy<SOLine.inventoryIDSum<SOLine.curyLineAmt>>>>(this);
 
query.Join<InnerJoin<POReceiptLineViewOn<POReceiptLineView.inventoryIDEqual<SOLine.inventoryID>>>>();
query.Join<InnerJoin<POReceiptOn<POReceipt.receiptTypeEqual<POReceiptLineView.receiptType>,
                            And<POReceipt.receiptNbrEqual<POReceiptLineView.receiptNbr>>>>>();
 
query.WhereAnd<Where<POReceipt.receiptTypeEqual<POReceiptType.poreceipt>>>();
query.WhereAnd<Where<POReceiptLineView.siteIDEqual<Current<Filter.wareHouse>>>>();

And then executed testing. Try to guess what was performance boost of SQL view with CTE usage in comparison with code which took everything from database? 

Version with CTE view become 20 times faster!

Summary

If you need to make some complicated data massaging with some kind of tricky grouping analysis of data, then instead of loading plenty of staff from Acumatica database use SQL Views. It will help you to speed up your Acumatica drastically. 

One more point worth mentioning is that if you will make complicated DAC class for your view, you'll get errors. Don't try to add plenty of attributes to your DAC Class view. Otherwise Acumatica will refuse to work with your view.

No Comments

Add a Comment