Performance Use Cases - Lessons Learned / Dos and Don'ts

Analytics

Incremental DM refresh

The only way to have a true incremental DM Refresh is to make sure that ALL underlying Data Sources are also set as incremental. For every change in the Data Source the Datamart also needs to be adjusted on a refresh. If the 'wrong' Data Source gets overridden (is not incremental), it can trigger up to a whole update of the whole Datamart on the next refresh. 

Performance

Calculation Jobs on a Data Source should only change relevant data and not override the whole Data Source (i.e. they should work incrementally). Internal Copy jobs should be set as incremental. 

Price Setting

Processing quotes with many (100+) line items

Various actions on quotes will trigger a full recalculation, i.e. submit, save, recalculate & workflow preview. The pricing logic will execute on every line item. Depending on the performance of the logic, this can end up taking quite some time. The same applies to header logic for the same quote which is executed twice (once before and once after the line item processing).

Performance

There is currently no distributed calculation for quote line items. Hence the solution is always to improve the processing logic, for example by batching and caching.

Logic

Recurring timeouts when trying to cache large data sets during logic execution

Caching is used more often in logics to improve logic performance. In some cases, large data sets are cached. While this can impact the memory, it also can take quite some time for the data to be returned by the databases. On an already loaded system, this may be longer than expected and exceed the timeout override for the logic element doing the work. In this case the data is not cached and the next item processed will trigger again the work to build the cache. On top of this, the database continues to process the queries. You can end up with an overloaded database. Growth of the DB data tables can impact the performance of queries AND indexes should be reviewed.

Performance & Stability

The 'timeout override' value for a logic element should have some buffer for situation where "other" work is happening in parallel. For known and recurring jobs generating such cache loading queries, the DB indexes should be reviewed by Ops.

Logic

Logic items should always explicitly return null when Display Mode is set to 'Never'

A logic item should always return null if the Display Mode is set to 'Never'. If the data from this logic element is required later in the logic, use caching (local, global or shared) to preserve it. There is a server side performance cost to store the data returned by a logic element. And as the amount of data grows, this cost can become very apparent! 

Performance

A logic item should always return null if the Display Mode is set to 'Never'. If the data from this logic element is required later in the logic, use caching (local, global or shared) to preserve it.

Logic

Batching and/or caching in logic

Batching and caching should be used where possible to avoid unnecessary duplicate work. As soon as there is a situation where multiple items are processed sequentially by a logic, each logic element should be reviewed carefully to see if it can benefit from batching and/or caching.

A typical example is a PA query that filters on the current SKU. Instead prefer an approach where the PA query is done with a filter including all the SKUs in the current batch. This decreases by a factor of 200 the number of PA queries for the overall job (assuming that more than 200 items are actually processed and the batch size is not changed from the default size of 200). Caching can be used for any data that can be reused as-is for a following item to be processed.

Performance & Stability

Where it makes sense, use batching and/or caching.

Analytics

DM normalization level

While the normalization level for a Datamart may default to 'High', this should NOT be used ESPECIALLY with large DMs, i.e. 1M+ records. Please prefer a normalization set to 'Medium' or 'Low'. The lower the level, the faster the response times will be for queries. Updates to the DM are slower the lower the level.

Performance

Do NOT use Datamart normalization level 'High'.

Logic

Data issues & logic

Do not assume that data will always correctly follow the agreed structure. Check for errors to ensure that your Groovy code does not error out. The latter can have ill-effects such as caching not being successful. In the case of a batch job such as a PG or PL, this can cause the caching logic to be executed upon every item instead of only for the first item of the batch!

Performance & Stability

Always do error handling in your Groovy code.

Logic

Streams should be closed ASAP

When streams are used, they should be closed ASAP. Leaving a stream open to do other work increases the risk of stream leakages which can ultimately take down the server. Basically, the streams should be opened, results saved and the stream should then immediately be closed.

Also, try-catch blocks can be added to ensure that an error will not prevent the stream from being closed correctly.

Another approach to ensure closure is to use the withCloseable method, i.e.:

