Bug 35944 - Accessing bibliography in PostgreSQL database is really slow
Summary: Accessing bibliography in PostgreSQL database is really slow
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Writer (show other bugs)
(earliest affected)
3.3.1 release
Hardware: x86 (IA32) Linux (All)
: medium normal
Assignee: Not Assigned
Depends on:
Reported: 2011-04-04 02:22 UTC by Alexander Pyhalov
Modified: 2012-01-04 09:07 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description Alexander Pyhalov 2011-04-04 02:22:26 UTC
I've configured LibreOffice to get bibliography entries from local postgresql database (postgres 8.4.7) using JDBC driver version 8.4.701-1. Every operation accessing publications table is really slow (e.g. search item by its Identifier when inserting new bibliographic reference). It fully eats one CPU core for about several minutes. Publication table is rather small - about 100 tuples. CPU is used by soffice.bin process, not postgresql itself. DBMS connection is in "idle" state. The same bug (http://openoffice.org/bugzilla/show_bug.cgi?id=28679) affects OpenOffice (at least, all versions I used since 3.1 version). 
On other hand, when you open this database in LibreOffice Database, database access is fast.
Comment 1 Ferry Toth 2011-05-28 14:26:26 UTC
You need the SDBC driver which is fast.

Unfortunately currently (LO and OO 3.3) it is broken.

Comment 2 Alex Thurgood 2011-05-28 22:51:43 UTC

This is not an accurate representation of your problem. How slow is slow (comparative timings please) ? Under which circumstances, i.e. we need the details :

- remote or local postgres server ?
- how many tuples  ?
- complex queries ?
- multiple cascading relationships ;
- JDBC driver version ?

Saying merely it is "slow" will not help either you or us to pinpoint the eventual problems, other than a request such as "please fix the SDBC driver" for which there is already a bug report.

Comment 3 Alex Thurgood 2011-05-28 23:01:10 UTC

*** This bug has been marked as a duplicate of bug 35683 ***
Comment 4 Alex Thurgood 2011-05-28 23:02:01 UTC
Better yet, go and add the details of your problem to bug 35683.

Comment 5 Alex Thurgood 2011-05-28 23:03:56 UTC
Or alternatively, use the JDBC3 driver, how does that compare in terms of speed to the JDBC4 driver ?

Comment 6 Alex Thurgood 2011-05-28 23:07:26 UTC
Sorry, reopening as this concerns the JDBC4 driver and not the SDBC driver. But my comments re the JDBC3 still remain valid - comparative testing/timing reports please.

Comment 7 Ferry Toth 2011-05-29 09:18:41 UTC

You are right. But in absolute numbers it depends on many factors. In relative numbers 'slowless' is also difficult to measure.

What I have noticed that in (Ubuntu) 3.3.2 ALL java based database interfaces (I tried) are in the order of 10x slower then native. In the past (long time since I really tried those) the difference was like 2x.

The problem seems to be with java, and appears to depend on the java version.

You should be able to reproduce the slowness with HSQL, MySQL and also with postgres.

My point really was: why bother, it's more important to get the SDBC driver fixed and avoid all java induced slowness.

Comment 8 Ferry Toth 2011-05-29 11:44:56 UTC

Some concrete measurements.

I opened 2 Forms with LO 3.3.2 in Natty (yes, the version that cannot edit forms). The SDBC driver is not able to enter data, but can retrieve it.

The server is on a gigabit LAN. I am connected with ADSL (up 12Mbs, down 680 kbs effective), through openvpn to the LAN. LO report Sun Java 1.6.0_22 but is actually openjdk-6-jre. JDBC driver is postgresql-9.0-801.jdbc4. SDBC from the Ubuntu repository (1:0.7.6+LibO3.3.2-1ubuntu5).

Both forms get data from a view on the server, I don't think sophisticated queries run on LO.

There appears to be a difference between the first run and the second run, so I measured both, then close LO completely. Using JDBC you see that it takes like 60% of the time to open the form, then the remaining time to fill the form with data. With SDBC it is difficult to say. In all cases 28 records are retrieved (but Form1 and Form2 get different views and also columns of data).

      SDBC 1st run SDBC 2nd run JDBC 1st run JDBC 2nd run
Form1 3 sec.       2 sec.       25 sec.      12 sec.
Form2 2 sec.       < 0.5sec.    20 sec.      8 sec.

So using the JDBC driver you can actually see the records 'coming in'.

I hope you notice that my gut feeling (10x speed difference) is quite accurate. And in my book 10x slower qualifies as 'slow'.

Comment 9 Ferry Toth 2011-05-29 12:07:25 UTC
Oh yeah,

I forgot to mention: opening a view directly as a table gives similar results.

But the real fun happens when you press the 'go to the last record' button in the navigation thingy below the table.

With the SDBC driver this takes < 1sec. With the JDBC driver I am still waiting, while typing this message (> 5 minutes)........

.... no it didn't crash. There are my data, more then 300 sec. slower then with the SDBC driver (300x slower).

Today the JDBC driver is not completely useless, it allows my to enter data in the table. The SDBC driver has the add record 'thingy' at the bottom of the table disabled.

The number of records in this table is 6312.

Comment 10 Alex Thurgood 2012-01-04 09:07:17 UTC
I am going to close this report as solved because the postgres sdbc driver should now be integrated into the 3.5 builds as a connector extension. If that doesn't happen to be the case for any particular OS, please re-open this bug report.