Performance Practices in Price Setting
- Michal Smíšek
- Jan Panoušek
- Stephen DeMondo
- Jana Volencová (Deactivated)
Data Volumes and Growth
The number of line items affects performance of the queries that filter or sort fields other than primary key. This has an impact on calculation logics querying line items (e.g. in dashboards) and on UI responsiveness, such as opening a line item or paging while having sorting by a field.
A typical recommended Live Price Grid or Price List size is tens of thousands of line items, potentially reaching a few hundred thousands of line items. The size of a single price grid or price list should not exceed 1.000.000 line items, otherwise the UI will become unusable for the user because of HTTP timeouts.
Price Grids
When designing the price grids and using the ‘Dynamic item mode’, check with the customer that the size will not exceed the recommended limits.
The recommended practice is to try to break large price grids to smaller ones, e.g. by product hierarchy or by teams responsible for prices maintenance.
On the other hand, the performance of a large number of price grids will get impacted by scheduler. Since if you run all price grids at once, they will get queued in the JST background job queue and therefore it will take up to 1 minute (assume 30 secs in average) to be picked up by the scheduler from ‘pending’ to ‘processing’ status. For example, for 100 price grids it would take up to 100 * 30 sec = 50 minutes just to start processing them. But in reality, the overall time will fluctuate depending on how many other jobs are processed simultaneously on a given cluster.
Price Lists
Price lists are created repeatedly and therefore the amount of line items can grow significantly over time. It is recommended to instruct the customer to delete the old price lists once they are not needed. Alternatively, create a Calculation Flow that will delete old price lists using a bound call.
Querying and Caching Data
Querying data is the most costly operation in logics, whereas conditions and mathematical operations in Groovy usually do not cause a performance problem.
Since the calculation engine will run the logic for each and every line item, you should not execute a query for each and every line item, but for the whole calculation or for the batch of records being calculated by a calculation node at a time.
Data for the Whole Calculation
The data not related to SKU (such as Company Parameters, some Data Sources, Customers, Customer Extensions) are typically queried and cached (stored) in api.global. The number of tables and records should not be too big, e.g. try to not exceed 200.000 in total.
Tip: You can generate the code for caching using a template in Studio. Either create a new dedicated element using the context menu Cache > Global Cache under the new element button or type “pfxGlobalCache<enter>” directly in the Groovy editor, typically in the “Global” element in the very beginning of the logic.
Remember that api.global cache is populated on every calculation node. So even though you might think those queries are executed only once, in reality, there will be multiple runs, depending on the number of nodes.
Data for the Batch
The data related to SKU (such as Product Extensions or Datamart with sales transactions) should be queried once for a set of SKUs (= a batch) being calculated by calculation node at a time. The query should fetch records relevant for the SKUs in the batch and cache them into api.global. See more in the Javadoc for api.batchInfo() or use functions in the SharedLib.BatchUtils library.
To further improve the performance, the new “Query API” with JOIN support, available in experimental mode since 13.0 Rampur release, can further boost the performance significantly. The idea is to do a single query and join multiple tables instead of individual isolated queries.
Data for the Line Item
Making query for every line item is not recommended. It will probably cause no harm for very low numbers of line items, however it is not a recommended practice.
Querying Price Across Multiple LPGs
If the SKU can be present in multiple LPGs, you will most probably need to make a single query across many/all LPGs. This will be possible only if you allocate the attributes to the same elements. See how you can configure these mappings: Defaults for Price Lists and LPGs | Preferred Field Mappings
Use Distributed Calculations
Calculation of a price list or price grid can scale very well with a growing number of calculation nodes if you use distributed calculations. Therefore check ‘Allow distributed calculation’ in the Set parameters tab when configuring creation of a price grid or price list.
UI
If the price list or price grid is created, no preferences exist by default and the default sorting of the records is by ID field. The sorting is important for paging of the items. If it takes a long time to open a price list/grid screen with the items due to high number of records or if you get a timeout, you may need to create a default preference where you set the default sorting by “sku” field for simple price grids/lists and “sku, key2” for matrix price grids/lists. This will enforce the corresponding database index to be used for paging. You should also inform the users to not change the default sorting since this may have impact on performance.
Default Database Indexes
TypeCode | Indexes |
|---|---|
PLI | [pricelistId, sku] [sku] |
PGI | [priceGridId, sku] [sku] |
XPLI | [pricelistId, sku, key2] |
XPGI | [priceGridId, sku, key2] |
Found an issue in documentation? Write to us.