0037835: Performance issue in Generate Invoices process

'Sort (cost=93208243.29..93208244.82 rows=613 width=63)' ' Sort Key: (COALESCE(to_char(COALESCE(to_char(td0.name), ''::character varying)), ''::character varying))' ' -> Nested Loop (cost=93206210.99..93208214.91 rows=613 width=63)' ' -> HashAggregate (cost=93206210.57..93206216.84 rows=627 width=33)' ' -> Subquery Scan on "ANY_subquery" (cost=93206196.46..93206209.00 rows=627 width=33)' ' -> HashAggregate (cost=93206196.46..93206202.73 rows=627 width=33)' ' -> Seq Scan on c_order (cost=0.00..93190075.26 rows=6448483 width=33)' ' Filter: (((docstatus)::text <> ALL ('{DR,IP}'::text[])) AND (issotrx = 'Y'::bpchar) AND ((((invoicerule)::text = 'O'::text) AND (NOT (SubPlan 1)) AND (SubPlan 2)) OR (((invoicerule)::text = ANY ('{D,S}'::text[])) AND (SubPlan 3)) OR (((invoicerule)::text = 'I'::text) AND (SubPlan 4))))' ' SubPlan 1' ' -> Index Scan using c_orderline_order on c_orderline (cost=0.56..222.25 rows=194 width=0)' ' Index Cond: ((c_order.c_order_id)::text = (c_order_id)::text)' ' Filter: (qtyordered <> qtydelivered)' ' SubPlan 2' ' -> Index Scan using c_orderline_order on c_orderline c_orderline_1 (cost=0.56..222.25 rows=194 width=0)' ' Index Cond: ((c_order.c_order_id)::text = (c_order_id)::text)' ' Filter: (qtyordered <> qtyinvoiced)' ' SubPlan 3' ' -> Index Scan using c_orderline_order on c_orderline c_orderline_2 (cost=0.56..222.74 rows=193 width=0)' ' Index Cond: ((c_order.c_order_id)::text = (c_order_id)::text)' ' Filter: ((qtyordered <> qtyinvoiced) AND (qtydelivered <> qtyinvoiced))' ' SubPlan 4' ' -> Index Scan using c_orderline_order on c_orderline c_orderline_3 (cost=0.56..222.25 rows=194 width=0)' ' Index Cond: ((c_order.c_order_id)::text = (c_order_id)::text)' ' Filter: (qtyordered <> qtyinvoiced)' ' -> Index Scan using c_bpartner_key on c_bpartner td0 (cost=0.42..2.69 rows=1 width=63)' ' Index Cond: ((c_bpartner_id)::text = ("ANY_subquery".c_bpartner_id)::text)' ' Filter: (((ad_client_id)::text = ANY ('{0,9D11C7AED56349F186249FDAD0B69515}'::text[])) AND (isactive = 'Y'::bpchar) AND ((ad_org_id)::text = ANY ('{0,D701705CCB6A48199723A207ED1146B1,7FA06B6FC46C4187BE1FB346A92D1928,59332BF8A9324107A30D9F93E048F6F4,8626F42D0E284E68BC9BD3D9C9E8EDCA,A1A1D9FA06384AA9B5CC2F35D39B0199,7D693EA6788F44818C46AE9C592EDC18,22283C0E837744B6A95DE7798529D149,2BB23DF1D10743BD944C3E643F81A21D,B3C7EF2F11B4443EA3EF7EFD6EE158A2,95A2353A5223434982403F0700489AAE,9BE0ED62B4EC41E0AA0258877627B882,633D90BC32214B03BC67F88F9BB6CF47,387F54B230D84A458223910ECAA46585,8BB8060F43CE4316BCC9639A75E1301B,691321964EFB422C9F9A8431C13BD454,1199229CE3F54357BF83995E23209820,74A75D4B82774C8F84325B7C6C25E901,E3D4869CA0A34E3B8FB110418FBBF50E,6ED25D3CA5CA4AE896D13804C2516FE8,81EF509AC26047C79BB88FB00F62848B,6B8A39712CBD47E692DFD6909674498C,BC2845E04C604D72A12D795C3577A135,388F51CA3B09465E8F863A8885364087,02A339D6E06B4E43B9B8FBFF263CB337,A09702376A7745AEAC55E2ADA23D1FD3,638BFC40E0C64C17955C1D20A3A51679,F572A6855237472C8A7B422B430E5EF3,8D58D404736B432D901E687893E8E171,43B4C56A6ABC45C2999858601EF452D6,10B10B421D354B068E724911376D0EAB,57E3793F499148529DEA9DC0B48BA0B5,2B5C645F90784ED88523C16874D05875,EA092A6B78D343F5897FC4328C897633,E9948F4F822941F9A2BC5065921BA13E,F41C2A14741F465A932EE07CD6FFEBBC,0EED637FC6DD4FC6A9E4ACE66F349D60,04FA68D5718F486EB0F6789A205D984E,3E8E55D9333546A2ADF0591D6BBB5202,DE67F772A3DE49C8B78C3A2C7D3B9A8F,CED6CCEB673E4C069B8D1167CF54CD5E,1B48B7801D5A4B45A87EE7EA6BE70CB3,5448E3E9D0DF4EE78082AD21C8F67693,D503E2FFF9204F6BBC1765C1CEE059E4,65C7845EF4E440A69415C8235EA85557,20409C0E6CD042348E768EF9DD3553F4,BD46249726054687BB877095378BE8F0,1AEBCB33F47649A798CA7F361BBA66F9,AC14BC4670014376B176B39116ADE55E,9CEEB4ECBCD14C6EABD9C104C20166D3,87B1A4B698FD460D97FEFF071F32A6ED,CBF4429631674F87BB3D29A463D475F7,88454885E1A34B16AFC51BC6190DF002,A06BBA017F0E4DCA9100E2A9092C7DE6,ABD3C740184C4A8E892B801CBEC7503B,7801B61436E24E52B0078F14F7270B27,B0CA1DA22EE241F79859CC153F96067F,FB1B2625EBA94A0281AD95065A546AFD,EF39BA0931904958AB8A91BC36D2B4EC,1FC710785A214BC7BF069205A0E506FA,D0B3BBFAE2A5470D88906F87EFB57B9F,25C9322D670C4308B484DAE04140AB5C,5BA277A736C94948A434D340178D9BAC,91FDEC433CB54A8A803019CF23ADDEB5,CA9E6A181368432C8A3EA4B9C395776E,AAFE16A9BF6E493CB87A8556A0BDC02B,261D4C493B9B48948E4042003F914FB0,0616ACD0F4CD4D59933DCCB2BAD12AAC,9CA9D2765EEE40DE91A15245FB3AA872,59323FEEA128423D8E6CFE52C9469840,6440820F77794731922C332C22EB0C5E,7B1238161D2D45CB917804C763C8604E,0E122C9CDC404086A37E86558CE67F17,6A85E17259ED43508E6672BB86186246,3030D85BD5854D9783B37D5B1984EBCD,D1C7D0C73A744C0AA9B78C3715DAC366,D3337B4F074545E0A5D68D36F64DE0D0,B60F88C677E641879AA07E36F204505C,78CCE833C2D442E0B55FA75C0F46688B,3D05638ABCD042BF8AE45048E3EAA993,BE4DA00046EC4EA694B08A38B55C4046,43AF06D02F7F4F26BC3EA1135AC279A9,28B527E15A2341EBA3E9842BD18A2A07,DD67968132604C67BBDB4A6579484F3D,227DB929435740A8AC0BF8CC0B3FFDC8,E80ABB58775C4FAB9C24C13AB24436BB,B818621BBC5A43A699933449B8EA37B8,FCF6AC5C03AE406DBD7F41A7AD5B1CD9,A35AFDBFD4214132B977821A61AF168F,ED65A777F6824380856721266386913D,023DF36C03AB4BF681C03DC310F0FD14,DA60E38381FE45248722805FE870DEF1,DEADD9ABDE554BF79774B80D25F7C5D5,14DDB18729144E98B991D5FE2C00E2D2,95165634C8CE44088AB3A0113084DF0A,B6A5F340B17246B3B19C7A2F35F1931C,C72216DA7849460690616CCC0B76E7AF,044D5A64FA214E9B9275A2A7BA08BE6C,4086CFCC0FC8449BBACCBFD45A79AB91,2FAEE704A31645309E77399BE80EF00C,67880CE9DB3A4614B71E5F540E5FD84F,A3B2AF75C3144204AEA18DE4019FD5BC,31C121B2A0864F37AF23760879DCF376,8A6645244D2B40DBBD8963035E38F6B5,9AE96E0DBF854535BB6C7C8E5CC83149,1294C2248D524FB7AEE22073B3DF1F46,6F5A6A0BA4A54D0497CCE82A9F75DF22,B5DBCF8701EF485DA55F78E04A1CE627,243AEDF44E2D4CA2A993CADBCC07285F,2288A0958718423887FE0373CBC79163,29F668B642D3483D96710097AA715F44,99E8D23A83E54E0DB95B51BBFF434C63,D055341711154E14AA89BA39278AA072,B2ACE3EF4EC747799EB37C70BE33AB24,103E4378B81245428BAD94E1DB9E9841,DDF1147346EA4DAE8B96E51F08CD8E65,4020122B392D4B8FAC4B71ABC007BE8E,76774EF90C894499AEDDE8AF585995A6,6F729785F8CA4B66B1CE8BBBE7257154,B552116D5B754A2D9DB6FE49CB5EB2B3,1ABF28E04092430DB70427FA66C51900,5A95CE93A8BC424DB6994DFD21BAF960,D06F60CFD03941F78460BB4737687F06,873D1C0F51AF41AAAB56C5DB61F72163,0E5A371867734AE5B9DB22C07EEFF4A6,0B051DE44D7D4D069C032B0961A39E13,0DF2CA6752E34596ABD36BCB8886BCB0,CCA64F7F3C114C62B7218B16B7BECB3E,FCD93C62A1774190BA2B50A6CA98D36E,829FB78685104D3AB3C6F5CB4EC895FC,ECA7B310948A478A914405F7A918BE9F,793A6F70CF0647088CD84E539CDC3950,B7B8D2E0EBF74087B2802DD75B2C3AAF,BCB92A47909F4D67A5910722C48D66CD,4EEBA27705D74460B125AC3796BB5044,766EE695426C4BBAB718F3C204A7DF9C,1E99A66B12BC41888E3626B1CA24701A,F974CB757D91497D9E0F58529D918E87,F95D56BB3B1E499ABCB3CB85B5520C0E,4DBE2BD989D14328A8996E4171AB62F7,C91D20D24575415CB81965907FA80AB9,340F5D97595F4DDF83EF0AFF133C11DE,602BF7D1EC0B41BEB412EA532D880F31,239E9315970D489CB336D51F8A4C1D6D,C39A954461A649339543DD70FCD8DDD0,45AB7A0BA23A47E5A5143024C33AD250,7A243C1DC47E4DDD8052DF306F01CE2A,E0B7A44F4C674D63BA728AED499FD18A,8801836C2A304F1FAD53C2E9200CA2B7,4A1E50C7661648BF969CCE6878D199DB,D1405FE61EC84371B36E606D0F07D820,0BC83AB4049349F6A970C1E196FECB55,A078C773065C45EB94AE75E1DD46F16C,5277569712DE4809BA93EBC1BEC60A1D,F421A0DF88FD44A988A293ECCB381180}'::text[])))'

