Extremely Slow Base Report Builder

users
#1

Greetings,
I have installed LibreOffice (LO) 3.5.3.2 and I am quite pleased with it. I am a long-time OpenOffice (OOo) user going back to 1.0 and even Star Office before that and after not being able to install the latest Apache OpenOffice (AOO) version, I switched to LibreOffice. I say that to confirm I am a new user to LO, but somewhat experienced with the *Office tree.

I used Oracle Report Builder 1.2.1-rev2 on my OOo 3.3.0 for some time and expected the LO Report Builder to be fine, especially since configuring & importing my MySQL databases, forms, queries, etc. worked great. Report Builder does seem to work - sort of- but it is extremely slow when executing the report. So slow, it is unusable; taking minutes to display a report in Writer. Then it keeps repeatedly going out to lunch for long periods so I cannot do anything with the report in Writer, such as scrolling down to examine the report. I am using the following database server:

    MySQL 5.0.67
    MySQL-connector-java-5.0.7-bin.jar
    Sun Java 1.6.0u11

A few months ago, I had a similar problem with OOo 3.3.0 after I updated my Java RE to 1.6.0u30. I posted my problem on the OOo Users forum and was told OOo 3.3.0 will only work with Java up to u22. That problem did not just affect Report Builder, but the entire Base functionality. This time with LO, the non-Report Builder Base functions work fine, at "normal" speed. I do not see any hesitation with Base form data entry.

Is my current Report Builder problem caused by this Java problem again?
Unlike OOo 3.3, will LO 3.5.3 work with the later Java versions and should I try updating my Java again?
I also have a Free Software Foundation Java 1.4.2 installed, but I think I read somewhere not to use it with LO.
Any suggestions where to look would be appreciated. Thanks in advance.
Girvin Herr

#2

With LibO 3.5 under Linux I can use any recent Java version without any issues. I tested 1.6.22, 1.6.31 and 1.7. Any of these simply work with my Java databases.
I avoid the report builder like illness. If you feel the need to dance with this software diva then you should do all calculations, filtering, grouping and sorting in SQL leaving the only good looking stuff up to the beast.

#3

Hi Girvin,

   MySQL 5.0.67
   MySQL-connector-java-5.0.7-bin.jar
   Sun Java 1.6.0u11

Oooh, very old and potentially highly exploitable version of Java there.

Is my current Report Builder problem caused by this Java problem again?

Yes.

Unlike OOo 3.3, will LO 3.5.3 work with the later Java versions and
should I try updating my Java again?

I have whatever version of Java Apple have grudgingly agreed to give me
(u31, apparently), and apart from the usual flakiness of the whole ORB
reporter extension, it works, but I won't even bother for large datasets
because on the Mac it regularly crashes. However, it does seem to be
more stable on other OSes. I would try upgrading the Java version to
something more recent first though.

It has been said, and I certainly found on Linux that u22 was still
pretty fast, although it too, had some nasty security issues. Much has
been written on this list in the past about how to keep several versions
of the JDK on your system at the same time, and then use only the one
you need with the best performance for LO.

Alex

#4

Andreas Säger wrote:

With LibO 3.5 under Linux I can use any recent Java version without any issues. I tested 1.6.22, 1.6.31 and 1.7. Any of these simply work with my Java databases.
I avoid the report builder like illness. If you feel the need to dance with this software diva then you should do all calculations, filtering, grouping and sorting in SQL leaving the only good looking stuff up to the beast.

Andreas,
Thanks for your reply. Report Builder (RB) has always been a "beast" to me too, but it is the only option I know of for printing my database data in a human readable form. So, I have to live with it, at least until I find something better. Before Base, I was using Rekall, which had some nice features. But that Open Source version is no longer supported, so that option is dead.
I am using a SQL query to sort and provide all the data to Report Builder. I am not using any RB grouping or filtering either. It is just a "flat" data set for RB to pretty up for printing. That is why I don't see why it is so slow. All the real time consumers, such as sorting, are being done by MySQL. Also, after writer displays my first page or two, it goes out to lunch again, as if it is recalculating everything all over again. That is frustrating, since it locks up and I have to wait several more minutes for another couple-second window where I can scroll or kill it before it goes off again.
I will certainly take your experience to heart and your recommendation to update my Java.
Thanks again.
Girvin

