Skip to main content

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!

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).

@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.

 

@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?

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

Reply


Terms & ConditionsCookie settings