Notes
(0102338) collazoandy4 (viewer) 2018-02-12 17:09	Test Plan Open Generate Invoices window and check the business partner field is empty Go to Sales Order and create three new records: 1. Business Partner: Alimentos y Supermercados, S.A, Invoice Terms: Immediate, Product: Agua sin Gas 1L, Ordered Quantity: 5 2. Business Partner: Hoteles Buenas Noches, S.A., Invoice Terms: After Delivery, Product: Zumo de Pera 0,5L, Ordered Quantity: 6 3. Business Partner: Restaurantes Luna Llena, S.A., Invoice Terms: After Order Delivered, Product: Zumo de Piña 0,5L, Ordered Quantity: 7 Go to Goods Shipment window and create a new record: Business Partner: Hoteles Buenas Noches, S.A. Create Lines From the previous order created Edit the line created Movement Quantity: 3 Complete the shipment Create another record in Header tab: Business Partner: Restaurantes Luna Llena, S.A. Create Lines From the previous order created Complete the shipment Open Generate Invoices window and check the business partner shows 3 records (The three partners created)

(0102447) collazoandy4 (viewer) 2018-02-15 17:45 edited on: 2018-02-16 17:19	With the changes made the execution query cost was reduce from 105544030.57 to 838264.07

(0102517) hgbot (developer) 2018-02-20 09:55	Repository: erp/devel/pi Changeset: b13652428b1cd2b3d8a8c491933e1a7cb5e50c1e Author: Armaignac <collazoandy4 <at> gmail.com> Date: Fri Feb 09 18:44:25 2018 -0500 URL: http://code.openbravo.com/erp/devel/pi/rev/b13652428b1cd2b3d8a8c491933e1a7cb5e50c1e [^] Fixes issue 37835: Performance issue in Generate Invoices process Generate Invoices process takes too long to load in environments with high amount of data The query to get all the business partner with pending sales order to invoice was changed using the index created to minimize the time that the process take to load --- M src-db/database/model/tables/C_ORDERLINE.xml M src-db/database/sourcedata/AD_VAL_RULE.xml ---

