Bug 63713 - FILEOPEN DB metadata: load lazily / in background / persistent cache file / limit by table filter
Summary: FILEOPEN DB metadata: load lazily / in background / persistent cache file / l...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
4.0.2.2 release
Hardware: Other All
: low enhancement
Assignee: Not Assigned
URL:
Whiteboard:
Keywords: needsDevEval, perf, skillCpp, skillSql
: 111813 115314 (view as bug list)
Depends on:
Blocks: Database-Import
  Show dependency treegraph
 
Reported: 2013-04-19 09:10 UTC by josef
Modified: 2019-03-19 12:32 UTC (History)
9 users (show)

See Also:
Crash report or crash signature:


Attachments
Base Advanded options (french) (76.60 KB, image/png)
2017-08-17 07:06 UTC, MichaelB
Details
Video that demonstrate the problem #63713 #111813 (2.62 MB, video/mp4)
2017-08-22 07:31 UTC, MichaelB
Details

Note You need to log in before you can comment on or make changes to this bug.
Description josef 2013-04-19 09:10:55 UTC
Connecting to our Oracle DB takes quite long for
e.g. the first click on "Tables" or opening a query or form.

maybe this has to do with too many DB Objects.

Is it possible to reduce this loading time?

After first time connection to the DB there is no speed problem any more.
Comment 1 josef 2013-04-19 09:14:48 UTC
I've tested this behaviour under Windows with JRE 1.6 and JRE 1.7
and Linux with OpenJDK 1.6 and 1.7
Comment 2 josef 2013-04-19 09:23:05 UTC
used JDBC-Drivers:

ojdbc6.jar
ojdbc14.jar

it seems,
Libreoffice reads all metadata for all found DB-Objects.
Maybe there could be a feature that limits this function only to those objects that are listet in tables filter.
Comment 3 Julien Nabet 2013-05-04 21:05:42 UTC
Which LO version do you use? FYI, last one is 4.0.2 and 4.0.3 is gonna be released in a few days (see https://wiki.documentfoundation.org/ReleasePlan#4.0_release)
Comment 4 josef 2013-05-05 09:38:46 UTC
(In reply to comment #3)
> Which LO version do you use? FYI, last one is 4.0.2 and 4.0.3 is gonna be
> released in a few days (see
> https://wiki.documentfoundation.org/ReleasePlan#4.0_release)

Actually this has been since I've been using LibreOffice Base with Oracle DB.
Now I'm using version 4.0.2.
As soon as possible I'll change to version 4.0.3 but the change log doesn't describe any feature change.
Comment 5 Julien Nabet 2013-05-05 14:01:13 UTC
josef: thank you for your feedback. I'll put 4.0.2 for the moment for Version field.
About 4.0.3, it was just for information, I don't know if something related to your problem has been modified/fixed in this version.
Comment 6 josef 2013-05-06 05:54:59 UTC
I think, this would be a major feature change.
I have so many DB-objects, that this kind of modification would be a glance.
Comment 7 Julien Nabet 2013-05-08 07:34:54 UTC
Josef: the fdo#57872 put in "See also" may interest you.

Lionel: should we consider it as a dup?
Comment 8 Lionel Elie Mamane 2013-05-08 07:44:42 UTC
(In reply to comment #7)
> Lionel: should we consider it as a dup?

No. bug 57872 is about managing the "C++ to Java call overhead", while this bug is about less eager (more lazy) caching of metadata.
Comment 9 Julien Nabet 2013-05-08 07:49:17 UTC
Lionel: Ok I thought (wrongly now I've read your feedback) that metadata should always be completely loaded and so that it was only due to the slowliness pb.
Comment 10 Lionel Elie Mamane 2013-05-10 14:10:52 UTC
Alternatively, we could cache the metadata information in the file, and refresh it only on explicit request by the user.

That's what MS Access does (with "linked tables"). I find that rather suck and not that user-friendly, actually. So I think I'd prefer the lazy loading. Or maybe background loading?


Summary of ways improving this:

1) Load metadata lazily

2) Load metadata in background

3) Cache metadata (negative vote from me)

4) Load only metadata of tables that match the tables filter.


4) can be combined with any of 1/2/3, and should be the easiest. Setting "PropôsedEasyHack" for this point "4".
Comment 11 Terrence Enger 2013-05-15 18:14:36 UTC
The behaviour that I complained about in bug 52027 sounds similar.  I observed it with an ODBC connection but L.E.M. commented that the problem is common to all backends.

By 2012-10-07 the problem was no longer evident.  I wonder if the current bug is something different or if my older problem has returned.

( I am almost of the net, so will be slow to contribute further.  Sigh. )

