Performance Issue on the Generic Inquiry


Userlevel 7
Badge +17

Hello Everyone,

We have created a Generic Inquiry screen and it is taking 2 minutes in production (where as I have 1256007 records) and this GI is loading very fast in my local machine as I have less number of records.

Attached this GI for your reference, can anyone help me to optimize the performance with this GI, when we have huge data.

 

 


11 replies

Userlevel 7
Badge +10

@Naveen B, do you have access to the database with SQL? I would recommend looking at the execution plan.

I ran it on my end with the Demo DB and didn’t find anything problematic -- query looks relatively simple.

P.S. You do have unused tables like INItemCost and you could probably get rid of the grouping

 

Userlevel 7
Badge +17

@Gabriel Michaud, I do not have access to SQL and I’m NOT able to identify the root cause with the DEMO data.

 

Sorry, I was unchecked the INItemCost fields in Result Grid, but actually that INItemCost table is required to display TranUnitCost and another field, and grouping I was removed but no difference in performance :(

 



 

Userlevel 7
Badge +10

You can use INItemStats instead of INItemCost -- it will be faster. Make sure to join on InventoryID and SiteID. I also think you can get rid of the groupings.

Please check updated GI attached and let me know the results.

Userlevel 7
Badge +17

Thanks a lot, Gabriel, I will check this in production and keep you posted on the status.

Userlevel 7
Badge +17

Hi, @Gabriel Michaud, I have verified this in production, and performance is improved a little bit like 120 seconds to 70 seconds. Currently, GI is taking 70 seconds to load the records. The client has still concern about the performance. 

All key fields are properly joined, I don’t think we can apply any indexes here. Not sure still taking 70 seconds to load the records.

Please suggest, if any other ideas and thoughts to improve performance?
 

Userlevel 7
Badge +10

Is this hosted on acumatica.com? If so you can ask their support team for help profiling the query. With the data volume that you have having access to SQL for profiling is critical. It could also be related to index fragmentation, which can’t be troubleshooted without this access.

Userlevel 7
Badge +17

@Gabriel Michaud Yes, it is hosted on acumatica.com.

I already raised a support ticket but they wanted to charge for the investigation on this performance issue. :(

Userlevel 7
Badge +9

Hi Naveen,

Adding non-clustered index to the fields added to the” Conditions” tab of the generic inquiry screen will help to improve the speed of the Query and hence results in extracting the data fastly. This could be one of the points that you add to your case. Hope this helps to improve.

 

Userlevel 7
Badge +10

@ChandrasekharM there’s nothing in the Conditions tab beside an optional Branch filter -- that’s not going to have much impact.

Userlevel 7
Badge +17

Hi @ChandrasekharM  There is only one filter I’m using i.e. Branch and for that as well I have created Non- Clustered Index but still there no impact on the performance improvement as Gabriel suggested.

Userlevel 2
Badge

Has this been solved? I have a similar issue, only 24k records and it takes 2 minutes for a search result to come out, only uses 5 tables, on premise installation though but other GI search appears in 5 seconds or less even when there are more records in the table…

mrivera67

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved