fix(perf): index creation on voucher_detail_no (#27866)
voucher_detail_no is supposed to have an index, it was added on
on_doctype_update function of table, however this function is only
called if DocType itself is updated and `on_update` is called on
DocType. Stock ledger Entry doctype hasn't changed since addition of
this index in function.
Before: Lack of this index was causing full table scan in
get_future_sle_to_fix function. (~50 seconds in a reposting job)
After: Single row is fetched (~0.5 second in full reposting job)
Learnings:
1. Add simple indexes via doctype only
2. For complex indexes always change doctype.json file for it to take
effect.
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 2463a21..40ae340 100644
--- a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.json
+++ b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.json
@@ -142,6 +142,7 @@
"oldfieldtype": "Data",
"print_width": "150px",
"read_only": 1,
+ "search_index": 1,
"width": "150px"
},
{
@@ -316,7 +317,7 @@
"in_create": 1,
"index_web_pages_for_search": 1,
"links": [],
- "modified": "2020-09-07 11:10:35.318872",
+ "modified": "2021-10-08 12:42:51.857631",
"modified_by": "Administrator",
"module": "Stock",
"name": "Stock Ledger Entry",
@@ -338,4 +339,4 @@
],
"sort_field": "modified",
"sort_order": "DESC"
-}
\ No newline at end of file
+}
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 caa1d42..382fdfa 100644
--- a/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
+++ b/erpnext/stock/doctype/stock_ledger_entry/stock_ledger_entry.py
@@ -181,4 +181,3 @@
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", ["voucher_detail_no"])