Designing and managing your GIs in an efficient, systematic way can improve site performance
Designing and managing your generic inquiries (GIs) in an efficient, systematic way can reduce the amount of site resources they use, while also giving your users a better experience.
We recommend you regularly review your GIs to make sure they're as fast and efficient as possible, including:
- Avoid modifying the primary list GI versions, so that extra data fields are only used when needed.
- Use filters (especially parameter-driven filters) to improve efficiency.
- Create function-specific GIs for specific uses, rather than larger multi-purpose GIs.
- Design more efficient joins by using INNER JOINS and avoiding calculated fields.
- Reduce your use of quick search.
Avoiding modifying primary list GIs
Primary list GIs, such as Sales Orders (SO3010PL) or Invoices (AR3010PL), are among the most widely used screens in the the system. When extra fields are added to these GIs it has a disproportionate effect on site-wide performance.
If you want to add fields to a primary list GI, we recommend you make a copy of the GI and make your changes to that. This way you can build a GI with more specific data for special purposes (e.g. a weekly report) without affecting overall performance.
Adding filters to GIs
Filters can reduce the amount of data returned by a GI, improving the performance of that GI as well as overall site performance. Thoughtfully filtering the most commonly used GIs across your site is an important tool in managing performance.
Simple filtering settings are available to site administrators and other power users, and MYOB Acumatica consultants can set up parameter-driven filters, which further increase efficiency.
For an example on how to set up filters across your site, see these case studies:
- Adding a basic default filter to Sales Orders (SO3010PL) to improve performance
- Adding a parameter-driven filter to Sales Orders (SO3010PL) to improve performance
For more information, see:
- Filtering and Sorting in MYOB Acumatica, for help for end users.
- Managing Advanced Filters, and Using Parameters and Filters, for implementation guidance.
Creating function-specific GIs
As GIs grow and access more data, site-wide performance can suffer. Taking a function-specific approach to creating GIs breaks them down into smaller chunks with a narrow scope and clear use case.
Consider creating separate GIs for functions such as:
- OData retrieval
- in-product reporting
- dashboarding.
While each function may access some of the same fields, optimising each GI for the specific purpose reduces unneeded database activity, while also avoiding overloading users with information that isn't relevant to their needs. It also simplifies maintenance, as each GI can be managed and updated independently of the others.
Joining GIs efficiently - InnerJoin and precomputed fields
If not done well, joining GIs can introduce inefficiencies that degrade performance. The InnerJoin clause and precomputed fields are two techniques you can use to reduce this effect.
Using InnerJoin vs LeftJoin
LeftJoin is a clause commonly used to join two GIs. This type of join returns all the results from one table set, and the matching results from the other. InnerJoin improves on this (in most circumstances) by returning only the matching rows from both tables.
Where possible, use InnerJoin to join GIs, as it is the faster and more efficient indexing method.
For more information, see Managing Generic Inquiries: Data from Multiple Data Sources: General Information.
Managing calculated fields in joined GIs
Using calculated fields in joins, particularly those marked as DBScalar or DBFormula in the data access class (DAC), can lead to poor SQL query performance.
We recommend you use precomputed fields, as they help the query optimiser make more effective use of indexes to make the GI faster.
Reducing the use of quick search
Quick search is, as the name suggests, very quick, at least from a user's perspective. However, the more fields that quick search can access, the more SQL database queries it runs, which can contribute to slower system-wide performance.
We recommend you limit the number of fields to only the most relevant, frequently-used fields.