#5

Alexander Thurgood wrote:

Hi Girvin,

   MySQL 5.0.67
   MySQL-connector-java-5.0.7-bin.jar
   Sun Java 1.6.0u11
    
Oooh, very old and potentially highly exploitable version of Java there.

Is my current Report Builder problem caused by this Java problem again?
    
Yes.

Unlike OOo 3.3, will LO 3.5.3 work with the later Java versions and
should I try updating my Java again?
    
I have whatever version of Java Apple have grudgingly agreed to give me
(u31, apparently), and apart from the usual flakiness of the whole ORB
reporter extension, it works, but I won't even bother for large datasets
because on the Mac it regularly crashes. However, it does seem to be
more stable on other OSes. I would try upgrading the Java version to
something more recent first though.

It has been said, and I certainly found on Linux that u22 was still
pretty fast, although it too, had some nasty security issues. Much has
been written on this list in the past about how to keep several versions
of the JDK on your system at the same time, and then use only the one
you need with the best performance for LO.

Alex

Alex,
Thanks for your reply.
I will take your recommendation to update my Java to heart and do so.
My datasets (tables) are typically over 1000 records. The dataset I am working on now, a parts inventory, is over 1500 records and I am only, maybe, 10% done with it. So I still have a long way to go. This is the first time I have seen Report Builder brought to its knees. It has always been a bit slow, but this is ridiculous. I have had other datasets with even more records, that RB has processed with reasonable speed in the past. That is what makes this problem so strange. It is like I reached some sort of threshold or something.
I wouldn't think keeping more than one Java version on my machine at once would be a problem. I know Sun/Oracle recommends removing any old versions, but it should work without doing that. Especially *Office, which allows a specific version to be selected. I also have a copy of the Free Software Foundation Java, and it seems to co-reside with the "Sun" Java. However, I don't know what app on my machine is using it.

Thanks again for your help.
Girvin

#6

Am 24.05.2012 21:01, Girvin R. Herr wrote:

I will certainly take your experience to heart and your recommendation
to update my Java.
Thanks again.
Girvin

If you are running Java 1.7 downgrading to some recent 1.6 version may be even more helpful. Until now I could not test RB with Java 1.7 and LibO. I disabled that extension several versions ago. Now I can not recall how to enable it again.

#7

Am 24.05.2012 21:33, Andreas Säger wrote:

Am 24.05.2012 21:01, Girvin R. Herr wrote:

I will certainly take your experience to heart and your recommendation
to update my Java.
Thanks again.
Girvin

If you are running Java 1.7 downgrading to some recent 1.6 version may
be even more helpful. Until now I could not test RB with Java 1.7 and
LibO. I disabled that extension several versions ago. Now I can not
recall how to enable it again.

OK, I built a new report with the report builder of version 3.5.3 under Linux-32 with Java 1.7.0_04 and it seems to work.
Create report using the wizard...
I've chosen a query with aggregation and grouping and clicked the "Finish" button.
The database is an external HSQLDB 2.2.8 opened in cached mode.

#8

Hi :slight_smile:
I think there is another way to build reports?  I think Andreas uses a different way inside Base.  Other people seem to start with the report builder and then hack it around.  It might be worth search the archives to find how Andreas does reports!
Regards from
Tom :slight_smile:

#9

Hi :slight_smile:
I have just done a bit of digging around.

Looking back through Andreas' answers
http://nabble.documentfoundation.org/template/NamlServlet.jtp?macro=user_nodes&user=254826&i=80

I found this but i'm not sure it's much help for you
http://nabble.documentfoundation.org/database-td3926217i20.html#a3928210

This one seems to expand on the useful line at the end there
http://nabble.documentfoundation.org/Base-3-5-2-2-hangs-td3910280.html#a3911654

This one is a bit of an overview with nothing specific but might be useful to skim through for ideas
http://nabble.documentfoundation.org/Address-Books-td3829696.html#a3832514

This one looks brilliant to me
http://nabble.documentfoundation.org/Base-and-new-Report-Builder-in-3-5-td3817159.html#a3819387

