Hi Everyone
Sometimes you need better control on how Acumatica selects data from database. And here Acumatica Framework provides you some ways to optimize data query.
Using PXFieldScope you can specify what fields will be selected form database. This can save some traffic and processors time in large and extra-large statements. Also it can improve performance in case you have plenty of data, but use very few fields from the selected tables. And of course excluding of some complex sub-selects may significantly improve data selecting on SQL servers side.
No RowSelecting events (e.g. from attributes) will be fired for the fields not fed into the FXFieldScope constructor.
Note, that for the head table all key fields should be included into the field scope declaration.
For example ARStatement and ARStatementCycle tables both include a lot of decimal and string fields (like statement totals for various ages, aging bucket labels, etc.) Still, under some circumstances these field may be not needed to perform an operation on statements. If there is substantial amount of statements in the system, restricting the selected fields may boost performance significantly.
PXSelectBase<ARStatement> select = new PXSelectJoin<ARStatement,
LeftJoin<ARStatementCycle, On<ARStatementCycle.statementCycleId,
Equal<ARStatement.statementCycleId>>,
LeftJoin<Customer, On<Customer.bAccountID,
Equal<ARStatement.statementCustomerID>>>>>(this);
using (new PXFieldScope(select.View,
typeof(ARStatement.branchID),
typeof(ARStatement.statementCycleId),
typeof(ARStatement.statementDate),
typeof(ARStatement.customerID),
typeof(ARStatement.statementCustomerID),
typeof(ARStatement.curyID),
typeof(ARStatement.dontPrint),
typeof(ARStatement.dontEmail),
typeof(ARStatement.printed),
typeof(ARStatement.emailed),
typeof(ARStatementCycle.descr),
typeof(Customer.printCuryStatements)))
{
PXResultset<ARStatement> res = select.Select();
}
Big thanks to Acumatica Development team for this example.
Have a nice development!
Hi Sergey,
I tried to do it with my custom view, but I got an error message: AplicacionInventoryItem view doesn’t exist. This is because I use the view for the DataMember property of my grid.
I want to retrieve especific fields to improve the performance.
This is my view:
public PXSelectJoin<
InventoryItem,
LeftJoin<InventoryItemAplication,
On<InventoryItemAplication.inventoryID, Equal>,
LeftJoin<Aplicacion,
On<Aplicacion.aplicacionID, Equal>,
LeftJoin<INItemXRef,
On<INItemXRef.inventoryID, Equal>,
LeftJoin<INSubItemSegmentValue,
On<INSubItemSegmentValue.inventoryID, Equal>>>>>,
Where2<
Where<Current, IsNull,
Or<Aplicacion.anoInicial, LessEqual<Current>, And<Aplicacion.anoFinal, GreaterEqual<Current>>>>,
And2<
Where<Current, IsNull,
Or<Aplicacion.segmento, Equal<Current>>>,
And2<
Where<Current, IsNull,
Or<Aplicacion.marca, Equal<Current>>>,
And2<
Where<Current, IsNull,
Or<Aplicacion.modelo, Equal<Current>>>,
And2<
Where<Current, IsNull,
Or<Aplicacion.motor, Equal<Current>>>,
And2<
Where<Current, IsNull,
Or<Aplicacion.usrCombustible, Equal<Current>>>,
And2<
Where<Current, IsNull,
Or<InventoryItem.itemClassID, Equal<Current>>>,
And2<
Where<Current, IsNull,
Or<INItemXRef.alternateID, Equal<Current>>>,
And2<
Where<Current, IsNull,
Or<InventoryItem.descr, Like<Current>>>,
And<
Where<Current, IsNull,
Or<INSubItemSegmentValue.value, Equal<Current>>>>>>>>>>>>>,
OrderBy<Asc>> AplicacionInventoryItem;
Can you help me with this?