Question

Question: Best Practices for refactoring general inquiries to increase performance when joining multiple tables

  • 8 April 2024
  • 9 replies
  • 69 views
jcavanagh
Rank
Userlevel 1

Hello, 

At our company, many people need to access the details section of a sales order, as critical for doing our jobs. We have a generic inquiry which pulls data from multiple tables, which impacts performance quite significantly, by taking often 45 seconds or more to load the query. This may not sound like a lot, but when dozens of people run this query dozens of time each day, it creates a lot of friction.

I suspect that there can be significant gains made to performance, with SQL best practices such as not scanning multiple tables, or other system level ways by using lazy loading, database caching, or using a  concurrent thread pool. 

Please direct me to a relevant thread or resource. If not, please respond and I will provide instructions on how to recreate the behavior of the query. 

Efficiency is so key here, because it is one of our largest pain points with the ERP since so many people rely on this general inquiry, it can save the company thousands of dollars each year along with the consternation of having to wait for critical information to load in the ERP.

 

Thank you for reading my post

取天下常以无事，及其有事，不足以取天下。

9 replies

aaghaei
Rank
Userlevel 7
Badge +8

@jcavanagh With due respect, this community is meant to gather people around so they can help each other. If you have questions ask your question. If you have prepared something that can benefit others and you are willing to share then share it. I don’t think here is a right place for sales pitches.

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
jcavanagh
Rank
Userlevel 1

This isn’t a sales pitch. I’m not selling anything unlike other absentee consultants who’ve left us out to dry in the past by not supporting the code they wrote for us.

I’m looking to improve performance, as a developer, when there clearly is a performance bottleneck in how we constructed one of the general inquiries.

取天下常以无事，及其有事，不足以取天下。
jcavanagh
Rank
Userlevel 1

It shouldn’t take anywhere near 45 seconds to query several tables if I refactored the general inquiry, which is my hypothesis

取天下常以无事，及其有事，不足以取天下。
aaghaei
Rank
Userlevel 7
Badge +8

Well, if you have tips to share then share. 

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
jcavanagh
Rank
Userlevel 1

I’ll follow up with performance bench marking.

取天下常以无事，及其有事，不足以取天下。
Chris Hackett
Rank
Userlevel 7
Badge

Hi @jcavanagh  - @lauraj46 has done a community webinar on generic inquiries and might be able to offer some advice. You may need to supply further information for her.

Chris ~ Let us know how we're doing: https://forms.office.com/r/xmJUdZ1QE5 - A member helped you, please *Like* their post. A member provided an answer to your question, please mark it as *Best Answer*.
jcavanagh
Rank
Userlevel 1

@Chris Hackett 

 

Thank you for the advice, you’re like a map of Acumatica! I saw her posts and it seems like her brain works in a fascinating way with an intuitive understanding of the data structures.

取天下常以无事，及其有事，不足以取天下。
aaghaei
Rank
Userlevel 7
Badge +8

@jcavanagh I guess I misunderestood the original post. My personal experience with GI and API calls when dealing with joined objects is because of the application layer bottleneck. As a programmer if you have ran profiller in your dev environment on Acumatica GIs you possibly have noticed it is really difficult to read the queries generated bu the engine and they are very combursome and the result loaded from the database includes lots of useless fields in the GIs we create. You can try multiple approaches: 

  1. For these heavily used inquiries create your own graph, write the Selects you want and using the Linq limit the filed sets to only those you need, to avoid unnessary load on application.
  2. You may try creating your own projection DACs based on the result set you want and then use the projection DAC in your inquiry but I am not so sure this will be very different than GI itself.
  3. Seems you are developing your internal customizations and not for certification purpose. So a third option that I believe will be the most performance efficient approach is to push the whole load to the Database level. You can create a View by joining the tables (Do NOT forget to add CompanyID join in your query) you need but in the select list only include the fields you need in your GI. Then from this view (Acumatica platform will treat it like a table) auto-create a DAC, append the attributes of the DAC and its fields and then use this DAC in your GI. I believe this will be best performance you can get from the engine.

By the way, shame on those who have let you down by walking away.

Reza Aghaei CPA, M. Acc. & Acumatica Customization - If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
jcavanagh
Rank
Userlevel 1

Thanks a lot @aaghaei, you are forgiven! I’ll be plugging away at this when I have time, since it’s not my primary responsibility at work, as an analyst. I’ll start with suggestion number 1

取天下常以无事，及其有事，不足以取天下。

Reply


Terms & ConditionsCookie settings
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