Acumatica 26 R1 – Recommended Database Optimization Techniques for Customizations

Question

Hi everyone,With Acumatica 26 R1, I’m interested in understanding the current best practices for database performance optimization when developing customizations.For custom solutions that work with large volumes of transactional data, what database optimization techniques are recommended in 26 R1?In particular, I’d like to get the community’s thoughts on:When should we use BQL, Projection DACs, Generic Inquiries, or SQL Views for better performance?	Are there any new improvements or recommendations in 26 R1 around query performance?	What are the best practices for optimizing complex BQL queries with multiple joins and filters?	When is it appropriate to create custom database indexes for a customization?	Are there any concerns or limitations around adding custom indexes from an upgrade/maintenance perspective?	What are the recommended approaches for processing large volumes of records without impacting the UI or overall system performance?	Are there any specific Acumatica tools or techniques you recommend for identifying slow queries and database bottlenecks?	Has anything changed in 26 R1 that developers should be aware of when optimizing existing customizations?It would be great to hear from Acumatica developers, MVPs, and ISVs who have already worked with 26 R1 and have experience optimizing customizations in high-volume environments.Thanks in advance for sharing your recommendations and real-world experiences!

Naveen Boga · Answer

​@John Masi74For Acumatica 26 R1 customizations, I think database performance should be approached from both the BQL/application layer and the database layer.Some areas I would consider are:Avoid retrieving more records/columns than are actually required.Review complex BQL joins and ensure appropriate filtering is applied as early as possible.Use Projection DACs where they provide a clear performance benefit for frequently queried data.For large Generic Inquiries, review the joins, filters, aggregates, and calculated fields before considering a custom database view.Avoid executing database queries repeatedly inside loops; retrieve the required data in a single query whenever possible.Use PXProcessing/long operations appropriately for large-volume processing rather than processing everything in a single UI request.Review SQL execution plans for queries that are consistently slow instead of assuming that adding an index will resolve the issue.When custom indexes are required, make sure they support the actual filtering/joining patterns and don't introduce unnecessary write overhead.Be cautious with database-level customizations so they remain upgrade-friendly and don't conflict with Acumatica's database schema or upgrade process.After an upgrade to 26 R1, I would also compare the performance of critical custom queries/GIs against the previous version, since framework or query-generation changes can affect execution plans.For ISV/customization development, I would generally prefer optimizing the BQL/projection/query design first and use database-level optimization only when there is measurable evidence that it is needed.Hope this info helps!

Sign up

Social Login

Login to the community

Social Login