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.
You need the SDBC driver which is fast.
Unfortunately currently (LO and OO 3.3) it is broken.
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.
*** This bug has been marked as a duplicate of bug 35683 ***
Better yet, go and add the details of your problem to bug 35683.
Or alternatively, use the JDBC3 driver, how does that compare in terms of speed to the JDBC4 driver ?
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.
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.
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'.
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.
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.