Openbravo Issue Tracking System - Openbravo ERP
View Issue Details
IDProjectCategoryView StatusDate SubmittedLast Update
0047047Openbravo ERPY. DBSourceManagerpublic2021-06-09 22:172021-06-11 10:18
Reportershuehner 
Assigned Toshuehner 
PrioritynormalSeverityminorReproducibilityhave not tried
StatusclosedResolutionfixed 
PlatformOS5OS Version
Product Version 
Target VersionFixed in VersionPR21Q3 
Merge Request Status
Review Assigned To
OBNetwork customer
Web browser
ModulesCore
Support ticket
Regression level
Regression date
Regression introduced in release
Regression introduced by commit
Triggers an Emergency PackNo
Summary0047047: Reading tables part of PostgreSQL modelloader could be simplified and speed up
Descriptionthe readTable overloaded function in the PostgreSqlModelLoader takes quite some extra time on top of the standard super.readTable() call.

Several things could be simplified:
1.) Add 'column comment' pl-function as an extra column to the _stmt_listcolumns statment.
That allows to remove 1 sql executed per every column read
Additionally it allows to remove the extra utility sql's _stmt_column_indexes, _stmt_comments_tables as inside the _stmt_listcolumns we have direct access to the needed values for free
Also that allows to remove the query for the table oid just needed for those utility sql's
2.) After 47036 the only caller of readTable is the ModelLoaderBase.readTable function. That allows to change the calling contract to state "tablename must be correct case". Doing that allows us to direct return to correct case via the _stmt_listtables (for PG variant) and remove the extra SQL extra _stmt_oids_tables for an extra (small) benefit
Steps To Reproduce-
Proposed Solution
Additional Information
TagsNo tags attached.
Relationships
depends on feature request 0047036PR21Q3 closed shuehner Remove lots of obsolete support code for obsolete (update.database.mod, ezattributes module, dbmanager.jar references) 
Attached Files
Issue History
Date ModifiedUsernameFieldChange
2021-06-09 22:17shuehnerNew Issue
2021-06-09 22:17shuehnerAssigned To => shuehner
2021-06-09 22:17shuehnerModules => Core
2021-06-09 22:17shuehnerTriggers an Emergency Pack => No
2021-06-09 22:17shuehnerRelationship addeddepends on 0047036
2021-06-09 22:52hgbotNote Added: 0129290
2021-06-10 13:31hgbotNote Added: 0129334
2021-06-10 13:31hgbotNote Added: 0129335
2021-06-10 13:31hgbotNote Added: 0129336
2021-06-10 13:31hgbotNote Added: 0129337
2021-06-10 13:31hgbotNote Added: 0129338
2021-06-11 10:18hgbotResolutionopen => fixed
2021-06-11 10:18hgbotStatusnew => closed
2021-06-11 10:18hgbotFixed in Version => PR21Q3
2021-06-11 10:18hgbotNote Added: 0129395

Notes
(0129290)
hgbot   
2021-06-09 22:52   
Merge Request created: https://gitlab.com/openbravo/product/dbsm/-/merge_requests/30 [^]
(0129334)
hgbot   
2021-06-10 13:31   
Repository: https://gitlab.com/openbravo/product/dbsm [^]
Changeset: 31fc2d690724e38027adcbc0fb7288fc67511330
Author: Stefan Huehner <stefan.huehner@openbravo.com>
Date: 2021-06-10T13:30:16+02:00
URL: https://gitlab.com/openbravo/product/dbsm/-/commit/31fc2d690724e38027adcbc0fb7288fc67511330 [^]

ISSUE-47047: Speedup 'Reading tables...' on postgresql

Speed up reading the comment of columns (which can contain i.e. our
NVARCHAR) marker.
Do that by:
- Adding the col_description pl-function call directly into the SQL to
  read all other column definition (_stmt_listcolumns)
- That allows to move the commentCol processing into the readColumn part
  For that overload the readColumn with a small postgres specific
version
- That allows to remove the loop in the readTable which before ran +1
  SQL per column to retrieve the commentCol one by one