def s = api.stream("C",null)?.withCloseable { it.collect { it.attribute1 } }

This approach ensures that the data is collected and stream closed immediately after that.

Stability

Close streams ASAP, do not wait for dependent work to be completed before closing the stream. Or use a try-catch block to ensure the stream is closed. Or use the withCloseable method to ensure closure of the stream.

Analytics

Logic

Processing PA data results

When processing large PA result sets, prefer an enumerator approach, i.e.:

for (item in data) {}

With this approach you have immediate direct access to each data entry and this does not required any index lookups.

Performance

Enumerator based approach for processing PA data results is most efficient, especially when processing large data sets.

Logic

Prefer 'api.stream' to 'api.find' when processing large data sets

'api.find' can be configured to retrieve max 10k results. Attempting to retrieve more data than that will result in multiple calls to 'api.find'. If your data set is really large, then prefer 'api.stream'. BUT ensure that you return raw data by passing a list of fields requested. This allows the Java engine to successfully garbage collect the raw data that has already been processed by your logic.

Performance and Stability

'api.stream' is much more efficient for processing large data sets BUT ensure you call this with a set of requested fields to allow the Java engine to garbage collect successfully.

Analytics

'Truncate & Refresh' vs. 'Incremental Refresh' of DM tables

Incremental refresh of Datamart tables can be time consuming when an underlying Data Source table has been heavily modified. A typical example is that 33% of all Product entries are modified, hence all DM entries related to these product changes have to also be updated. This can end up in refreshing a large portion of the DM table. In this kind of situation, prefer a 'Truncate & Refresh' approach.

Performance

'Truncate & Refresh' should be used when Data Source changes result in large DM refresh operations.

Logic

Partial processing of stream results

In some cases, there may be a need to process only a subset of the stream results. This is sometimes implemented as follows:

