Patch set_produced_qty_field_in_sales_order_for_work_order is taking Forever to execute

Install / Update
1

Hi,

I am trying to update the system and every time I have to skip this patch set_produced_qty_field_in_sales_order_for_work_order since this patch is taking like ages for it to execute something like more than 1 hour any suggestions as to how could I not skip it and apply to my instance.

I have already skipped applying this patch for the past 1 month in the hope that someone would find a problem with this patch but it seems that not a lot of people are facing issues with the patch.

The patch seems to be hung on executing but when I check the mariadb console I do get to see that the patch is working, how much time would this patch need for my db

show full processlist;
+----------+------------------+-----------------+------------------+---------+------+----------------+----------------------------------------------------------------------------------------
| Id       | User             | Host            | db               | Command | Time | State          | Info                                                                                   
+----------+------------------+-----------------+------------------+---------+------+----------------+----------------------------------------------------------------------------------------
| 10060320 | 7c9e7b08e59cc63c | localhost:42760 | 7c9e7b08e59cc63c | Query   |    0 | Sorting result | select produced_qty
                        from `tabWork Order`
                        where `tabWork Order`.sales_order_item = "7ef602048a" and `tabWork Order`.docstatus = 1.0
                        
                         order by `tabWork Order`.docstatus asc, `tabWork Order`.`creation` DESC |    0.000 |
1 Like
2

Well just a quick follow up for the same and also note that there is another patch which is causing problem in migrating and this patch is from Frappe and the patch is make_all_prepared_report_attachments_private and this patch is also taking like a long time to execute which basically means I have to skip these patch.

Is there a way I can execute these patches without skipping them and not waiting for like 2~3 hours for the patches to execute.

3

In the first patch (set_produced_qty_field_in_sales_order_for_work_order) you can replace this lines:

    sales_order_items = frappe.db.get_all('Sales Order Item', ['name'])
    for so_item in sales_order_items:
        update_produced_qty_in_so_item(so_item.get('name'))

Whit this ones and it’s going to be faster:

    frappe.db.commit()
    frappe.db.sql("""UPDATE `tabSales Order Item` soi
    	             set soi.produced_qty = (SELECT ifnull(sum(ifnull(wo.produced_qty,0)), 0)
    	                                     from `tabWork Order` wo
    	                                     where wo.sales_order_item = soi.name
    	                                     and wo.docstatus = 1)""")
    frappe.db.commit()
1 Like
4

And for the second patch, you can replace all the code with:

    frappe.db.commit()
    frappe.db.sql("""DELETE `tabFile` fi
    				 where not exists(SELECT name
                                      from `tabPrepared Report` pr
                                      where pr.name = fi.attached_to_name)
                     and fi.attached_to_doctype = "Prepared Report"
    	          """)
    frappe.db.sql("""UPDATE `tabFile` fi set is_private=1 where attached_to_doctype = "Prepared Report"; """)
    frappe.db.commit()

You should do a backup before executing this patches and then check if everything is ok.

This patches are slow because they use the frappe ORM to handle big quantity of data, and that’s super slow.

1 Like