Terry.
Comment 12 Terrence Enger 2013-05-16 13:15:41 UTC
(In reply to comment #11)
The problem of bug 52027 is still absent in master commit 6faa622
pulled 2013-04-19.  I conclude the the present bug is something
different, and my earlier comment is mere noise.
Comment 13 Joel Madero 2014-02-27 23:28:46 UTC
In order to limit the confusion between ProposedEasyHack and EasyHack and to make queries much easier we are changing ProposedEasyHack to NeedsDevEval.

Thank you and apologies for the noise
Comment 14 Alex Thurgood 2015-01-03 17:40:45 UTC Comment hidden (no-value)
Comment 15 Robinson Tryon (qubit) 2015-12-14 04:53:49 UTC Comment hidden (obsolete)
Comment 16 Alex Thurgood 2017-08-16 07:23:20 UTC
*** Bug 111813 has been marked as a duplicate of this bug. ***
Comment 17 MichaelB 2017-08-17 07:06:59 UTC
Created attachment 135603 [details]
Base Advanded options (french)
Comment 18 MichaelB 2017-08-17 07:07:25 UTC
(In reply to Alex Thurgood from comment #16)
> *** Bug 111813 has been marked as a duplicate of this bug. ***

- which OS(es) you are using;
--> Problem on Linux Ubuntu (all) and Windows (all)

- if you are using a Linux OS, which distribution and version number;
--> Ubuntu 16.04 (older and probably new version too)

- if you are using a Linux OS, whether or not you are using the Linux distribution provided version of LibreOffice or the one provided by TDF from its download page;
--> From PPA but same problem with distribution provided version

- which version of the JRE, and/or JDK;
--> openjdk-8-jre
--> ojdbc6.jar (provided by Oracle)

- which options, if any, you have entered on the JDBC driver configuration line, and which options are ticked in the ODB file under the Advanced Properties tab.
--> Oracle JDBC
--> oracle.jdbc.driver.OracleDriver
--> Default options (see attachment in french)
Comment 19 MichaelB 2017-08-22 07:31:26 UTC
Created attachment 135719 [details]
Video that demonstrate the problem #63713 #111813

I made a little video for this problem / bug.

You can see that it have a big delay between authentication and request (> 25 seconds).

In my case i have a query parameter but this problem appear also without parameter.

You can see also that after entering the parameter (YEAR: 2017) the result is fast. So the problem is not the query but before, like explained in this bug.

Please can someone can apply a patch to resolve this (this problem exist from many years). It is on a business environment and this delay are too long for daily usage.

--> Ubuntu 16.04 (i7 / 8Go)
--> LibreOffice (5.4.x but are on ALL older version) 

--> openjdk-8-jre
--> ojdbc6.jar (provided by Oracle)

--> Oracle JDBC
--> oracle.jdbc.driver.OracleDriver

Thanks in advance,
Michael
Comment 20 Julien Nabet 2017-08-22 07:45:01 UTC
MichaelB: thank you for your feedback but the problem is already known, it concerns the load of metadata (see https://bugs.documentfoundation.org/show_bug.cgi?id=63713#c8).

You've got 4 choices:
- waiting for a fix
- use something else than LO
- you can submit a patch (if you know coding obviously)
- you can pay someone so he/she submits a patch

Have in mind that, badfully, there's only 1 expert dev on Base part.
Comment 21 Alex Thurgood 2018-01-31 08:30:53 UTC
*** Bug 115314 has been marked as a duplicate of this bug. ***
Comment 22 Johan 2019-03-19 12:32:56 UTC
We ran into this issue as well. Refreshable Calc/Base dashboards are shared per mail. Each time a dashboard is opened(first database connect), the user has to wait for a very long time. Sometime up to many minutes. There are two causes:

1. Base reads the entire Oracle Data Catalogue and others (<SCHEMA>.ALL_OBJECTS, <SCHEMA>.ALL_SYNONYMS, <SCHEMA>.ALL_CONSTRAINTS, <SCHEMA>.ALL_CONS_COLUMNS). The Oracle Data Catalogue queries in general are slow.

2. ODBC FetchBufferSize (Windows and Linux) is by default low. Causing many round trips with the database. Over a slow link this gives huge delays, up to minutes! If I recall correctly by default Base fetches 10 rows a time. A bigger Pre Fetch buffers helps tremendously.

Workarounds / solutions:

1. For the 1st issue, I've created (lets call it a less then favourable) workaround. But works very well. Create a schema(user), and override/redirect the existing ALL_* views to the USER_* views. The ALL_* views contains many records, while in general the USER_* views are relatively small. This schema can be used to create your own tables(will end up in USER_* views) and use them in Base queries. But on top of that, you can link(synonym) to tables in other schemas (won't end up in USER_* views automatically, but can be inserted manually if needed). 

create or replace view ' || UserID || '.ALL_OBJECTS      as select ''' || UserID || ''' as "OWNER", uo.* from user_objects  uo
create or replace view ' || UserID || '.ALL_SYNONYMS     as select ''' || UserID || ''' as "OWNER", us.* from user_synonyms us
create or replace view ' || UserID || '.ALL_CONSTRAINTS  as select * from user_constraints
create or replace view ' || UserID || '.ALL_CONS_COLUMNS as select * from user_cons_columns

create or replace synonym ' || UserID || '.' || TableName || ' for ' || BaseUserID || '.' || TableName

2. Set FetchBufferSize to a much larger value. I've experimented a bit and 1024000 work very well.

By applying these workarounds, we:

1. Have no initial connect delays anymore
2. All Base queries are fast (as quick as DB can deliver)

Base fix suggestions:

1. In Base, specifically when connecting to an Oracle Database, an option could be added to *not* query the ALL_* views, but only the USER_* views. This shouldn't be to hard to implement?
2. In Base, create options to influence the pre-fetch buffer sizes and the number of (pre-)fetched rows per round trip. Not sure though to what extend these options can be influenced when using abstraction layers like ODBC. 

By default, connecting to an Oracle Database will always be slow. And I think that should not be the case.


Thanks everybody for all the work! It's awesome to have an Open Source Office Suite!