3

we created a custom screen which displays list of sales data based on filter conditions like (today, yesterday, this week, this month, this quarter, this year), we created a SQL view for this and then from VIEW we created and DAC and using it in custom screen. We also have filters in our screen. for filter conditions we are using view delegate and returning the data. the question is why the screen takes too long around 70 seconds to load 2K records. Is using view delegate decrease the speed of loading data. We can go with GI but we need to display images in the GRID so we opted for custom screen and also we have some report button in header which prints report. as we can't show images in GI we chose this.

Michel
  • 95
  • 5
  • Need more information. So using the view for your GI. Is it as slow ? – Rick Aug 12 '20 at 08:11
  • if we use view in GI directly it is fast, may be using of view delegate slow down the loading time. – Michel Aug 12 '20 at 08:46
  • Try to avoid views directly in SQL. The Acumatica way of doing this is PXProjection. Using PXProjection, you write the SQL statement (via BQL or FBQL) that brings all the records together and then map the fields of the SQL statement to the fields you define in the "DAC" that you create with the PXProjection attribute. This approach keeps Acumatica database agnostic. It also keeps all of the customization within Acumatica instead of your database, which is a plus for ongoing support and maintenance from your dev team. – Brian Stevens Aug 12 '20 at 13:58
  • Hi Michel, Can you please provide the view delegate code. That will help us to digging identifying the root cause of this performance issue. – Naveen B Sep 27 '20 at 18:28

1 Answers1

1

The slowness you see is most likely caused by combination of two reasons.

  1. When you use BQL view, it in fact requests only the number of records you see on the screen. For instance if you have grid with paging and only 20 records are visible on the page, the SQL select will have TOP 20 limitation. However, once you have select delegate, that optimization stops working since the framework does not know what you'd like to do with the data you select. The solution here would be to use SelectWithViewContext with DelegateResult return object instead of regular select. In that case user filtering, pagination and ordering is preserved in the select. (Use this method only if resulting records on the screen relate as 1 to 1 to the records you select. If you use any kind of aggregation or inserting records from 2 different select, that approach does not work)

Example:

protected virtual IEnumerable ardocumentlist()
{
PXSelectBase<BalancedARDocument> cmd =
    new PXSelectJoinGroupBy<BalancedARDocument,
        ...
        OrderBy<Asc<BalancedARDocument.docType, //Set necessary sorting fields: use the key fields
        Asc<BalancedARDocument.refNbr>>>> //Set necessary sorting fields: use the key fields
        (this);

PXDelegateResult delegResult = new PXDelegateResult
{
    IsResultFiltered = true, //set these fields to indicate that the result does not need re-filtering, resorting and re-paging
    IsResultTruncated = true,
    IsResultSorted = true
}

foreach (PXResult<BalancedARDocument> res_record in cmd.SelectWithViewContext())
{
    // add the code to process res_record
    delegResult.Add(res_record);
}
return delegResult;
}
  1. Probably you don't have proper indexes on your table since even if you select all 2k records at once it should not result in 70 seconds load time. Recommendation here would be to use the request profiler to catch the exact SQL generated (https://help-2020r2.acumatica.com/Help?ScreenId=ShowWiki&pageid=e4c450bb-86bc-4fb2-b7e6-1f715abe3c8b) and execute the SQL in MS SQL Management studio with option 'Include Actual Execution Plan' (https://learn.microsoft.com/en-us/sql/relational-databases/performance/display-an-actual-execution-plan?view=sql-server-ver15) . Usually in this mode the MS SQL server suggests the indexes needed to speed up the query execution.
Dmitrii Naumov
  • 1,692
  • 8
  • 17