Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0034072Openbravo ERPA. Platformpublic2016-09-23 12:002016-09-26 11:57
ReporterAugustoMauch 
Assigned ToAugustoMauch 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in Version3.0PR16Q4 
Merge Request Status
Review Assigned Tocaristu
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0034072: Adding an index on c_bpartner_location.c_location will speed certain processes
DescriptionThere are processes that given a location (table c_location) they need to retrieve if it is related with any business partner location (table c_bpartner_location).

This results in the invokation of a query like this:

SELECT *
FROM c_bpartner_location
WHERE c_location_id = ?

In an environment with around 20 million entries in c_location and c_bpartner_location this query takes around 6 seconds. After adding an index in the column c_location of c_bpartner_location the query takes around 250 milliseconds (0000102:0000024 times faster).
Steps To ReproduceIn any database, invoke:

EXPLAIN SELECT *
FROM c_bpartner_location
WHERE c_location_id = <any_c_location_id>

Check that a sequencia scan is done in the c_bpartner_location table.
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2016-09-23 12:00AugustoMauchNew Issue
2016-09-23 12:00AugustoMauchAssigned To => AugustoMauch
2016-09-23 12:00AugustoMauchModules => Core
2016-09-23 12:00AugustoMauchTriggers an Emergency Pack => No
2016-09-23 12:18hgbotCheckin
2016-09-23 12:18hgbotNote Added: 0090202
2016-09-23 12:18hgbotStatusnew => resolved
2016-09-23 12:18hgbotResolutionopen => fixed
2016-09-23 12:18hgbotFixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/74900cd513e19d00b3d319b4ea70d159290fde96 [^]
2016-09-23 12:18AugustoMauchReview Assigned To => caristu
2016-09-23 12:20AugustoMauchDescription Updatedbug_revision_view_page.php?rev_id=13172#r13172
2016-09-23 15:20caristuNote Added: 0090210
2016-09-23 15:20caristuStatusresolved => closed
2016-09-23 15:20caristuFixed in Version => 3.0PR16Q4
2016-09-23 18:22hudsonbotCheckin
2016-09-23 18:22hudsonbotNote Added: 0090215
2016-09-26 11:57AugustoMauchNote Added: 0090252

Notes
(0090202)
hgbot   
2016-09-23 12:18   
Repository: erp/devel/pi
Changeset: 74900cd513e19d00b3d319b4ea70d159290fde96
Author: Augusto Mauch <augusto.mauch <at> openbravo.com>
Date: Fri Sep 23 12:15:25 2016 +0200
URL: http://code.openbravo.com/erp/devel/pi/rev/74900cd513e19d00b3d319b4ea70d159290fde96 [^]

Fixes issue 34072: Adds an index to c_bpartner_location.c_location_id

The index will prevent sequencial scans on c_bpartner_location when a query like this is invoked:

SELECT *
FROM c_bpartner_location
WHERE c_location_id = ?

---
M src-db/database/model/tables/C_BPARTNER_LOCATION.xml
---
(0090210)
caristu   
2016-09-23 15:20   
Reviewed
(0090215)
hudsonbot   
2016-09-23 18:22   
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/25f7de2ab346 [^]
Maturity status: Test
(0090252)
AugustoMauch   
2016-09-26 11:57   
The use case that benefited from the addition of this indexes was the following. There is a multiserver router (see [1]) that given a location it needs to obtain the list of servers that should receive changes done to that location.

As part of that process, the router needs to know if the modified location belongs to a business partner via the c_bpartner_location table (see [2]). It is a that point that the query mentioned in the issue description is invoked.

[1] https://code.openbravo.com/erp/pmods/org.openbravo.retail.storeserver.synchronization/file/b75c3a641421/src/org/openbravo/retail/storeserver/synchronization/router/LocationRouter.java [^]
[2] https://code.openbravo.com/erp/pmods/org.openbravo.retail.storeserver.synchronization/file/b75c3a641421/src/org/openbravo/retail/storeserver/synchronization/router/LocationRouter.java#l93 [^]