Ross,
You have done an amazing job analyzing this.... I believe, however, that
this question should be directed to the dba list directly. I would like
to hear what the dba developers have to say about this. I am not certain
if this would be better answered at [email protected] or
[email protected] but for now I will submit this question to
[EMAIL PROTECTED]
I know that you are not currently subscribed to these lists, so I
included your email address as a CC so that hopefully any replies will
also copy you. You might want to consider subscribing to these mailing
lists.....
Ross Johnson wrote:
[Sorry - resending because the timing results table was mangled.]
This is my first post to this list and my first project using OOo Forms.
I apologise for the length of the message ...
Some background:
I'm converting a database from MS Access to OOo/PostgreSQL. Mostly this
has gone very well using mdbtools to migrate the database to PostgreSQL
and using JDBC to connect OOo to PostgreSQL as the data source (using
PostgreSQL.org's official JDBC3 driver). I'm using OOo V2.0.1, Sun's
J2SE 1.5 (JRE 5.0), and PostgreSQL 8.1, all running on the same machine
which is Fedora Core 3 Linux. The machine is Intel X86 2.4GHz, 1Gbyte
RAM.
Problem:
One of the tables in the database has a very large number of columns
(251) and I've built a single large form in Calc bound to this table.
All of the columns in the table are represented by fields in the form.
The problem is: it takes several minutes for the form to go active with
the first record. If I reduce the number of form fields then it loads
much faster, (timing tests below show that the time to load increases
"exponentially" with the number of form fields).
If I split the form into several smaller forms accessing different
columns of the same table the delays are reasonable but still slower
than I would expect.
Trying to pin-point where the delay is: if I display the data source
viewer and select the table, it takes about 5 seconds to display the
table (first 11 records) and only 2 or 3 seconds more to load all 2360
records when I click the go-to-last-record button on the navigator
toolbar. This seems to rule out PostgreSQL, the JDBC driver or other
parts of the connection path, or accessing/converting either the data or
the table metadata (column names etc).
There is also no disk activity (e.g. page swapping) during the delay, so
that would seem to rule out memory resource problems.
Another observation that may provide a clue is: if I open the document
in design mode and then click design mode off it takes several minutes
before the data source username/password dialog appears. This suggests
that something is going on before any attempt is made to access the
database server.
To comfirm, I progressively cut the form down, saved, re-opened and
connected the form to the data source to time the delays. Here are the
results (times are in seconds of course):
"Fields" "Connect time" "Load time" "Close time"
13 2 8.7 0.5
71 37 9.6 3
90 52 10 4.2
191 89 12 9
222 176 12 15.5
328 406 17 46
- Connect time is from clicking Form Design button to “off” and the
appearance of the data source authentication dialog.
- Load time is from completing authentication to appearance of first
record data in form.
- Close time is from clicking Form Design button “on” to refresh of
frame in design mode.
[In case you're wondering why there are more fields than columns, some
columns in the table are bound to multiple radio buttons and each one is
counted as a field in the table above.]
So, while it's clearly related to the number of form fields, I don't
believe it's the size of the table (# of columns) that is causing the
problem.
I've also tried increasing maximum Java heap space from 64M to 128M
(after getting an exception at one stage with the multiple forms
experiment). This had no effect on the timing.
Any clues on how to fix or work around this problem would be greatly
appreciated. Any insights as to what is actually going on would also be
most welcome.
Thanks.
Ross Johnson
--
Andrew Pitonyak
My Macro Document: http://www.pitonyak.org/AndrewMacro.odt
My Book: http://www.hentzenwerke.com/catalog/oome.htm
Info: http://www.pitonyak.org/oo.php
See Also: http://documentation.openoffice.org/HOW_TO/index.html
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
