perf: Add indexes in stock queries and speed up bin updation #27758
perf: Add indexes in stock queries and speed up bin updation
diff --git a/erpnext/controllers/stock_controller.py b/erpnext/controllers/stock_controller.py
index 78a6e52..4697205 100644
--- a/erpnext/controllers/stock_controller.py
+++ b/erpnext/controllers/stock_controller.py
@@ -591,7 +591,7 @@
data = frappe.db.sql("""
select item_code, warehouse, count(name) as total_row
- from `tabStock Ledger Entry`
+ from `tabStock Ledger Entry` force index (item_warehouse)
where
({})
and timestamp(posting_date, posting_time)
diff --git a/erpnext/stock/doctype/bin/bin.py b/erpnext/stock/doctype/bin/bin.py
index 5fbc2d8..4be0415 100644
--- a/erpnext/stock/doctype/bin/bin.py
+++ b/erpnext/stock/doctype/bin/bin.py
@@ -14,51 +14,6 @@
self.stock_uom = frappe.get_cached_value('Item', self.item_code, 'stock_uom')
self.set_projected_qty()
- def update_stock(self, args, allow_negative_stock=False, via_landed_cost_voucher=False):
- '''Called from erpnext.stock.utils.update_bin'''
- self.update_qty(args)
-
- if args.get("actual_qty") or args.get("voucher_type") == "Stock Reconciliation":
- from erpnext.stock.stock_ledger import update_entries_after, update_qty_in_future_sle
-
- if not args.get("posting_date"):
- args["posting_date"] = nowdate()
-
- if args.get("is_cancelled") and via_landed_cost_voucher:
- return
-
- # Reposts only current voucher SL Entries
- # Updates valuation rate, stock value, stock queue for current transaction
- update_entries_after({
- "item_code": self.item_code,
- "warehouse": self.warehouse,
- "posting_date": args.get("posting_date"),
- "posting_time": args.get("posting_time"),
- "voucher_type": args.get("voucher_type"),
- "voucher_no": args.get("voucher_no"),
- "sle_id": args.name,
- "creation": args.creation
- }, allow_negative_stock=allow_negative_stock, via_landed_cost_voucher=via_landed_cost_voucher)
-
- # update qty in future ale and Validate negative qty
- update_qty_in_future_sle(args, allow_negative_stock)
-
-
- def update_qty(self, args):
- # update the stock values (for current quantities)
- if args.get("voucher_type")=="Stock Reconciliation":
- self.actual_qty = args.get("qty_after_transaction")
- else:
- self.actual_qty = flt(self.actual_qty) + flt(args.get("actual_qty"))
-
- self.ordered_qty = flt(self.ordered_qty) + flt(args.get("ordered_qty"))
- self.reserved_qty = flt(self.reserved_qty) + flt(args.get("reserved_qty"))
- self.indented_qty = flt(self.indented_qty) + flt(args.get("indented_qty"))
- self.planned_qty = flt(self.planned_qty) + flt(args.get("planned_qty"))
-
- self.set_projected_qty()
- self.db_update()
-
def set_projected_qty(self):
self.projected_qty = (flt(self.actual_qty) + flt(self.ordered_qty)
+ flt(self.indented_qty) + flt(self.planned_qty) - flt(self.reserved_qty)
@@ -143,3 +98,67 @@
def on_doctype_update():
frappe.db.add_index("Bin", ["item_code", "warehouse"])
+
+
+def update_stock(bin_name, args, allow_negative_stock=False, via_landed_cost_voucher=False):
+ '''Called from erpnext.stock.utils.update_bin'''
+ update_qty(bin_name, args)
+
+ if args.get("actual_qty") or args.get("voucher_type") == "Stock Reconciliation":
+ from erpnext.stock.stock_ledger import update_entries_after, update_qty_in_future_sle
+
+ if not args.get("posting_date"):
+ args["posting_date"] = nowdate()
+
+ if args.get("is_cancelled") and via_landed_cost_voucher:
+ return
+
+ # Reposts only current voucher SL Entries
+ # Updates valuation rate, stock value, stock queue for current transaction
+ update_entries_after({
+ "item_code": args.get('item_code'),
+ "warehouse": args.get('warehouse'),
+ "posting_date": args.get("posting_date"),
+ "posting_time": args.get("posting_time"),
+ "voucher_type": args.get("voucher_type"),
+ "voucher_no": args.get("voucher_no"),
+ "sle_id": args.get('name'),
+ "creation": args.get('creation')
+ }, allow_negative_stock=allow_negative_stock, via_landed_cost_voucher=via_landed_cost_voucher)
+
+ # update qty in future sle and Validate negative qty
+ update_qty_in_future_sle(args, allow_negative_stock)
+
+def get_bin_details(bin_name):
+ return frappe.db.get_value('Bin', bin_name, ['actual_qty', 'ordered_qty',
+ 'reserved_qty', 'indented_qty', 'planned_qty', 'reserved_qty_for_production',
+ 'reserved_qty_for_sub_contract'], as_dict=1)
+
+def update_qty(bin_name, args):
+ bin_details = get_bin_details(bin_name)
+
+ # update the stock values (for current quantities)
+ if args.get("voucher_type")=="Stock Reconciliation":
+ actual_qty = args.get('qty_after_transaction')
+ else:
+ actual_qty = bin_details.actual_qty + flt(args.get("actual_qty"))
+
+ ordered_qty = flt(bin_details.ordered_qty) + flt(args.get("ordered_qty"))
+ reserved_qty = flt(bin_details.reserved_qty) + flt(args.get("reserved_qty"))
+ indented_qty = flt(bin_details.indented_qty) + flt(args.get("indented_qty"))
+ planned_qty = flt(bin_details.planned_qty) + flt(args.get("planned_qty"))
+
+
+ # compute projected qty
+ projected_qty = (flt(actual_qty) + flt(ordered_qty)
+ + flt(indented_qty) + flt(planned_qty) - flt(reserved_qty)
+ - flt(bin_details.reserved_qty_for_production) - flt(bin_details.reserved_qty_for_sub_contract))
+
+ frappe.db.set_value('Bin', bin_name, {
+ 'actual_qty': actual_qty,
+ 'ordered_qty': ordered_qty,
+ 'reserved_qty': reserved_qty,
+ 'indented_qty': indented_qty,
+ 'planned_qty': planned_qty,
+ 'projected_qty': projected_qty
+ })
\ No newline at end of file
diff --git a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.json b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.json
index 40ae340..2651407 100644
--- a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.json
+++ b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.json
@@ -317,7 +317,7 @@
"in_create": 1,
"index_web_pages_for_search": 1,
"links": [],
- "modified": "2021-10-08 12:42:51.857631",
+ "modified": "2021-10-08 13:42:51.857631",
"modified_by": "Administrator",
"module": "Stock",
"name": "Stock Ledger Entry",
diff --git a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
index 382fdfa..2cf71ac 100644
--- a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
+++ b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
@@ -181,3 +181,4 @@
frappe.db.add_index("Stock Ledger Entry", ["voucher_no", "voucher_type"])
frappe.db.add_index("Stock Ledger Entry", ["batch_no", "item_code", "warehouse"])
+ frappe.db.add_index("Stock Ledger Entry", ["warehouse", "item_code"], "item_warehouse")
diff --git a/erpnext/stock/stock_ledger.py b/erpnext/stock/stock_ledger.py
index 1b5b792..e9d5b6a 100644
--- a/erpnext/stock/stock_ledger.py
+++ b/erpnext/stock/stock_ledger.py
@@ -13,8 +13,8 @@
import erpnext
from erpnext.stock.utils import (
- get_bin,
get_incoming_outgoing_rate_for_cancel,
+ get_or_make_bin,
get_valuation_method,
)
@@ -805,14 +805,13 @@
def update_bin(self):
# update bin for each warehouse
for warehouse, data in iteritems(self.data):
- bin_doc = get_bin(self.item_code, warehouse)
- bin_doc.update({
+ bin_record = get_or_make_bin(self.item_code, warehouse)
+
+ frappe.db.set_value('Bin', bin_record, {
"valuation_rate": data.valuation_rate,
"actual_qty": data.qty_after_transaction,
"stock_value": data.stock_value
})
- bin_doc.flags.via_stock_ledger_entry = True
- bin_doc.save(ignore_permissions=True)
def get_previous_sle_of_current_voucher(args, exclude_current_voucher=False):
@@ -918,7 +917,7 @@
company = erpnext.get_default_company()
last_valuation_rate = frappe.db.sql("""select valuation_rate
- from `tabStock Ledger Entry`
+ from `tabStock Ledger Entry` force index (item_warehouse)
where
item_code = %s
AND warehouse = %s
@@ -929,7 +928,7 @@
if not last_valuation_rate:
# Get valuation rate from last sle for the item against any warehouse
last_valuation_rate = frappe.db.sql("""select valuation_rate
- from `tabStock Ledger Entry`
+ from `tabStock Ledger Entry` force index (item_code)
where
item_code = %s
AND valuation_rate > 0
diff --git a/erpnext/stock/utils.py b/erpnext/stock/utils.py
index aeb06e9..c4a0497 100644
--- a/erpnext/stock/utils.py
+++ b/erpnext/stock/utils.py
@@ -180,12 +180,27 @@
bin_obj.flags.ignore_permissions = True
return bin_obj
+def get_or_make_bin(item_code, warehouse) -> str:
+ bin_record = frappe.db.get_value('Bin', {'item_code': item_code, 'warehouse': warehouse})
+
+ if not bin_record:
+ bin_obj = frappe.get_doc({
+ "doctype": "Bin",
+ "item_code": item_code,
+ "warehouse": warehouse,
+ })
+ bin_obj.flags.ignore_permissions = 1
+ bin_obj.insert()
+ bin_record = bin_obj.name
+
+ return bin_record
+
def update_bin(args, allow_negative_stock=False, via_landed_cost_voucher=False):
+ from erpnext.stock.doctype.bin.bin import update_stock
is_stock_item = frappe.get_cached_value('Item', args.get("item_code"), 'is_stock_item')
if is_stock_item:
- bin = get_bin(args.get("item_code"), args.get("warehouse"))
- bin.update_stock(args, allow_negative_stock, via_landed_cost_voucher)
- return bin
+ bin_record = get_or_make_bin(args.get("item_code"), args.get("warehouse"))
+ update_stock(bin_record, args, allow_negative_stock, via_landed_cost_voucher)
else:
frappe.msgprint(_("Item {0} ignored since it is not a stock item").format(args.get("item_code")))