---
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlModelLoader.java
---
(0129335)
hgbot   
2021-06-10 13:31   
Repository: https://gitlab.com/openbravo/product/dbsm [^]
Changeset: 1aca895fea3c4d7a0e051492f0cae2b6c15e3501
Author: Stefan Huehner <stefan.huehner@openbravo.com>
Date: 2021-06-10T13:30:16+02:00
URL: https://gitlab.com/openbravo/product/dbsm/-/commit/1aca895fea3c4d7a0e051492f0cae2b6c15e3501 [^]

ISSUE-47047: Speedup Reading tables - part2

After previous change the columnIndexes array in pg readTable is no
longer needed.
Remove it together with it's SQL _stmt_column_indexes removing one query
per readTable call.

---
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlModelLoader.java
---
(0129336)
hgbot   
2021-06-10 13:31   
Repository: https://gitlab.com/openbravo/product/dbsm [^]
Changeset: 3cb271550d67864b2d2b6627d438bde225985e74
Author: Stefan Huehner <stefan.huehner@openbravo.com>
Date: 2021-06-10T13:30:16+02:00
URL: https://gitlab.com/openbravo/product/dbsm/-/commit/3cb271550d67864b2d2b6627d438bde225985e74 [^]

ISSUE-47047 Speedup PG Reading tables - part3

Change calling contract for ModelLoaderBase.readTable(tablename)

Now it must be called with tablename in the correct case.
Only caller left (after cleanup from 47036) in readTables()
Modify the (pg specific) query within to return the proper case
That allows to remove the extra query done in PG.readTable to find the
proper case (_stmt_oids_tables). Note that the 2nd field queried in
there before (table oid) was made obsolete by the previous commit also.

Runtime of Reading tables ... for part1..3 (on example i3-4130)
Before: 8.7 .. 9.7 seconds
After: 5.8 seconds

---
M src/org/apache/ddlutils/platform/ModelLoaderBase.java
M src/org/apache/ddlutils/platform/postgresql/PostgreSqlModelLoader.java
---
(0129337)
hgbot   
2021-06-10 13:31   
Repository: https://gitlab.com/openbravo/product/dbsm [^]
Changeset: 669130221ee180241bf73a5ddd7a4bbbf72ac7cf
Author: Stefan Huehner <stefan.huehner@openbravo.com>
Date: 2021-06-10T13:30:16+02:00
URL: https://gitlab.com/openbravo/product/dbsm/-/commit/669130221ee180241bf73a5ddd7a4bbbf72ac7cf [^]

ISSUE-47047: Move code-comment to be javadoc style

Move important note on spelling of tablename to a javadoc comment to
make it a bit more visible.

---
M src/org/apache/ddlutils/platform/ModelLoaderBase.java
---
(0129338)
hgbot   
2021-06-10 13:31   
Merge request merged: https://gitlab.com/openbravo/product/dbsm/-/merge_requests/30 [^]
(0129395)
hgbot   
2021-06-11 10:18   
Directly closing issue as related merge request is already approved.

Repository: https://gitlab.com/openbravo/product/openbravo [^]
Changeset: 5c9721a21dc89e884c13b3bfb31df376e727ffdb
Author: Stefan Huehner <stefan.huehner@openbravo.com>
Date: 2021-06-11T10:17:06+02:00
URL: https://gitlab.com/openbravo/product/openbravo/-/commit/5c9721a21dc89e884c13b3bfb31df376e727ffdb [^]

fixes ISSUE-47036; fixes ISSUE-45729; fixes ISSUE-47047; fixes ISSUE-28703

dbsm including the fixes for:

ISSUE-47036: Remove lots of obsolete support code for obsolete
    (update.database.mod, ezattributes module, dbmanager.jar references)
ISSUE-45729: export.database fails with some views on oracle with:
    An invalid XML character (Unicode: 0x0) was found in the CDATA section.
ISSUE-47047: Reading tables part of PostgreSQL modelloader could be simplified and speed up
ISSUE-28703: dbmanager.jar & src-db/src removal

---
M src-db/database/lib/dbsourcemanager.jar
---