perf: Weed out disabled variants via sql query instead of pythonic looping separately
- If the number of variants are large (almost 2lakhs), the query to get variants and attribute data takes time
- If the no.of disabled attributes is large as well, the list comprehension weeding out disabled variants takes forever
- We dont need to loop over the variants data so many times
- Avoid any `if a in list(b)` is best when the iterables have tremendous data
diff --git a/erpnext/e_commerce/variant_selector/item_variants_cache.py b/erpnext/e_commerce/variant_selector/item_variants_cache.py
index bb6b3ef..9b22255 100644
--- a/erpnext/e_commerce/variant_selector/item_variants_cache.py
+++ b/erpnext/e_commerce/variant_selector/item_variants_cache.py
@@ -66,25 +66,39 @@
)
]
- # join with Website Item
- item_variants_data = frappe.get_all(
- 'Item Variant Attribute',
- {'variant_of': parent_item_code},
- ['parent', 'attribute', 'attribute_value'],
- order_by='name',
- as_list=1
+ # Get Variants and tehir Attributes that are not disabled
+ iva = frappe.qb.DocType("Item Variant Attribute")
+ item = frappe.qb.DocType("Item")
+ query = (
+ frappe.qb.from_(iva)
+ .join(item).on(item.name == iva.parent)
+ .select(
+ iva.parent, iva.attribute, iva.attribute_value
+ ).where(
+ (iva.variant_of == parent_item_code)
+ & (item.disabled == 0)
+ ).orderby(iva.name)
)
+ item_variants_data = query.run()
- disabled_items = set(
- [i.name for i in frappe.db.get_all('Item', {'disabled': 1})]
- )
+ # item_variants_data = frappe.get_all(
+ # 'Item Variant Attribute',
+ # {'variant_of': parent_item_code},
+ # ['parent', 'attribute', 'attribute_value'],
+ # order_by='name',
+ # as_list=1
+ # )
+
+ # disabled_items = set(
+ # [i.name for i in frappe.db.get_all('Item', {'disabled': 1})]
+ # )
attribute_value_item_map = frappe._dict()
item_attribute_value_map = frappe._dict()
# dont consider variants that are disabled
# pull all other variants
- item_variants_data = [r for r in item_variants_data if r[0] not in disabled_items]
+ # item_variants_data = [r for r in item_variants_data if r[0] not in disabled_items]
for row in item_variants_data:
item_code, attribute, attribute_value = row