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
SET QUOTED_IDENTIFIER ON
create view [dbo].[POReceiptLineView] as
WITH cte AS
ROW_NUMBER() OVER (PARTITION BY inventoryid ORDER BY ReceiptDate DESC) AS rn
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:
public class POReceiptLineView : IBqlTable
public abstract class inventoryID : IBqlField
[POLineInventoryItem(Filterable = true)]
public virtual int? InventoryID
public abstract class receiptType : IBqlField
[PXDBString(IsKey = true)]
public virtual string ReceiptType
public abstract class receiptNbr : IBqlField
[PXDBString(IsKey = true)]
public virtual string ReceiptNbr
public abstract class siteID : IBqlField
public virtual int? SiteID
public abstract class receiptDate : IBqlField
public virtual DateTime? ReceiptDate
public virtual DateTime? CreatedDateTime
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)]
public virtual Decimal? CuryUnitCost
public abstract class receiptQty : PX.Data.IBqlField
[PXDBQuantity(typeof(POReceiptLine.uOM), typeof(POReceiptLine.baseReceiptQty), HandleEmptyKey = true, MinValue = 0)]
[PXUIField(DisplayName = "Receipt Qty.", Visibility = PXUIVisibility.Visible)]
public virtual Decimal? ReceiptQty
And finally I've used following BQL query:
var query = new PXSelectJoinGroupBy<SOOrder,
InnerJoin<SOLine, On<SOOrder.orderNbr, Equal<SOLine.orderNbr>, And<SOOrder.orderType, Equal<SOLine.orderType>>>>,
query.Join<InnerJoin<POReceiptLineView, On<POReceiptLineView.inventoryID, Equal<SOLine.inventoryID>>>>();
query.Join<InnerJoin<POReceipt, On<POReceipt.receiptType, Equal<POReceiptLineView.receiptType>,
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!
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.