0030146: Oracle takes too much time to complete a query

Description

Oracle takes too much time to complete a query

In local, the time required is around 30 seconds

Steps To Reproduce

This issue was detected thanks to these tests:
- CashUpGroupingWithDifferentsBP
- CashUpNoGroupingWithDifferentsBP

The specific query taking too much time could be this one:

"SELECT c_invoice.documentno as c_invoice_documentno, c_order.documentno as c_order_documentno, fin_payment_schedule.fin_payment_schedule_id, c_invoice.ispaid as c_invoice_paymentcomplete, fin_payment_schedule.amount as fin_pmnt_schle_expectedamount, fin_payment_schedule.paidamt as fin_pmnt_schle_receivedamount, c_invoice.grandtotal as c_invoice_grandtotal, c_invoice.totalpaid as c_invoice_totalpaid, c_invoice.outstandingamt as c_invoice_outstandingamt, c_invoice.finalsettlement as c_invoice_finalsettlement, c_invoiceline.line, c_invoice.c_bpartner_id, c_invoiceline.linenetamt, c_invoiceline.line_gross_amount FROM c_invoiceline INNER JOIN c_invoice ON c_invoiceline.c_invoice_id = c_invoice.c_invoice_id LEFT JOIN fin_payment_schedule ON c_invoice.c_invoice_id = fin_payment_schedule.c_invoice_id INNER JOIN c_orderline ON c_invoiceline.c_orderline_id = c_orderline.c_orderline_id INNER JOIN c_order ON c_orderline.c_order_id = c_order.c_order_id WHERE c_invoice.documentno IN ( SELECT c_invoice.documentno FROM c_invoiceline INNER JOIN c_invoice ON c_invoiceline.c_invoice_id = c_invoice.c_invoice_id INNER JOIN c_orderline ON c_invoiceline.c_orderline_id = c_orderline.c_orderline_id INNER JOIN c_order ON c_orderline.c_order_id = c_order.c_order_id WHERE c_order.documentno = 'VBS1001/0000005') ORDER BY c_order_documentno ASC, c_invoiceline.line"

Proposed Solution

maybe "the inner select can go away as it seems that most info is already joined in on the main from clause"

Tags

No tags attached.

Attached Files

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0078174) hgbot (developer) 2015-06-10 13:06	Repository: tools/automation/pi-mobile Changeset: c0cf33eb69253357dc6b77115f4559c84f4e64dd Author: Rafa Alonso <ral <at> openbravo.com> Date: Wed Jun 10 12:19:39 2015 +0200 URL: http://code.openbravo.com/tools/automation/pi-mobile/rev/c0cf33eb69253357dc6b77115f4559c84f4e64dd [^] Related to issue 30146. Deactivated related tests until the issue is fixed - the problem is that Oracle is taking too long to return the query results --- M src-test/org/openbravo/test/mobile/retail/pack/selenium/tests/cashup/CashUpGroupingWithDifferentsBP.java M src-test/org/openbravo/test/mobile/retail/pack/selenium/tests/cashup/CashUpNoGroupingWithDifferentsBP.java ---

(0160454) shuehner (administrator) 2024-02-08 14:44	After mass reviewing old pos issues in 52891 => Closing as out of date

Issue History
Date Modified	Username	Field	Change
2015-06-10 12:17	Orekaria	New Issue
2015-06-10 12:17	Orekaria	Assigned To	=> Retail
2015-06-10 12:17	Orekaria	Triggers an Emergency Pack	=> No
2015-06-10 12:23	Orekaria	Description Updated	View Revisions
2015-06-10 12:24	Orekaria	Proposed Solution updated
2015-06-10 13:06	hgbot	Checkin
2015-06-10 13:06	hgbot	Note Added: 0078174
2016-02-19 16:33	Orekaria	Status	new => acknowledged
2024-02-08 14:44	shuehner	Status	acknowledged => scheduled
2024-02-08 14:44	shuehner	Note Added: 0160454
2024-02-08 14:44	shuehner	Status	scheduled => closed
2024-02-08 14:44	shuehner	Resolution	open => out of date