[Libreoffice-bugs] [Bug 63713] FILEOPEN DB metadata: load lazily / in background / persistent cache file / limit by table filter

[bugzilla-daemon at bugs.documentfoundation.org]

https://bugs.documentfoundation.org/show_bug.cgi?id=63713

--- Comment #22 from Johan <lightworker78 at gmail.com> ---
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!

-- 
You are receiving this mail because:
You are the assignee for the bug.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.freedesktop.org/archives/libreoffice-bugs/attachments/20190319/71c7f6a7/attachment-0001.html>