Issue: Performance bottleneck in stock entry submit due to incorrect use get_cached_value and queries in the loop #51611
Description
Information about bug
I have one stock entry with 500 rows, per recorder, there are more than 16,000 sql queries!!! this query count is way too much, huge performance improvement can be made.
def validate_serial_batch_no_bundle(self):
if self.is_cancelled == 1:
return
item_detail = frappe.get_cached_value(
"Item",
self.item_code,
["has_serial_no", "has_batch_no", "is_stock_item", "has_variants", "stock_uom"],
as_dict=1,
)
the above get_cached_value will fetch Item and all its child tables, but only the data from Item table is needed, need to be changed as
def validate_serial_batch_no_bundle(self):
if self.is_cancelled == 1:
return
item_detail = frappe.get_value(
"Item",
self.item_code,
tuple(["has_serial_no", "has_batch_no", "is_stock_item", "has_variants", "stock_uom"]),
as_dict=1, cached=True
)
also before the loop on self.items, we need to pre-fetch the needed data for all unique item codes and populate the frappe.db.value_cache accordingly like below
def make_sl_entries(sl_entries, allow_negative_stock=False, via_landed_cost_voucher=False):
"""Create SL entries from SL entry dicts
args:
- allow_negative_stock: disable negative stock valiations if true
- via_landed_cost_voucher: landed cost voucher cancels and reposts
entries of purchase document. This flag is used to identify if
cancellation and repost is happening via landed cost voucher, in
such cases certain validations need to be ignored (like negative
stock)
"""
from erpnext.controllers.stock_controller import future_sle_exists
if sl_entries:
item_code_set = {row.item_code for row in sl_entries}
bulk_populate_item_cache(item_code_set)
warehouse_set = {row.warehouse for row in sl_entries}
bulk_populate_warehouse_cache(warehouse_set)
counts = {}
for d in sl_entries:
key = (d.item_code, d.warehouse)
counts[key] = counts.get(key, 0) + 1
item_code_warehouse_set = [key for key, count in counts.items() if count == 1]
bulk_populate_bin_cache(item_code_warehouse_set)
cancel = sl_entries[0].get("is_cancelled")
if cancel:
validate_cancellation(sl_entries)
set_as_cancel(sl_entries[0].get("voucher_type"), sl_entries[0].get("voucher_no"))
args = get_args_for_future_sle(sl_entries[0])
future_sle_exists(args, sl_entries)
for sle in sl_entries:
if sle.serial_no and not via_landed_cost_voucher:
validate_serial_no(sle)
if cancel:
sle["actual_qty"] = -flt(sle.get("actual_qty"))
if sle["actual_qty"] < 0 and not sle.get("outgoing_rate"):
sle["outgoing_rate"] = get_incoming_outgoing_rate_for_cancel(
sle.item_code, sle.voucher_type, sle.voucher_no, sle.voucher_detail_no
)
sle["incoming_rate"] = 0.0
if sle["actual_qty"] > 0 and not sle.get("incoming_rate"):
sle["incoming_rate"] = get_incoming_outgoing_rate_for_cancel(
sle.item_code, sle.voucher_type, sle.voucher_no, sle.voucher_detail_no
)
sle["outgoing_rate"] = 0.0
if sle.get("actual_qty") or sle.get("voucher_type") == "Stock Reconciliation":
sle_doc = make_entry(sle, allow_negative_stock, via_landed_cost_voucher)
args = sle_doc.as_dict()
args["posting_datetime"] = get_combine_datetime(args.posting_date, args.posting_time)
if sle.get("voucher_type") == "Stock Reconciliation":
# preserve previous_qty_after_transaction for qty reposting
args.previous_qty_after_transaction = sle.get("previous_qty_after_transaction")
is_stock_item = frappe.db.get_value("Item", args.get("item_code"), "is_stock_item", cache=True)
if is_stock_item:
bin_name = get_or_make_bin(args.get("item_code"), args.get("warehouse"))
args.reserved_stock = flt(frappe.db.get_value("Bin", bin_name, "reserved_stock", cache=True))
repost_current_voucher(args, allow_negative_stock, via_landed_cost_voucher)
update_bin_qty(bin_name, args)
else:
frappe.msgprint(
_("Item {0} ignored since it is not a stock item").format(args.get("item_code"))
)
def bulk_populate_item_cache(item_code_set):
fields = [
"has_batch_no",
"has_serial_no",
"item_name",
"item_group",
"serial_no_series",
"create_new_batch",
"batch_number_series",
]
data = frappe.get_all("Item", filters = {"name": ("in", item_code_set)},
fields= ["name", "valuation_method", "allow_negative_stock", "is_stock_item", "has_variants", "stock_uom"] + fields
)
for d in data:
frappe.db.value_cache[("Item", d.name, "allow_negative_stock")] = ((d.allow_negative_stock,),)
frappe.db.value_cache[("Item", d.name, "valuation_method")] = ((d.valuation_method,),)
#base_document get_invalid_links has name as part of cache key
frappe.db.value_cache[("Item", d.name, ("name","has_batch_no","has_serial_no"))] = [d]
sle_fields = ["has_serial_no", "has_batch_no", "is_stock_item", "has_variants", "stock_uom"]
frappe.db.value_cache[("Item", d.name, tuple(fields))] = [d]
frappe.db.value_cache[("Item", d.name, tuple(sle_fields))] = [d]
frappe.db.value_cache[("Item", d.name, "is_stock_item")] = ((d.is_stock_item,),)
#frappe.db.value_cache[("Item", d.name, ('name','item_group','stock_uom'))] = [d]
def bulk_populate_warehouse_cache(warehouse_set):
fields = ["name", "company", "is_group", "disabled"]
data = frappe.get_all("Warehouse", filters={"name": ("in", warehouse_set)}, fields=fields)
for d in data:
for field in fields[1:]:
frappe.db.value_cache[("Warehouse", d.name, field)] = ((d.get(field),),)
def bulk_populate_bin_cache(item_code_warehouse_set):
item_codes, warehouses = zip(*item_code_warehouse_set) if item_code_warehouse_set else ([], [])
item_code_set = set(item_codes)
warehouse_set = set(warehouses)
fields = ["name", "item_code", "warehouse", "reserved_stock"]
data = frappe.get_all("Bin", filters={
"item_code": ("in", item_code_set),
"warehouse": ("in", warehouse_set)
}, fields=fields)
for d in data:
item_code_warehouse = (d.item_code, d.warehouse)
if item_code_warehouse in item_code_warehouse_set:
frappe.db.value_cache[("Bin", d.item_code, d.warehouse)] = d.name
frappe.db.value_cache[("Bin", d.name, "reserved_stock")] = ((d.reserved_stock,),)
Module
stock
Version
erpnext v15 and v16
Installation method
None