Bottleneck

In the stock events design, application state is immutable.

Once saved, stock cards and stock card line items won’t be updated, ever.

Stock on hand, by its nature, is a mutable thing. So in the stock events design, it’s not persisted, it’s recalculated instead.

So that means whenever users ask a question like: “What is my stock on hand of some stock card at some facility?”, stock management service must read the application state of that stock card along with all of its stock card line items, then conduct recalculation.

This is fine when data volume is not very high.

But when you accumulate data over time, the recalculation will take longer and longer.

So, the performance bottleneck of stock management service would be: all the operations that incur recalculation of stock on hand.

Threshold

As we have tested, on a AWS m4 large machine, requests would start timeout when:

There are 100 stock cards at the same facility, and each stock card have about 4000 line items

Or

There are 1000 stock cards at the same facility, and each stock card have about 300 line items

Impacted business scenarios

Pretty much every web page of stock management UI incurs recalculation of stock on hand.

• The stock on hand(stock cards list) page, which is obvious.

• The adjustment page. Users can see the current stock on hand before they make an adjustment.

• The issue page. Same as above.

• The receive page. Same as above.

• Physical inventory page. Also, before users confirms, they can see current stock on hand.

Factors of the bottleneck

Recalculation of stock on hand involves

1 Reading stock cards and stock card line items from the DB

2 Perform + and - operations on entries that have been read from the DB

The + and - operations takes very little time, almost ignorable comparing to time taken to read DB. So the bottleneck is essentially I/O intensive, not computationally intensive.

Then the I/O part itself can be broken down into two factors:

1 Reading stock cards

2 Reading stock card line items that are associated with those stock cards

The next section will try to find a solution by limiting those two factors.

Possible solution

Time taken ≈ (stock cards amount) x (stock card line items amount)

This formula should be clear, given the previous section.

So, if we can control those two multiplying factors, we can get time taken under control.

Control stock card amount

We can not control how many stock cards a facility is allowed to own.

But we can control how many stock card a user is allowed to retrieve per request.

This has been done by implementing the /api/stockCardSummaries. It supports pagination.

The front end can specify a page size when calling this back end api.

Control stock card line items amount

We can not control how many stock card line items is allowed to be created for each stock card.

But we can control how many stock card line items are needed in recalculation, by taking snapshots of stock on hand.

Snapshots

A snapshot is the stock on hand value of a certain stock card, at a certain point of time.

The system would take a snapshot of a stock card, whenever the stock card accumulates a certain number of line items. (I will call this “snapshot interval” for brevity.)

For example:

There is a stock card, called A.

It has 100 stock card line items, accumulated over the past 10 weeks, at a rate of 10 items a week.

If the system had taken snapshots for every 30 line items, they could look somehow like this:

End of week 3, stock on hand is 10

End of week 6, stock on hand is 25

End of week 9, stock on hand is 5

Now if we were to do recalculation of stock card A, we only need to:

1 read the latest snapshot, which is number 5, at the end of week 9

2 read the stock card line items that are not covered by any snapshots yet, which are the 10 line items that were created after week 9

If both control measures have been taken, the formula would turn from:

Time taken ≈ (stock cards amount) x (stock card line items amount)

to

Time taken ≈ (page size) x (snapshot interval)

If in the front end, we were to limit page size to a certain number, and in the back end limit snapshot interval to a certain number. Then this formula will turn into a constant.

Updating snapshot

Because we allow users to fill in missing stock movements(for example: in the system, I have already entered stock movement of today, then I realized I forgot to enter a few things that happened yesterday), snapshots need to be updated.

This can be done by examining the occurred date during stock event processing. If the occurred date predates any snapshot that has already been taken, then we need to update all the snapshots that are taken after the occurred date involved in this new stock event.