Submit Stock Transaction is very slow after upgraded to V12

ERPNext
1

I’m encountering poor performance after upgraded from V10 to V12.
Submitting stock transactions via Stock Entry, Delivery Note, Sales Invoice is very poor…it takes 20-30 secs each. It doesn’t happen for other than stock transactions.

My server is 8GB RAM, 4 cores. Innodb_buffer is set to 6GB, 4 gunicorn workers.
It should be enough…in V10 everything was working very normal

show full processlist on mariadb console showed every stock transaction doing this query:
SELECT item_code, stock_value, name, warehouse FROM tabStock Ledger Entry sle WHERE posting_date <= '2020-07-31' AND warehouse = 'XXX' ORDER BY timestamp(posting_date, posting_time) DESC, creation DESC;

It iterates over all warehouses. tabStock Ledger Entry has 1M+ records currently
Also added index for (posting_date, warehouse) but doesn’t help

Is there special database config for V12? Anyone has clues? Thanks

2

The bottleneck is here using %prun doc.submit() in pyhton console.
this method query up whole records <= current posting date hence it loads huge amount of records

/erpnext/stock/utils.py in get_stock_value_on(warehouse, posting_date, item_code)
     65                 WHERE posting_date <= %s {0}
     66                 ORDER BY posting_date DESC
---> 67         """.format(condition), values, as_dict=1)
     68
     69         sle_map = {}

Original query:

SELECT item_code, stock_value, name, warehouse FROM `tabStock Ledger Entry` sle WHERE posting_date <= '2020-08-02'  AND warehouse = 'bom' ORDER BY timestamp(posting_date, posting_time) DESC,creation desc

Modified query (remove timestamp function in order by clause):

SELECT item_code, stock_value, name, warehouse FROM `tabStock Ledger Entry` sle WHERE posting_date <= '2020-08-02'  AND warehouse = 'bom' ORDER BY posting_date DESC, creation desc

Also, I did a test by modifying the query above to not order by timestamp(posting_date, posting_time) . if I do the query using mariadb console then the query took less than 2 sec to load almost 300.000 records of SLE

but still the submission of any stock transaction takes 20-30 sec.

Anybody experiencing the same or has clues? Thanks

3

I am experiencing this too, how did you solved this??

I am taking like 14-18 secs to send an POS invoice! I just discovered it by the slow_query log

4

Actually still not found solution. Anybody can help pointing out the issue here?
I removed order_by timestamp but still it took 20-30 to submit stock transactions

slow_query_log showed:
# Query_time: 27.838087 Lock_time: 0.000044 Rows_sent: 1077415 Rows_examined: 2154830
# Rows_affected: 0 Bytes_sent: 62185866
# Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: Yes Filesort_on_disk: No Merge_passes: 46 Priority_queue: No
SET timestamp=1624295832;
SELECT item_code, stock_value, name, warehouse
FROM tabStock Ledger Entry sle
WHERE posting_date <= ‘2021-06-22’ AND warehouse = ‘ABC-NS’
ORDER BY posting_date DESC, posting_time DESC, creation DESC;

Actually I removed the order by timestamp but it didn;t help.
using V12.8

5

This might be consequence of Perpetual Inventory.
Try this in a Dev/Test environment:

  1. Find this file:
    ./apps/erpnext/erpnext/accounts/utils.py
  2. In that file, find this function:
    def check_if_stock_and_account_balance_synced():
  3. At the beginning of that function, just add a return statement.

If that speeds things up? Then it’s because of Perpetual Inventory checks and balances.

6

didn’t find this function??

I’m using v12.8

7

My apologies, that was v13 code.
I’m not sure if/where v12 has anything equivalent.

8

removed order_by clause. it took 11sec to just submit stock transactions which is still bad.
Anybody having same issue on v12?
I tried adding some composite indexes but still no luck

9

def get_stock_value_on on /erpnext/stock/utils.py:

stock_ledger_entries = frappe.db.sql("""
		SELECT item_code, stock_value, name, warehouse
		FROM `tabStock Ledger Entry` sle
		WHERE posting_date <= %s {0}
			and is_cancelled = 0
		ORDER BY timestamp(posting_date, posting_time) DESC, creation DESC
	""".format(condition), values, as_dict=1)

Anybody can help how to improve this query? This function is called from get_stock_and_account_balance on erpnext/accounts/utils.py everytime on stock transactions

If you have large tabStock Ledger Entry table then this function iterate whole stock ledger entry (in my cases it has over 1mil records) that causes poor performance. Please anybody who can help on this

10

for now, I removed order_by clause to speedup query. now it took 3sec to query over 1M+ records. I don’t understand the reason why every stock transaction requires to get warehouse-wise balance (the whole Stock Ledger Entry records) - not per item code.

Is ths bug in V12? But I viewed V13 code and the same code is still there

11

Maybe you can try to add index on warehouse + item_code + posting_date + posting_time ?

12

not working, i tried many indexes but it didn’t help

13

The problem exists in v13 as well. That is why the code looks the same. If you have a large database in v12 or v13 with a large history of records, then submitting a sales invoice by POS or by long form takes up to a minute to complete.

I am not sure how I can upgrade my customers to v13 because the system is pretty much unusable. Stock transfers are extremely slow as well.

BKM

14

I believe the answer to your “why” question is Perpetual Inventory. More specifically, how the concept of Perpetual Inventory is being modeled in ERPNext.

Here’s a long but interesting thread.
TLDR: Some of us don’t understand why it necessitates summing the entire inventory balance, every time you post a new entry.