We added a print statement in database.py as follows -
def sql(self, query, values=(), as_dict = 0, as_list = 0, formatted = 0, debug=0, ignore_ddl=0, as_utf8=0, auto_commit=0, update=None): """Execute a SQL query and fetch all rows. .... """ print ('QUERY = ', query, values)
and measured number of queries getting fired on
Sales Invoice (select sales order, click on make sales invoice) save and submit operations.
First this first -
Is it a right way to measure number of DB Calls?
Another declaration -
I am yet to analyse this data fully. Will keep on sharing finding. Staring this thread just to get some early inputs if anyone has. We may be completly wrong.
Following are the findings -
Sales Order (and hence invoice) had 9 Items in it
- Save = Around 778 database calls
- Submit = Around 1050 database calls.
tabSingles 60 tabItem 56 tabCurrency 39 tabBin 27 tabAccount 26 tabAccount 26 tabSales Invoice 22 tabSales Order 21 tabUOM Conversion Detail 18 tabItem Supplier 18 (custom) tabItem Tax 18 tabItem Variant 18 tabItem Company Settings 18 (custom) tabItem Customer Details 18 tabItem Quality inspection parameters 18 tabItem Reorder 18 tabItem Website Specification 18 tabWebsite Item Group 18 tabAttachment Link 18 tabItem Group 17 tabSales Invoice Item 12 tabCompany 12 tabDocType 11 tabCustomer 11 tabProject 11 tabPrice List 11 tabProject 11 tabWarehouse 10 tabCost Center 10 tabSales Order Item 9 tabSales Taxes and charges template 9 tabItem Price 9 tabItem 9 tabProductBundle 9 tabPurchase Order 9 tabPurchase Receipt Item 9 tabDocType 8 tabUOM 7 tabSales Team 7 tabAddress 6 tabParty Account 5 tabJournal Entry 5 tabPayment Schedule 5 tabCustomer Group 4 tabSales Invoice Payment 4 tabSales Invoice Timesheet 4 tabPacked Item 4 tabCommunicaiton 4 tabVersion 3 tabContact 2 tabSales Person 1
We need to understand why so many DB calls are being made. There could be some of our custom code (We dont have much). However we need to relook at the code design in general. We are still digesting this data. In case anyone has looked into this aspect, can you please comment? We will keep on sharing our findings here.
Our first observation is about the tables linked to Item. There are many child tables in the
Item doctype on which there are repeat
select queries happening. A quick
traceback.print_stack() showed select queries are originating from
File "/workspace/Dev/frappe-bench/apps/erpnext/erpnext/controllers/accounts_controller.py", line 200, in set_missing_item_details
Even queries to tables like
Purchase order and
Purchase Order Items``, Purchase Receipt Item``` are originating from set_missing_item_details.