Try ERPNext Buy Support Partners Foundation

Submit Stock Transaction is very slow after upgraded to V12

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

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