0050302: Adding Index on the Orderline and Invoiceline table to improve the performance.

Description

Delete on the m_offer table causing IO and contention.

The delete was only for one offer but seems to be slow due to the foreign key in other tables referring to the m_offer_id column on the m_offer table.
When there is a constraint postgresql check if there is any row pointing to the deleted entry.

Steps To Reproduce

The following tables pointing to the m_offer without index on the reference column
1. c_orderline
2. c_orderline_offer
3. c_invoiceline
4. c_invoiceline_offer

Fix: Create indexes on the m_offer_id column on the 4 tables.

Tags

No tags attached.

Attached Files

50302.diff [^] (2,915 bytes) 2022-10-06 08:10 [Show Content] [Hide Content]

diff --git a/src-db/database/model/tables/C_INVOICELINE.xml b/src-db/database/model/tables/C_INVOICELINE.xml
index b7414f808e4451ffb0dfdecc9187d109b0a1ff71..1fbc14b8b458df59cab33903de370d4ac31c0513 100644
--- a/src-db/database/model/tables/C_INVOICELINE.xml
+++ b/src-db/database/model/tables/C_INVOICELINE.xml
@@ -315,6 +315,9 @@
       <index name="C_INVOICELINE_INVOICE" unique="false">
         <index-column name="C_INVOICE_ID"/>
       </index>
+      <index name="C_INVOICELINE_MOFFER" unique="false">
+        <index-column name="M_OFFER_ID"/>
+      </index>
       <index name="C_INVOICELINE_ORDERLINE" unique="false">
         <index-column name="C_ORDERLINE_ID"/>
         <whereClause><![CDATA[C_ORDERLINE_ID IS NOT NULL]]></whereClause>
diff --git a/src-db/database/model/tables/C_INVOICELINE_OFFER.xml b/src-db/database/model/tables/C_INVOICELINE_OFFER.xml
index 6d09a4588b7bf4730b63ff65a626384a40b6a65a..a346f48916182f8e9d951b8a767086c3b7ac9fe0 100644
--- a/src-db/database/model/tables/C_INVOICELINE_OFFER.xml
+++ b/src-db/database/model/tables/C_INVOICELINE_OFFER.xml
@@ -77,6 +77,9 @@
       <foreign-key foreignTable="M_OFFER" name="C_INVOICELINE_OFFER_M_OFFER">
         <reference local="M_OFFER_ID" foreign="M_OFFER_ID"/>
       </foreign-key>
+      <index name="C_INVOICELINEOFFER_MOFFER" unique="false">
+        <index-column name="M_OFFER_ID"/>
+      </index>
       <index name="C_INVOICEOFFER_LINE_IDX" unique="false">
         <index-column name="C_INVOICELINE_ID"/>
       </index>
diff --git a/src-db/database/model/tables/C_ORDERLINE.xml b/src-db/database/model/tables/C_ORDERLINE.xml
index 9bbcd688d84f838f3ba2dcd0bdd7384db6ca07c8..1707f8cf5ba8bfeeb1b9c35d883c391cd855b32a 100644
--- a/src-db/database/model/tables/C_ORDERLINE.xml
+++ b/src-db/database/model/tables/C_ORDERLINE.xml
@@ -396,6 +396,9 @@
         <index-column name="M_INOUTLINE_ID"/>
         <whereClause><![CDATA[M_INOUTLINE_ID IS NOT NULL]]></whereClause>
       </index>
+      <index name="C_ORDERLINE_MOFFER" unique="false">
+        <index-column name="M_OFFER_ID"/>
+      </index>
       <index name="C_ORDERLINE_ORDER" unique="false">
         <index-column name="C_ORDER_ID"/>
         <index-column name="functionBasedColumn" functionExpression="OBEQUALS(QTYORDERED,QTYINVOICED)"/>
diff --git a/src-db/database/model/tables/C_ORDERLINE_OFFER.xml b/src-db/database/model/tables/C_ORDERLINE_OFFER.xml
index 25968d1ccb2a5666b95a9a9102f08d5b4c74b872..e57a9df481dff67a7902feda079bcc948ea99791 100644
--- a/src-db/database/model/tables/C_ORDERLINE_OFFER.xml
+++ b/src-db/database/model/tables/C_ORDERLINE_OFFER.xml
@@ -80,5 +80,8 @@
       <index name="C_ORDERLINEOFFER_LINE_IDX" unique="false">
         <index-column name="C_ORDERLINE_ID"/>
       </index>
+      <index name="C_ORDERLINEOFFER_MOFFER" unique="false">
+        <index-column name="M_OFFER_ID"/>
+      </index>
     </table>
   </database>

Relationships [ Relation Graph ] [ Dependency Graph ]

Notes
(0141710) marvintm (manager) 2022-10-06 08:08	Adding those indices for all customers doesn't seem to be a good decision, as it will make insertion slow for all of them ,and especially the orderline and invoiceline tables are generally already quite big, but at the same time it will not necessarily provide a huge benefit for them. If some customer in particular needs those indices, they can be added for them specifically.

Issue History
Date Modified	Username	Field	Change
2022-09-21 15:00	swathi_kamal	New Issue
2022-09-21 15:00	swathi_kamal	Assigned To	=> Retail
2022-09-21 15:00	swathi_kamal	Triggers an Emergency Pack	=> No
2022-09-27 08:41	ranjith_qualiantech_com	Assigned To	Retail => ranjith_qualiantech_com
2022-09-27 08:41	ranjith_qualiantech_com	Status	new => scheduled
2022-10-06 08:08	marvintm	Note Added: 0141710
2022-10-06 08:08	marvintm	Status	scheduled => closed
2022-10-06 08:08	marvintm	Resolution	open => no change required
2022-10-06 08:08	marvintm	Review Assigned To	=> marvintm
2022-10-06 08:10	ranjith_qualiantech_com	File Added: 50302.diff