while (stream.hasNext() && count < batchSize) {

Currently this triggers a hikari pool usage issue on the backend and is being investigated. For now, make sure you process ALL stream data.

Stability

Make sure to process ALL stream data.

Price Setting

Analytics

Default prefs should be well set up

When viewing data from large tables (i.e. PL, PG ...), it is very important to make sure that the default prefs make use of indexes to return data quickly. For example, in the case of PG, it should sort on either the 'sku' or 'key2' column. Not having sorting defined in the pref will result in sorting by 'id' which is not efficient. Also, end users tend to use the default pref as a starting point, so if it is slow adding further filtering may not help.

Performance

Prefs on large DB tables should include sorting on key columns (i.e. indexed columns).

Analytics

In MEM vs. file based DL Flush jobs

Data Load Flush jobs can be tuned to be more efficient by making use of server side params. These force the server to process the job in memory as opposed to file based. These settings also force the job to be batched as Memory is limited.

Performance

Switch to using 'inMem' DL Flush jobs, this will improve the overall performance of these jobs.

Price Setting

Improve 'api' only user based requests

When connecting to Pricefx with an 'api' only based user, it will help to make use of JWT (JSON Web Token) authentication as opposed to traditional 'username/password' authentication. Pricefx provides an endpoint to create long-running JWTs: "/accountmanager.getjsonwebtoken/{expiry}". The customer should put in place a process to do self-renewal of the JWT.

Performance

Switch to JWT based authentication. Make use of long running JWTs.

Analytics

Dashboards and expression based DM fields

Dashboards, depending on their complexity, can generate complex PA queries. This complexity will be even bigger if some of the fields included are expression based. Also dependencies of expression based fields can make things even worse. The performance of the PA query execution will decrease in these kinds of situations. One option to make things faster is to move the expression based calculations into a Data Load Calculation job. This way the expressions are executed once and results stored directly in the DM. This makes for faster DM queries.

Performance

Where possible avoid expression based DM fields and calculate the values in a DL Calculation job instead.

Rebates

Rebate agreements

Rebate agreement save operation triggers a syntax check logic execution on ALL associated rebate records. Each RR contains a field with the agreement level input parameters. 'Configurator' input parameters can be heavy if not designed carefully (i.e. 'find & select' is better than a long list of entries with checkboxes). Aborting the syntax check should be done using 'api.abortSyntaxCheck' instead of 'api.abortCalculation'. Finally, prefer the usage of 'Payout' records as opposed to 'Rebate' records where possible.

Performance

'Configurator' fields should be designed to be lightweight. Use 'api.abortSyntaxCheck' instead of 'api.abortCalculation' when needing to abort a syntax check logic execution. Prefer the usage of 'Payout' records (over 'rebate' records) where possible.

Logic

IN clauses with long list of entries

Generating a DB request containing a long list of items within an IN clause has multiple negative impacts. It can be slow at the DB execution level as selecting a good index will be tricky for the optimization engine. It will impact the MEM usage within Pricefx as caching at the hibernate level may retain this even after job completion.

Performance and Stability

Avoid DB queries with a long list of items within an IN clause.

Optimization

Executing Optimization logic

Optimization logic can be triggered from the Price Setting module. This ends up executing the 'Segmentation_Eval' logic associated to the Optimization model. Very often we see this executed for every line item. Certain params are passed to this logic and these may not be unique for every line item. Hence it is important to find out what is the percentage of unique combinations of params to this logic. And make use of caching when the ratio of unique combinations is low.

Performance

Avoid duplicate calls to Optimization logic with identical params. Make use of caching to avoid unnecessary calls.

Analytics

Dashboards and persisted columns

DM queries generated by dashboards will try to pick up data directly from the DM table. However, if a particular field is not persisted in the DM then the system will generate a query containing a left outer join to the underlying table. If the latter is small, this will be possibly unnoticeable, but if it is large (for example, transaction DM to DS left outer join) then this will negatively impact the query.

Performance

Ensure that all DM queried columns are persisted

All

Data sizes per storage type

Company Parameters vs. Product/Customer Extensions vs. Analytics. There are many places where data can be stored. Company Parameters are ideal for generic data with at most 6 keys. PX/CX tables are primarily used to store product/customer related information where the single key is the SKU or customer ID. Also, the PX/CX tables cannot have multiple entries for a single SKU / customer ID. We generally recommend not to go above 1M entries in CP/PX/CX tables. But this is not a hard limit in any way. Analytics tables can grow much larger.

Stability

The selection of a storage container for your data has to be thought out carefully. This does not depend ONLY on the number of rows. Number of columns is another factor. Also, the access patterns to the data can highly influence the decision to go with one or the other storage containers. If in doubt, reach out to peers or knowledgeable people.

API

Pricing based API requests

More and more, customers make use of our pricing API based capabilities and sometimes expect unrealistic times as these requests are generally included in a realtime process. Agreeing to a response time or throughput KPIs can be very dangerous. There are solutions in Pricefx to reduce the logic execution times; this includes making use of service threads, caching (inMem, sharedCache or even Yugabyte) but this has to be thought out carefully as there are pros and cons for each approach.

Performance

Do not agree to a response time or throughput KPI without having prior approval from the Performance team.

Price Setting

Quote Processing

Quote processing in Pricefx is currently still single threaded. The complexity of header and line item logic can influence drastically the total recalculation time for the quote. Hence it is key to get it right and not do any unnecessary work. For example, do not do the same work at line item level over and over, instead do this as header level (pre-phase) and then pass the results to the line items. There are many tricks that can be used to reduce the logic processing times.

Performance

Make sure you understand the logic involved in your quote processing as this has to be as efficient as possible.

Logic

api.find

“api.find” is a generic method for retrieving data from the database. It can be called with various parameters. One of them is key when it comes to MEM management and performance. When called without a list of columns, the request will return full objects, for example LPG or Contract objects. These can be quite large, especially if they contain many line items. On top of this, the objects loaded will be checked for “dirtiness” during hibernate flush operations. Hence, unless you explicitly need full objects, you should avoid this by specifying the list of columns of data to be returned.

Make sure you always specify columns to be returned by your api.find operation unless you explicitly need the full object