ERPNext Performance - Warehouse Tree fetches stock value of all children

Warehouse Tree view currently shows Stock Value against each warehouse by default as follows -

While we understand the feature and need for the same, fetching stock balances of each child warehouse on click on ‘Expand All’ button results into calculation of stock value for all the child warehouses using following function -

def get_stock_value_on(warehouse=None, posting_date=None, item_code=None):
	if not posting_date: posting_date = nowdate()

	values, condition = [posting_date], ""

	if warehouse:
		
		lft, rgt, is_group = frappe.db.get_value("Warehouse", warehouse, ["lft", "rgt", "is_group"])
		
		if is_group:
			values.extend([lft, rgt])
			condition += "and exists (\
				select name from `tabWarehouse` wh where wh.name = sle.warehouse\
				and wh.lft >= %s and wh.rgt <= %s)"
		
		else:
			values.append(warehouse)
			condition += " AND warehouse = %s"

	if item_code:
		values.append(item_code)
		condition.append(" AND item_code = %s")

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

	sle_map = {}
	for sle in stock_ledger_entries:
		if not sle_map.has_key((sle.item_code, sle.warehouse)):
			sle_map[(sle.item_code, sle.warehouse)] = flt(sle.stock_value)
		
	return sum(sle_map.values())

While we can tune above function to make it directly select sum(stock_value) directly in the query, we had a question if the stock_value can be retrieve directly from tabBin by multiplying actual_qty by valuation_rate?

This way we can avoid larger table scan on tabStock Ledger Entry

5 Likes

very good point. we also used to focus on tabBin for current stock in query reports. performance is way way batter for current stock reports.

Okay. We will update the above function and submit PR.

1 Like

PR Submitted

https://github.com/frappe/erpnext/pull/14584

1 Like

Team,

Could you help me this “child warehouse values do not exist for Warehouse in erpnext”

“COLUMN 4 (Parent Warehouse)
The following values do not exist for Warehouse:”