Anonymous | Login
Project:
RSS
  
News | My View | View Issues | Roadmap | Summary

View Issue Details[ Jump to Notes ] [ Issue History ] [ Print ]
ID
0034072
TypeCategorySeverityReproducibilityDate SubmittedLast Update
feature request[Openbravo ERP] A. Platformminorhave not tried2016-09-23 12:002016-09-26 11:57
ReporterAugustoMauchView Statuspublic 
Assigned ToAugustoMauch 
PrioritynormalResolutionfixedFixed in Version3.0PR16Q4
StatusclosedFix in branchFixed in SCM revision74900cd513e1
ProjectionnoneETAnoneTarget Version
OSAnyDatabaseAnyJava version
OS VersionDatabase versionAnt version
Product VersionSCM revision 
Review Assigned Tocaristu
Web browser
ModulesCore
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary

0034072: 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.
TagsNo tags attached.
Attached Files

- Relationships [ Relation Graph ] [ Dependency Graph ]

-  Notes
(0090202)
hgbot (developer)
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 (developer)
2016-09-23 15:20

 Reviewed
(0090215)
hudsonbot (developer)
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 (manager)
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 [^]

- Issue History
Date Modified Username Field Change
2016-09-23 12:00 AugustoMauch New Issue
2016-09-23 12:00 AugustoMauch Assigned To => AugustoMauch
2016-09-23 12:00 AugustoMauch Modules => Core
2016-09-23 12:00 AugustoMauch Triggers an Emergency Pack => No
2016-09-23 12:18 hgbot Checkin
2016-09-23 12:18 hgbot Note Added: 0090202
2016-09-23 12:18 hgbot Status new => resolved
2016-09-23 12:18 hgbot Resolution open => fixed
2016-09-23 12:18 hgbot Fixed in SCM revision => http://code.openbravo.com/erp/devel/pi/rev/74900cd513e19d00b3d319b4ea70d159290fde96 [^]
2016-09-23 12:18 AugustoMauch Review Assigned To => caristu
2016-09-23 12:20 AugustoMauch Description Updated View Revisions
2016-09-23 15:20 caristu Note Added: 0090210
2016-09-23 15:20 caristu Status resolved => closed
2016-09-23 15:20 caristu Fixed in Version => 3.0PR16Q4
2016-09-23 18:22 hudsonbot Checkin
2016-09-23 18:22 hudsonbot Note Added: 0090215
2016-09-26 11:57 AugustoMauch Note Added: 0090252

Copyright © 2000 - 2009 MantisBT Group
Powered by Mantis Bugtracker