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.
I've tested this behaviour under Windows with JRE 1.6 and JRE 1.7
and Linux with OpenJDK 1.6 and 1.7
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.
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)
(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
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.
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.
I think, this would be a major feature change.
I have so many DB-objects, that this kind of modification would be a glance.
Josef: the fdo#57872 put in "See also" may interest you.
Lionel: should we consider it as a dup?
(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.
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.
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".
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. )
(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.
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
Adding self to CC if not already on
Migrating Whiteboard tags to Keywords: (needsDevEval skillSQL skillCpp)
*** Bug 111813 has been marked as a duplicate of this bug. ***
Created attachment 135603 [details]
Base Advanded options (french)
(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;
--> 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
--> Default options (see attachment in french)
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)
--> ojdbc6.jar (provided by Oracle)
--> Oracle JDBC
Thanks in advance,
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.
*** Bug 115314 has been marked as a duplicate of this bug. ***
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!
I just would like to relaunch this thread and problem. I guess no progress has been made with it. We use now 6.2.x and also tested 6.3 in our company with a Oracle server via Oracle JDBC and it still extremely slow to get some datas via Calc.
Would be very nice if a dev can resolve it in the core :)
(In reply to MichaelB from comment #23)
> I just would like to relaunch this thread and problem. I guess no progress
> has been made with it. We use now 6.2.x and also tested 6.3 in our company
> with a Oracle server via Oracle JDBC and it still extremely slow to get some
> datas via Calc.
> Would be very nice if a dev can resolve it in the core :)
The problem is there are too few people on Base part above all working on proprietary and expensive DBs.
If interested you or some people in your company may contribute:
- financially via bounty or by paying someone to do the job
- by proposing some patches, see https://wiki.documentfoundation.org/Development/GetInvolved to start coding for LO