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.
Page 1 / 1
@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
@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 :(
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.
Thanks a lot, Gabriel, I will check this in production and keep you posted on the status.
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?
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.
@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. :(
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.
@ChandrasekharM there’s nothing in the Conditions tab beside an optional Branch filter -- that’s not going to have much impact.
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.
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
@Naveen Boga we’re you able to find anything that helped or an approach that improved the performance? We also are in the middle of troubleshooting performance within our system and I’m looking for any insight that may help us.