There is some documentation at
https://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Base_Guide
and an FAQ (translated from the French team's) at
https://wiki.documentfoundation.org/Faq

Sadly most of the people that know a lot about the ins-and-outs of Base are unwilling to help write the Base guide so it's down to 1 person who is desperately busy with a lot of other projects.

Regards from
Tom :slight_smile:

#10

Am 25.05.2012 09:35, Tom Davies wrote:

Hi :slight_smile:
I think there is another way to build reports? I think Andreas uses a different way inside Base. Other people seem to start with the report builder and then hack it around. It might be worth search the archives to find how Andreas does reports!
Regards from
Tom :slight_smile:

Somebody shouting: "Hi, Andreas! I need last year's figures of Item X but only the batch numbers used by myself"

Me shouting back: "Don't you remember the form 'Item Filter' and its print button?"

The print button on form 'Item Filter' points to spreadsheet template file:///Z:/Shared/ItemDB/Item_Filter.ots
That template keeps 2 unsaved import ranges. One import range shows the filter criteria that have been stored by the calling form. The other range pulls the resulting data from a prepared parameter query bound to the filter criteria and the involved data tables.
A new spreadsheet from template loads with a message: "This document contains unsaved links to data sources. Do you want to update the links?". I told my people to always hit "Yes"(Enter) in this case. The user can print the resulting report and he may or may not save a copy of the new file. All this works like a charm without a single line of macro code.
[Example #1] Filter/Search with Forms (leveraging SubForms):

http://user.services.openoffice.org/en/forum/viewtopic.php?f=100&t=42845

In some cases where my nifty form does not apply, I need to create a query from scratch or copy/paste/adapt an existing one, drag the query icon into a spreadsheet template (or create a pivot table), apply some cell styles, send the result to some printer or PDF. Sometimes Calc's pivot tables provide the best reporting engine for the task.
Drag&Drop works with a blank Writer document as well. Unfortunately, we can not link the resulting text tables to the imported record set.

My database is for internal use only. Nobody cares when the print margins, orientations or number formats are not perfect.

I used the Sun Report Builder exactly once when it was particularly important to impress a bankster. I have a rough idea what it could do for me. I think Calc can do the same more easily except for pictures.

#11

Am 25.05.2012 10:04, Tom Davies wrote:

Sadly most of the people that know a lot about the ins-and-outs of Base are unwilling to help write the Base guide so it's down to 1 person who is desperately busy with a lot of other projects.

How would you write a guide for hammer, chisel, plow, saw and file? There is nothing special with Base. It is a simple tool set to do something with databases. "Simple" means the opposite of "easy".

#12

Tom Davies wrote:

Hi :slight_smile:
I have just done a bit of digging around.

Looking back through Andreas' answers
http://nabble.documentfoundation.org/template/NamlServlet.jtp?macro=user_nodes&user=254826&i=80

I found this but i'm not sure it's much help for you http://nabble.documentfoundation.org/database-td3926217i20.html#a3928210

This one seems to expand on the useful line at the end there
http://nabble.documentfoundation.org/Base-3-5-2-2-hangs-td3910280.html#a3911654

This one is a bit of an overview with nothing specific but might be useful to skim through for ideas
http://nabble.documentfoundation.org/Address-Books-td3829696.html#a3832514

This one looks brilliant to me
http://nabble.documentfoundation.org/Base-and-new-Report-Builder-in-3-5-td3817159.html#a3819387

There is some documentation at https://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Base_Guide
and an FAQ (translated from the French team's) at https://wiki.documentfoundation.org/Faq

Sadly most of the people that know a lot about the ins-and-outs of Base are unwilling to help write the Base guide so it's down to 1 person who is desperately busy with a lot of other projects.

Regards from
Tom :slight_smile:

Tom,
Your "brilliant" link looks interesting. I knew Calc and Base have some similarities internally, but I wasn't aware that my data could be imported into Calc from my MySQL server. All of my reports are tabular in format anyway, so Calc could be an option, if it could get my data. One limit to using Calc would be Calc's row limit. I think I read that LO just raised the maximum rows, but if there are more database records than available Calc rows, then...
Can Calc be linked to a Base query to get its data?
If so, then all my sorting can be done in the query. I do not do any grouping in my reports at this time.

As an update to my problem with Report Builder, my 1500+ record database takes 94 pages of a Report Builder / Writer document. I put on my patience hat and timed how long it takes to format the report, ready for printing. As a baseline, it takes about 49 minutes to create the original document. I cleared my preferences and re-entered them, using mostly defaults and timed it again: 49 minutes. So it is not preference corruption from previous versions. I then updated my java to 1.6u30 and timed it again: 49 minutes. So, it doesn't look like Java is the problem. I then went back to my original use of SQL from when I created the report with the wizard, rather than using a query for the data and timed it: 49 minutes! No matter what I do, it takes about the same excruciatingly long time to build my report. What is really frustrating is that there is no status popup to inform me of what it is doing. It just goes out to lunch. Actually, it seems to initially create 128 pages with header and footer, but no data, at the end of the first "sleep". When the page status at the bottom of the writer window drops to 94 pages, I know it is done. Every so many pages, it does come back alive for a second or two and then goes out to lunch again for several minutes. Each time it returns, a few more pages are populated with data.
Girvin

#13

Yes, you can link to a Base query with Calc. With Cal open, use the
F4 key to open the Data Source window. (You will have to register the
database before it will appear in this window.) The left side is the
name of the registered databases. Click the arrow in front of the
database to see that you can get data from from a table or query. When
you select one of these, its data appears in table form on the right
side.

--Dan

#14

Tom Davies wrote:
> Hi :slight_smile:
> I have just done a bit of digging around.
>
> Looking back through Andreas' answers
> http://nabble.documentfoundation.org/template/NamlServlet.jtp?macro=user_nodes&user=254826&i=80
>
> I found this but i'm not sure it's much help for you
> http://nabble.documentfoundation.org/database-td3926217i20.html#a3928210
>
> This one seems to expand on the useful line at the end there
> http://nabble.documentfoundation.org/Base-3-5-2-2-hangs-td3910280.html#a3911654
>
> This one is a bit of an overview with nothing specific but might be useful to skim through for ideas
> http://nabble.documentfoundation.org/Address-Books-td3829696.html#a3832514
>
> This one looks brilliant to me
> http://nabble.documentfoundation.org/Base-and-new-Report-Builder-in-3-5-td3817159.html#a3819387
>
> There is some documentation at
> https://wiki.documentfoundation.org/Documentation/Publications#LibreOffice_Base_Guide
> and an FAQ (translated from the French team's) at
> https://wiki.documentfoundation.org/Faq
>
> Sadly most of the people that know a lot about the ins-and-outs of Base are unwilling to help write the Base guide so it's down to 1 person who is desperately busy with a lot of other projects.
>

Hi Tom

Really - you seem to know a lot, are you writing a manual?

@Girvin - there is no place that I've seen the report builder be that
slow and not have an installation problem - not saying it is impossible
but sounds really odd. 1500 records in a tabular report, 49 seconds is a
long time for that actually. There is one exception to that, graphics -
is your report including graphics stored in data fields?

On the other hand Calc, as has been pointed out, might be your preferred
path.

Best,

//drew

#15

Dan Lewis wrote:

     Yes, you can link to a Base query with Calc. With Cal open, use the
F4 key to open the Data Source window. (You will have to register the
database before it will appear in this window.) The left side is the
name of the registered databases. Click the arrow in front of the
database to see that you can get data from from a table or query. When
you select one of these, its data appears in table form on the right
side.

--Dan

Dan,
Wow! That worked fine. Thanks.
However, I had to copy and paste the database data from the Data Source window into the Calc spreadsheet. Is there a way to dynamically get the current data to the spreadsheet so I don't have to cut and paste every time I have new data?
If so, it would be a lot less error-prone (and easier). However, I can see that not being able to do so, is not a show-stopper for using Calc to do my reports.
Note that the data copy, although it took a little time, was *much* faster than Report Builder! :slight_smile:
I am going to have to play with this some more...
Thanks very much.
Girvin

#16

drew jensen wrote:
8><...

Hi Tom

Really - you seem to know a lot, are you writing a manual?

@Girvin - there is no place that I've seen the report builder be that
slow and not have an installation problem - not saying it is impossible
but sounds really odd. 1500 records in a tabular report, 49 seconds is a
long time for that actually. There is one exception to that, graphics -
is your report including graphics stored in data fields?

On the other hand Calc, as has been pointed out, might be your preferred
path.

Best,

//drew

8><...

Hello Drew,
I understand what you are saying, but I installed the LO 3.5.2 binary (RPM) packages from the LO website. I repackaged them for Slackware, but that does not change the 1s and 0s, just unpacks the RPMs and re-packages the files into a Slackware package for installation. Therefore, if there is an installation problem, it seems to be in the LO packages. Note that I am using the Report Builder bundled into LO, not the version from the extensions website. I do believe the newest is 1.2.1 rev 2, and the one in LO is still 1.0-something. I have not tried to replace the bundled version with the 1.2 version.

I could live with 49 _"seconds"_. However, it is taking 49 _minutes_ for my report. My database is nothing special, just text and integer values. Nine elements plus the key per record. No graphics. So, yes, this problem does sound unusual. However, I have not been able to pin down the problem yet. In the meantime, I am looking into using Calc to print my data. It looks good and will do until I can get RB working.

BTW: The report is a simple text page header, Detail, and page footer, no graphics there either. I do have the date and time fields in the header, but that shouldn't bring it down. Actually, I do have some separator lines inserted at the bottom of the header and the bottom of each Detail line. Page number at the bottom of the footer. That's it.
Thanks for the help.
Girvin

#17

Hi Girvin

Sounds like it aught to run lickity-split...hmm

Anyway - looks like you are off to use Cacl.

You might want to do this.

When you open the data window under Calc, instead of copy/paste from the
data grid - grad, drag and drop the actual query name from the left side
of the data window. This will create a named range, you can set options
on the range to _not_ save the data in the spreadsheet, jut the
connection information...then when you open the spreadsheet again it
will re-run the query and update the data anew..

Best wishes,

//drew

#18

Girvin ,

You must know that ORB , when openning a report, first execute the SQL commands "saved" with the report, this can surrly takes some time, then you can give a new 'actual)set off SQL commands. Therefore we never safe the reports with real SQL commands, we just make a dummy command and so the reports are comming up match quicker.
Hope its helps

Fernand

#19

Dan,
Wow! That worked fine. Thanks.
However, I had to copy and paste the database data from the Data Source
window into the Calc spreadsheet. Is there a way to dynamically get the
current data to the spreadsheet so I don't have to cut and paste every
time I have new data?
If so, it would be a lot less error-prone (and easier). However, I can
see that not being able to do so, is not a show-stopper for using Calc
to do my reports.
Note that the data copy, although it took a little time, was *much*
faster than Report Builder! :slight_smile:
I am going to have to play with this some more...
Thanks very much.
Girvin

-- When you drag the query icon from the left pane of the data source window onto a Calc cell you will get a semi-automatic link. Call menu:Data>Refresh to refresh the link.
-- When you drag selected row selectors you get a copy of selected rows. The top-left corner of a row set selects all rows. Same with queries, views and tables opened from some database window.
-- Dragging a query icon from the database window creates a refreshable link as well but the link breaks when the connection closes. Bug or not bug? Who cares?
-- From a form you can copy from the additional grid view. Hit the last button on the navigation tool bar.

The linked data range resizes dynamically with every refresh.
Any formula cells directly adjacent to the import range will expand and shrink with the resized data range. So the clearly defined row sets of a database enable dynamically resizing formula ranges. A feature that does not exist with a pure spreadsheet. Tons of silly macro code have been written for this.

Its properties are accessible via menu:Data>Define...
Below [More Options] we have 4 widely unknown options:
1) Contains column labels (always true for database imports)
2) Keep formatting: spreadsheet formatting overrides any Base formatting. Should be true by default.
3) Insert/remove cells: Do not overwrite/clear cells when the database range expands/shrinks. Should be true by default, particularly with resizing formula ranges.
4) Don't save imported data. Useful with very large row sets and when you want to refresh on open. Prompts for refresh when opening the file.

#20

Am 26.05.2012 12:40, Andreas Säger wrote:

The linked data range resizes dynamically with every refresh.
Any formula cells directly adjacent to the import range will expand and
shrink with the resized data range. So the clearly defined row sets of a
database enable dynamically resizing formula ranges. A feature that does
not exist with a pure spreadsheet. Tons of silly macro code have been
written for this.

I forgot:
All references in formulas, conditional formatting, validation and charts dynamically adjust to the resized data range.
A reference A2:A99 becomes A2:A100 when the import range grows by one row after refresh.