(0102518) dmiguelez (viewer) 2018-02-20 09:56	Code Review + Testing Ok

(0102778) hudsonbot (viewer) 2018-02-22 18:19	A changeset related to this issue has been promoted main and to the Central Repository, after passing a series of tests. Promotion changeset: https://code.openbravo.com/erp/devel/main/rev/980a6ad5bbf5 [^] Maturity status: Test

Issue History
Date Modified	Username	Field	Change
2018-02-05 16:01	JONHM	New Issue
2018-02-05 16:01	JONHM	Assigned To	=> Triage Finance
2018-02-05 16:01	JONHM	File Added: generate_invoices.sql
2018-02-05 16:01	JONHM	OBNetwork customer	=> Yes
2018-02-05 16:01	JONHM	Modules	=> Core
2018-02-05 16:01	JONHM	Support ticket	=> 50770
2018-02-05 16:01	JONHM	Resolution time	=> 1519599600
2018-02-05 16:01	JONHM	Triggers an Emergency Pack	=> No
2018-02-05 16:02	JONHM	File Added: generate_invoices_test_plan
2018-02-05 16:12	jfrances	Issue Monitored: jfrances
2018-02-05 16:24	JONHM	File Deleted: generate_invoices.sql
2018-02-05 16:24	JONHM	File Deleted: generate_invoices_test_plan
2018-02-05 16:24	JONHM	File Added: generate_invoice.sql
2018-02-05 16:24	JONHM	File Added: generate_invoices_query_plan
2018-02-06 11:20	dmiguelez	Assigned To	Triage Finance => collazoandy4
2018-02-06 22:22	collazoandy4	Status	new => scheduled
2018-02-12 17:09	collazoandy4	Note Added: 0102338
2018-02-15 17:45	collazoandy4	Note Added: 0102447
2018-02-16 17:19	collazoandy4	Note Edited: 0102447	View Revisions
2018-02-20 09:55	hgbot	Checkin
2018-02-20 09:55	hgbot	Note Added: 0102517
2018-02-20 09:55	hgbot	Status	scheduled => resolved
2018-02-20 09:55	hgbot	Resolution	open => fixed
2018-02-20 09:55	hgbot	Fixed in SCM revision	=> http://code.openbravo.com/erp/devel/pi/rev/b13652428b1cd2b3d8a8c491933e1a7cb5e50c1e [^]
2018-02-20 09:56	dmiguelez	Review Assigned To	=> dmiguelez
2018-02-20 09:56	dmiguelez	Note Added: 0102518
2018-02-20 09:56	dmiguelez	Status	resolved => closed
2018-02-20 09:56	dmiguelez	Fixed in Version	=> 3.0PR18Q2
2018-02-22 18:19	hudsonbot	Checkin
2018-02-22 18:19	hudsonbot	Note Added: 0102778

Relationships [ Relation Graph ] [ Dependency Graph ]