Performance Improvements / Custom Indexes - Best Practices

  • 12 May 2023
  • 4 replies
  • 167 views
kyoung14
Rank
Userlevel 3
Badge

Just looking to see what best practices others have around improving performance of GI’s, pivots, reports inside of Acumatica.  We have a client that does a lot of transactions (Project trans, Inventory trans, Sales Order trans).  We’ve created many custom GI’s, pivots and reports (some utilizing custom DACs) for their data reporting needs.  As their data and transactions grow, some of these are getting very slow to run.  Maybe some of these are written poorly but many I don’t think there would be a better way.  Is anyone out there creating custom indexes to address performance issues?  I know we can use Request profiler in Acumatica to identify problem queries or SQL tools if we have a local test instance.  Any best-practices or “gotchas” anyone wants to share on custom indexes and Acumatica?  I know there are requirements around multi-tenancy (i.e, include CompanyID in the index).  Any other thoughts or tools people have found helpful?

 

Or are people just moving to 3rd party data warehouse and reporting tools that perform better when they get to this point?

 

Thanks in advance for any thoughts!

4 replies

Dmitrii Naumov
Rank
Userlevel 7
Badge +5

In general I think it’s fine to create custom indexes. 

However, several things to keep in mind:

  1. Every time you create a new index, you slow down the insert/update/delete operations for the table. So, it should only be done if the slowness in data insertion/deletion is acceptable for having faster selects.
  2. Make sure you index names start with Usr. Make sure your index names are unique and will not intersect with the default ones. Good way to do this is to use ISV prefix (if you have one) in the index name (after the Usr).
kyoung14
Rank
Userlevel 3
Badge

@Dmitrii Naumov  - thank you for your feedback!  Yes, there are trade-offs in deploying more indexes as you mention as well as additional space usage, etc.  The trick is how best to weigh the cost and benefit...which will always be case-by-case.

 

A
Rank

@Dmitrii Naumov - we have run into trouble in the past where custom indexes don’t get preserved in an upgrade.

 

Is there a way to preserve custom indexes when upgrading?

Dmitrii Naumov
Rank
Userlevel 7
Badge +5

@annie92  please use ‘Usr’ prefix for your index names

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