Bug Hunting Session
Bug 52170 - An extremely slow search/browse table in embedded HSQLDB
Summary: An extremely slow search/browse table in embedded HSQLDB
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.5.2 release
Hardware: x86 (IA32) All
: high major
Assignee: Not Assigned
URL:
Whiteboard: hfmuc2012
Keywords: regression
Depends on: 51239 53281
Blocks: 51976
  Show dependency treegraph
 
Reported: 2012-07-16 21:51 UTC by Lionel Elie Mamane
Modified: 2014-03-25 12:16 UTC (History)
16 users (show)

See Also:
Crash report or crash signature:


Attachments
Small database (hsqldb) with one table to show performance degradation (436.89 KB, application/vnd.oasis.opendocument.database)
2013-01-04 19:49 UTC, Reto Diener
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Lionel Elie Mamane 2012-07-16 21:51:12 UTC
+++ This bug was initially created as a clone of Bug #51239 +++

Text by David:

Libre Office Base is extremely slow when sifting through the database table. I have a table with 30000+ entries which can take up to 20 minutes to sift through. Generally I can use the slider to move to the vicinity in the table where I want to be, but this freezes the table for a while. At its worst, the base table crashes and closes LibreOffice entirely, at times at the cost of the entire file.
This is Linux specific as Windows does not share this problem.
Previous versions (3.x) of both Open and Libre Office also had this problem, but with Linux, I "downgraded" my Java Runtime to 1.6.22 and this would solve the problem. (Actually, I would manually install the 1.6.22 version to my /opt folder and guide Libre Office to it.) Since 3.5, this no longer works either.
I previously ran Linux Mint 9 with Libre Office 3.5.4 installed from the command line. I then upgraded to Linux Mint 13 with LibreOffice 3.5.2.2 as the resident office suite. Both have this problem. I ran Open Suse 11.3 on another machine and installed Libre Office 3.5.4... and same problem again. However, Windows XP and 7 do not share this, regardless of the JRE installed.
I have registered with rapidshare and will upload a watered down version of the bug as soon as possible. Details to follow.


I can however confirm that no JDK/JRE since 1.6.22 has solved this problem (And
I have downloaded 1.7 recently to test) and whatever happened in the
LibreOffice 3.5x code, has significantly changed the Base / Java operation that
no JRE can help anymore.
I have also downloaded Open Office Org 3.4 and it does share the original bug
with base, but JRE 1.6.22 still keeps base running faster on OOo.
It is also not an Ubuntu specific bug, as Opensuse 12.1 and 11.3 both share it
in their versions of LibreOffice/ OpenOffice. (I am the IT guy for a small
company running OpenSuse 11.3 as their OS, and 12.1 on the file/ print server
and all these laptops share the same problem when testing Base.)
Also, the windows (WIN32) versions do not share this problem, so it seems the
bug is specific to the combination of Linux / Java / Base. I tested it on a 
1Ghz P3 running Windows XP pro with LibreOffice 3.4 and JRE 1.6.31 and it was
significantly faster than my Core 2 duo laptop running LibreOffice 3.5.2 on
Mint 13...

See attachment 63259 [details] to bug 51239 for reproduction file.


All testers: *please* report in this bug _only_ for tests with versions where bug 51239 is fixed (at this point I hope for 3.5.6 and 3.6.0.rc2) or 47520 *not* fixed (that is: 3.5.4 or older)! Please always clearly say:

1) which platform
2) JRE version
3) LibreOffice version
Comment 1 Lionel Elie Mamane 2012-07-18 08:22:51 UTC
So, in my development tree, *after* bug 51239 is fixed, it takes more than 350 seconds to move to the last row of the table.

reproduction file: https://bugs.freedesktop.org/attachment.cgi?id=63257

1) Open file in Base, either with 3.5.4 or earlier, or with any tree where bug 51239 is fixed.

2) Click on "tables" in the left part of the window

3) double-click on "Sales Invoices"

4) click on "go to last record" button in the navigation bar on bottom of window.
    Image looks like |>| (a triangle then a vertical bar)


On 3.4.6 (Debian package): 120s
Comment 2 Lionel Elie Mamane 2012-07-18 08:36:40 UTC
3.5.5.rc1 (official packages): 130s

LibreOffice 3.3.4: 300s with OpenJDK 6b24-1.11.1-6, but that's probably because of bug 35023
Comment 3 Lionel Elie Mamane 2012-07-18 08:56:17 UTC
3.5.5 final: 120s
3.5.4 rc2 (=final): 120s


Hmm... we need a test comparing 3.3.x with JRE 1.6.22 and 3.5.4 with JRE 1.6.24 or older. My "slower on dev tree" thing is probably because it is a debug build.
Comment 4 Alex Thurgood 2012-07-20 19:43:30 UTC
(In reply to comment #3)
> 3.5.5 final: 120s
> 3.5.4 rc2 (=final): 120s
> 
> 
> Hmm... we need a test comparing 3.3.x with JRE 1.6.22 and 3.5.4 with JRE 1.6.24
> or older. My "slower on dev tree" thing is probably because it is a debug
> build.

Just for comparison with different JVMs :

Linux Mint 12 64bit, LO 3.4.4 64bit

Jvm 1.6_24
Opens ODB in approx 22 seconds
Goes to end of resultset in 6 min, maxing out processor occupation (noted via top)

Jvm 1.6_26
Opens ODB in approx 30 seconds 
Goes to end of resultset in 6 min, maxing out processor occupation (noted via top)

Oracle openJDK 1.7.0
Opens ODB in approx 2 min
Took more than 16 minutes to move to end of resultset, gave up and killed process, maxed out processor occupation

Alex
Comment 5 Robert Großkopf 2012-07-21 10:52:11 UTC
(In reply to comment #3)

> Hmm... we need a test comparing 3.3.x with JRE 1.6.22 and 3.5.4 with JRE 1.6.24
> or older. 

Here with JRE 1.6.22, LO 3.3.4 and OpenSuSE 32-bit: 3s!
Comment 6 Lionel Elie Mamane 2012-12-06 18:58:19 UTC
Need new tests with libreoffice 4.0.beta2 or later.

Or with a libreoffice-4-0 or master (4.1.alpha) daily build from *after* today.

It should be faster, but is it still significantly slower than before?
Comment 7 Alex Thurgood 2012-12-07 08:04:23 UTC
(In reply to comment #6)
> Need new tests with libreoffice 4.0.beta2 or later.
> 
> Or with a libreoffice-4-0 or master (4.1.alpha) daily build from *after*
> today.
> 
> It should be faster, but is it still significantly slower than before?

Hi Lionel,

I opened the Timbabase.odb file on a 32bit Linux master build :

System:    Host: Aspire-X1430 
Kernel: 3.2.0-34-generic i686 (32 bit) Desktop: KDE 4.8.5 Distro: Linux Mint 13 Maya
Machine:   Mobo: Acer model: Aspire X1430
Bios: AMI version: P01-C1 date: 11/16/2011
CPU:       Dual core AMD E-350 APU with Radeon HD Graphics (-MCP-)
cache: 1024 KB flags: (lm nx sse sse2 sse3 sse4a ssse3 svm) 
Clock Speeds: 1: 800.00 MHz 2: 800.00 MHz
Graphics:  Card: Advanced Micro Devices [AMD] nee ATI Wrestler [Radeon HD 6310] 
           X.Org: 1.11.3 drivers: ati,fglrx (unloaded: fbdev,vesa,radeon) Resolution: 1440x900@59.9hz 



Double-clicked to load the table, 4-5 seconds to load the table and display. Clicked on the "Go to last record" arrow. 
After more than 5 minutes of waiting, I gave up and killed the whole process. So no significant gain for me.

Alex
Comment 8 Alex Thurgood 2012-12-07 08:09:25 UTC
Additional info :

JRE version: 6.0_24-b24
# Java VM: OpenJDK Client VM (20.0-b12 mixed mode, sharing linux-x86 )
# Derivative: IcedTea6 1.11.5
# Distribution: Ubuntu 12.04 LTS, package 6b24-1.11.5-0ubuntu1~12.04.1

LibreOffice Build
Version 4.1.0.0.alpha0+ (Build ID: 625ce5e92ba1850c841ce591f7bc395243af46a)
Comment 9 Alex Thurgood 2012-12-07 08:27:20 UTC
Also tested with :

JRE version: 7.0_09-b30
# Java VM: OpenJDK Client VM (23.2-b09 mixed mode, sharing linux-x86 )

I waited this time (coffee break) :

Time to completion : 13 minutes and 30 seconds.

:-/

Alex
Comment 10 Alex Thurgood 2012-12-07 09:11:21 UTC
However, with :

64bit build 
Version 4.1.0.0.alpha0+ (Build ID: abb1e3352b21d26e2ea00f504162540daa7d69f)

on 64bit Bodhi Linux 2.0 and

java version "1.7.0_09"
Java(TM) SE Runtime Environment (build 1.7.0_09-b05)
Java HotSpot(TM) 64-Bit Server VM (build 23.5-b02, mixed mode)

The time to completion drops to 9 minutes - looking better :-)

Possibly, my 32bit build was made before your change was integrated. Will have to rebuild it and try again.


Alex
Comment 11 Lionel Elie Mamane 2012-12-07 09:47:03 UTC
(In reply to comment #7)

> I opened the Timbabase.odb file on a 32bit Linux master build :

> Double-clicked to load the table, 4-5 seconds to load the table and display.
> Clicked on the "Go to last record" arrow. 
> After more than 5 minutes of waiting, I gave up and killed the whole
> process. So no significant gain for me.

> JRE version: 6.0_24-b24
> # Java VM: OpenJDK Client VM (20.0-b12 mixed mode, sharing linux-x86 )
> # Derivative: IcedTea6 1.11.5
> # Distribution: Ubuntu 12.04 LTS, package 6b24-1.11.5-0ubuntu1~12.04.1

> LibreOffice Build
> Version 4.1.0.0.alpha0+ (Build ID: 625ce5e92ba1850c841ce591f7bc395243af46a)

That's too old. The important commit (fixing bug 53281) is:


 commit bd60c90f854954aad4361e6de9829e0db2ac2ccc
 Author: Lionel Elie Mamane <lionel@mamane.lu>
 Date:   Wed Dec 5 18:09:57 2012 +0100

     fdo#53281 Don't cache whole row in KeySet
    
     This was done for the sake of ODBC,
     but the cost was imposed on all backends.
    
     The ODBC problems are now solved cleanly (and more efficiently)
     in the SDBC<->ODBC layer.
    
     Change-Id: Ib8a864da08deaaacc96a379fb72b3b7cbb34598c


Need tests with things newer than that.


(In reply to comment #10)
> 64bit build 
> Version 4.1.0.0.alpha0+ (Build ID: abb1e3352b21d26e2ea00f504162540daa7d69f)

That's also too old to be of a meaning.

> on 64bit Bodhi Linux 2.0 and

> java version "1.7.0_09"
> Java(TM) SE Runtime Environment (build 1.7.0_09-b05)
> Java HotSpot(TM) 64-Bit Server VM (build 23.5-b02, mixed mode)

> The time to completion drops to 9 minutes - looking better :-)

Maybe the difference comes from the Java version?
Comment 12 Terrence Enger 2012-12-08 19:20:27 UTC
On my ubuntu-natty (11.04) 32-bit, output from `

     dpkg-query --list '*jre*' | grep '^i'

shows

    ii  default-jre             1:1.6-40ubuntu1               Standard Java or Java compatible Runtime
    ii  default-jre-headless    1:1.6-40ubuntu1               Standard Java or Java compatible Runtime (headless)
    ii  gcj-4.4-jre-headless    4.4.5-15ubuntu3               Java runtime environment using GIJ/classpath (headless version)
    ii  gcj-4.4-jre-lib         4.4.5-15ubuntu3               Java runtime library for use with gcj (jar files)
    ii  gcj-4.5-jre             4.5.2-8ubuntu1                Java runtime environment using GIJ/classpath
    ii  gcj-4.5-jre-headless    4.5.2-8ubuntu1                Java runtime environment using GIJ/classpath (headless version)
    ii  gcj-4.5-jre-lib         4.5.2-8ubuntu1                Java runtime library for use with gcj (jar files)
    ii  gcj-jre                 4:4.5.2-1ubuntu3              Java runtime environment using GIJ/classpath
    ii  gcj-jre-headless        4:4.5.2-1ubuntu3              Java runtime environment using GIJ/classpath (headless version)
    ii  icedtea-6-jre-cacao     6b24-1.11.5-0ubuntu1~11.04.1  Alternative JVM for OpenJDK, using Cacao
    ii  icedtea-6-jre-jamvm     6b24-1.11.5-0ubuntu1~11.04.1  Alternative JVM for OpenJDK, using JamVM
    ii  openjdk-6-jre           6b24-1.11.5-0ubuntu1~11.04.1  OpenJDK Java runtime, using Hotspot JIT
    ii  openjdk-6-jre-headless  6b24-1.11.5-0ubuntu1~11.04.1  OpenJDK Java runtime, using Hotspot JIT (headless)
    ii  openjdk-6-jre-lib       6b24-1.11.5-0ubuntu1~11.04.1  OpenJDK Java runtime (architecture independent libraries)

I do not know how to tell what my LibreOffice is using.


My configuration parameters are
    --enable-dbgutil
    --enable-crashdump
    --disable-build-mozilla
    --without-system-postgresql
    --without-myspell-dicts
    --without-help
    --with-extra-buildid


I timed how long it took to move to the last row of attachment 63257 [details]
in two local builds from master:

(*) commit bd9c451, fetched 2012-12-04 around 17:20 UCT, lacks
    Lionel's patch, and it took 335 seconds to move to the last row.

(*) commit 8450a99, fetched 2012-12-08 around 02:10 UTC, includes
    Lionel's patch, and it took 465 seconds to move the last row.


Remember that debug builds can impose an orders-of-magnitude
performance penalty, as I noted in bug 56400 "FILEOPEN and close very
slow on a particular .odt"
<https://bugs.freedesktop.org/show_bug.cgi?id=56406#c2>.  That was in
Writer, which is differnt of course.


HTH,
Terry.
Comment 13 Julien Nabet 2012-12-08 19:28:29 UTC
Terrence:
I don't know how to force LO to use a specific javac during building or java during running but "update-alternatives" command may help you.

In brief, you can choose which javac and java to use, in root (or by prefixing the command with sudo)
update-alternatives --config javac
update-alternatives --config java

If you don't compile the sources, you just have to choose java part I think.

In details:
See http://doc.ubuntu-fr.org/java
Comment 14 Terrence Enger 2012-12-08 20:57:00 UTC
Thank you, Julien.  That reminds that the build told me what version
of java it is using...
    checking whether to build with Java support... yes
    checking for java... /usr/bin/java
    checking the installed JDK... checked (JDK 1.6.0_24)
    checking for target Java bytecode version... 1.6
    checking for javac... /usr/bin/javac

That is--no surprise--the default version.  `java -version` shows
    java version "1.6.0_24"
    OpenJDK Runtime Environment (IcedTea6 1.11.5) (6b24-1.11.5-0ubuntu1~11.04.1)
    OpenJDK Client VM (build 20.0-b12, mixed mode, sharing)
Comment 15 Lionel Elie Mamane 2012-12-10 11:23:42 UTC
The Java installation to use can be chosen from within LibreOffice:

 Menu Tools / Options / LibreOffice / Java

Choose one, click "OK", you'll have to restart LibreOffice for it to be taken into account.
Comment 16 Lionel Elie Mamane 2012-12-10 11:26:24 UTC
(In reply to comment #12)
> On my ubuntu-natty (11.04) 32-bit,
> I timed how long it took to move to the last row of attachment 63257 [details]
> [details]
> in two local builds from master:

> (*) commit bd9c451, fetched 2012-12-04 around 17:20 UCT, lacks
>     Lionel's patch, and it took 335 seconds to move to the last row.

> (*) commit 8450a99, fetched 2012-12-08 around 02:10 UTC, includes
>     Lionel's patch, and it took 465 seconds to move the last row.

Ow. Your test says it is actually *slower* now. Darn. That's dispiriting.
Comment 17 Alex Thurgood 2012-12-10 12:12:21 UTC
Tested with :

Version 4.1.0.0.alpha0+ (Build ID: 7f78980ea260999c88878e362a4d6948d41ddbd)

Pear Linux 32bit

JDK 1.6-024

Time to completion : ca. 2.5 - 3 minutes


Alex
Comment 18 Alex Thurgood 2012-12-10 12:33:45 UTC
Tested also on Mint 13 KDE 32bit :

JDK 1.6.024

Time to completion : 2 min 28 secs

then with JDK 1.7 :

Time to completion : 1.7.09

so at least on my 32bit builds, this is much better :-)


Alex
Comment 19 Alex Thurgood 2012-12-10 12:34:57 UTC
(In reply to comment #18)
> Tested also on Mint 13 KDE 32bit :
> 
> JDK 1.6.024
> 
> Time to completion : 2 min 28 secs
> 
> then with JDK 1.7 :
> 
> Time to completion : 1.7.09
> 
> so at least on my 32bit builds, this is much better :-)
> 
> 
> Alex

Oops, should correct that :

JDK 1.7.09
Time to completion : 2 min 30 secs

Alex
Comment 20 Robert Großkopf 2012-12-16 19:56:08 UTC
I have tested now with LO 4.0.0.0 beta from 2012/12/15
Time to browse to the end of the table with over 30000 rows is now 1:20 minutes. Much better than nearly 6:00 minutes I have tested for LO 3.6.* here: https://bugs.freedesktop.org/show_bug.cgi?id=51976#c16

But when I start my LO 3.3.4, this version needs only 3 seconds to reach the last row.
Comment 21 frofa 2012-12-21 06:07:39 UTC
And moving to the last row of the repro file (30,849 rows) using Apache OO 3.4.1 (on Mac OS X.6) takes 4 secs (if that is relevant). It takes about 1 min 58 secs under LO Version 3.6.4.3 (Build ID: 2ef5aff) on the same machine.
Comment 22 Robert Großkopf 2012-12-23 08:46:58 UTC
(In reply to comment #21)
> And moving to the last row of the repro file (30,849 rows) using Apache OO
> 3.4.1 (on Mac OS X.6) takes 4 secs (if that is relevant). It takes about 1
> min 58 secs under LO Version 3.6.4.3 (Build ID: 2ef5aff) on the same machine.

Interesting for developers: AOO 3.4.1 has the same bug as LO 3.3.4: If I start it (under Linux rpm, 32bit) with jre 1.6.0_22, it's running as fast as LO 3.3.4 (only some seconds moving to last rows). But when I use another jre (tried it with 1.6.0_29) it lasts 18 seconds only to open the table, and 4 minutes and 50 seconds to move to last row.

The bad browsing to last record seems to be introduced to LO, when there was something changed to let LO (under Linux) work with other jre as only jre 1.6.0_22.
https://bugs.freedesktop.org/show_bug.cgi?id=35023
Comment 23 frofa 2012-12-23 22:09:10 UTC
(In reply to comment #21)
> And moving to the last row of the repro file (30,849 rows) using Apache OO
> 3.4.1 (on Mac OS X.6) takes 4 secs (if that is relevant). It takes about 1
> min 58 secs under LO Version 3.6.4.3 (Build ID: 2ef5aff) on the same machine.

I forgot to say the version of JRE I have installed for the above tests is 1.6.0_37 (I think that version is provided by Apple). Also, under AOO, the repro file table ("Sales_Invoices_ID") opens almost instantly.
Comment 24 Reto Diener 2013-01-04 19:49:10 UTC
Created attachment 72523 [details]
Small database (hsqldb) with one table to show performance degradation
Comment 25 Reto Diener 2013-01-04 19:49:55 UTC
It seems I have precisely the same problem within my environment:
LibreOffice 3.6.4.3 on Windows XP (Intel Core i3, 2.5GHz, 4GB Memory)

I tested with several version of Java6 RT 6.06, 6.022, 6.038 (latest). No difference at all.

All ended with the same result:
Loading of a simple table containing about 4700 records goes quick (1s)
(about 70 records are loaded) 
Loading of the rest of the table (jumping to the last record) goes terribly slow (45s)!

See attached test-db (with embedded HSQL 1.8)

I tested additionally with a MySQL-DB via JDBC (from Oracle). Again: NO EFFECT. Same performance problem!

Inspecting the task-monitor: 
One of the 4 cores goes up to 100% (so 25% totally). There is no IO-activity for at least 30 seconds (only memory consumption is slowly increasing).

By the way: Switching to Ubuntu 12.04 LT (same hardware) with latest LibreOffice installed: Again, the same problem!!!!

Would be very cool to have a fix for that. Really!

Many thanks
Reto Diener, Switzerland
Comment 26 pasqual milvaques 2013-01-04 20:00:19 UTC
please repeat the testing with libreoffice 4 beta 2:
https://www.libreoffice.org/download/pre-releases/

I had the same problem and in that version something has changed that made it work. I hope that the problem is really solved and the fix backported to the 3.6 series for 3.6.5

best regards
Comment 27 Robert Großkopf 2013-01-04 21:03:24 UTC
(In reply to comment #26)
> please repeat the testing with libreoffice 4 beta 2:
> https://www.libreoffice.org/download/pre-releases/
> 
> I had the same problem and in that version something has changed that made
> it work. 

Can't confirm, that it is working now with Version 4.0.0.0.beta2+ from 2012-12-28_12:46:28 (latest Version).
It isn't necessary to attach another file. All tests for this bug were made with
https://bugs.freedesktop.org/attachment.cgi?id=63257
It last over 1 minute to reach the last row. Better than before, but not really the 3 seconds, that LO 3.3.4 needs for this.
I tested this with OpenSuSE 11.4, 32bit rpm.
Comment 28 Julien Nabet 2013-01-04 21:39:27 UTC
Comment on attachment 72523 [details]
Small database (hsqldb) with one table to show performance degradation

Fix mimetype
Comment 29 Reto Diener 2013-01-04 22:16:39 UTC
Thanks for the quick answers and the link!

Meantime I have tested with the prerelease LO 4 beta 2 (Windows)

Indeed, the bug seems (almost) gone!
Loading AND scrolling to last record now runs quite fast. both about the same time (1-2s) [scrolling took at least 40s before]

just by the way..... one thing came up while testing with forms:
setting the list length within the preload-trigger did not work (within LO 4):
Sub PreOpenList( event as Object )    
   event.Source.FetchSize = 300    
End Sub

What you mean with 'Fix mimetype'

Regards
Reto
Comment 30 Julien Nabet 2013-01-04 22:52:10 UTC
Reto: about what's a mimetype see http://en.wikipedia.org/wiki/Internet_media_type
I know what odb and other odf files are in fact zip but if you attach odb, it's better to put mimetype of odb.

On pc Debian x86-64 with master sources updated today, scrolling to last record is quite fast: less than 2-3 seconds for me on i5, 6GB
Comment 31 Lionel Elie Mamane 2013-01-05 08:30:18 UTC
(In reply to comment #29)

> just by the way..... one thing came up while testing with forms:
> setting the list length within the preload-trigger did not work (within LO
> 4):
> Sub PreOpenList( event as Object )    
>    event.Source.FetchSize = 300    
> End Sub

Could you please put this in a new bug, attach a reproduction example and put me in CC? Thanks.
Comment 32 Robert Großkopf 2013-01-05 09:52:14 UTC
Where do you get this new version. When I have had a look at
http://dev-builds.libreoffice.org/daily/libreoffice-4-0/Linux-x86_10-Release-Configuration/
the newest version is from 29.12.2012
- and this version is as slow as written before in my system. (Linux 32bit rpm)
Comment 33 Robert Großkopf 2013-02-08 19:56:51 UTC
https://bugs.freedesktop.org/attachment.cgi?id=72523 is too small to show the slow browsing through the table. I have just tested again with timbabase.odb
(https://bugs.freedesktop.org/attachment.cgi?id=63257) and LO 4.0.0.3, OpenSuSE 11.4 32bit rpm. Browsing to last record: 1 minute and 20 seconds.

The problem does still exist. Faster than in 3.5 (since 3.5.0 rc2) and also faster than 3.6.0 (double and more slow than 3.5.0 rc2) - but much slower than 3.4.4 or 3.3.4, which both need some seconds.
Comment 34 frofa 2013-02-09 00:40:01 UTC
Using LO Version 4.0.0.3 with the test table (DB name: 'timbabase') of 30,000+ rows, and connecting as a 'split' DB under HSQLDB v.2.2.8, the time to move to the last record is about 8 seconds! So the problem looks like its fixed (at least on the Mac platform -  I'm using Mac OS X 10.6 with Java(TM) SE Runtime Environment (build 1.6.0_39).
Comment 35 Robert Großkopf 2013-02-09 07:25:28 UTC
(In reply to comment #34)
> Using LO Version 4.0.0.3 with the test table (DB name: 'timbabase') of
> 30,000+ rows, and connecting as a 'split' DB under HSQLDB v.2.2.8, the time
> to move to the last record is about 8 seconds! So the problem looks like its
> fixed (at least on the Mac platform -  I'm using Mac OS X 10.6 with Java(TM)
> SE Runtime Environment (build 1.6.0_39).

Could you please test with the embedded HSQLDB?
Title of this bug: "An extremely slow search/browse table in embedded HSQLDB." External database-connections aren't the problem of this bug.
Comment 36 frofa 2013-02-09 21:44:47 UTC
OK. With embedded config, the time taken is 50 seconds. Looks like problem not solved on the Mac platform after all.
Comment 37 Fred Toussi 2013-02-10 15:50:57 UTC
The cause of this issue was recently analysed. See bug 53333 comment 3 and follow-ups.

In summary, navigation on large tables is slow with the embedded HSQLDB (version 1.8.0) but much faster with the external HSQLDB version 2.2.x which has query optimisations not available with the older version and performs the work in much less time. A development version of 2.3.0 has also been tested with larger embedded databases and shows the improved performance.
Comment 38 Joel Madero 2013-02-13 21:34:47 UTC
As the bug is in NEEDINFO status & the 3.5 MAB bug tracker is being closed I am going to remove this from the 3.5 MAB. 

@Lionel - if we can put this back in NEW status (all info is there), can you open the bug back up as NEW and then if you feel like it belongs on 3.6 MAB, go ahead and make it a blocker for 44446 :) Thanks!
Comment 39 Lionel Elie Mamane 2013-02-17 17:30:49 UTC
(In reply to comment #37)
> The cause of this issue was recently analysed. See bug 53333 comment 3 and
> follow-ups.

No, what is discussed there is bug 51976. This bug is supposed to be about another slowdown (but it is extremely hard to separate different slowdowns of the same operation in testing/discussion).
Comment 40 Robert Großkopf 2013-05-17 16:25:16 UTC
Here is a new test, asked for in https://bugs.freedesktop.org/show_bug.cgi?id=51239.
I have started the example-database "Timbabase" with 30000 rows in four different LO-versions an Linux 32bit rpm-system:
LO 3.4.4  |  open table: 2 seconds  |  last record: 2,5 seconds
(This is nearly identical to all LO-Versions included LO 3.5.0 rc1 - remember, that LO 3.4.5 is a release after 3.5.0 rc1; it's extremely slow)
LO 3.5.4  |  open table: 4 seconds  |  last record: 3 minutes, 40 seconds
LO 3.5.7  |  open table: 4,4 seconds  |  last record: 5 minutes, 35 seconds
LO 4.0.3.3  |  open table: 4,8 seconds  |  last record: 1 minute, 15 seconds

So LO 4.0.3.3 is faster than all versions since LO 3.5.0 rc1; but it's 30 times slower than LO 3.4.4.
Comment 41 tommy27 2013-09-07 07:18:17 UTC
(In reply to comment #40)
> Here is a new test, asked for in
> https://bugs.freedesktop.org/show_bug.cgi?id=51239.
> I have started the example-database "Timbabase" with 30000 rows in four
> different LO-versions an Linux 32bit rpm-system:
> LO 3.4.4  |  open table: 2 seconds  |  last record: 2,5 seconds
> (This is nearly identical to all LO-Versions included LO 3.5.0 rc1 -
> remember, that LO 3.4.5 is a release after 3.5.0 rc1; it's extremely slow)
> LO 3.5.4  |  open table: 4 seconds  |  last record: 3 minutes, 40 seconds
> LO 3.5.7  |  open table: 4,4 seconds  |  last record: 5 minutes, 35 seconds
> LO 4.0.3.3  |  open table: 4,8 seconds  |  last record: 1 minute, 15 seconds
> 
> So LO 4.0.3.3 is faster than all versions since LO 3.5.0 rc1; but it's 30
> times slower than LO 3.4.4.

nice test, robert. what about current 4.1.1.2 release? 
can you reproduce test on the same machine and see if the performance is improved or not?
Comment 42 Robert Großkopf 2013-09-07 07:43:42 UTC
(In reply to comment #41)
> 
> nice test, robert. what about current 4.1.1.2 release? 
> can you reproduce test on the same machine and see if the performance is
> improved or not?

Have just tested it with 4.1.2.1, but not the same machine (too many problems with tho old - bought a new and installed rpm-64 bit instead of rpm-32 bit Linux).

5 Seconds to browse to the end of the table with over 30000 rows. Couldn't be only the changing of the system (it's now a Intel Core i3-3220 with 4 GB-RAM in a so called "Mini-PC").
Don#t know, if the bug has also gone for 32-bit system. But for me it works now.
Comment 43 tommy27 2013-09-07 08:03:20 UTC
@Robert
I have an old WinXP 32bit machine (2006) where I could test and record performance. however I'm not very familiar with Base...

could please tell me step-by-step instructions how to load and browse that database?
Comment 44 Robert Großkopf 2013-09-07 08:49:44 UTC
(In reply to comment #43)
> @Robert
> I have an old WinXP 32bit machine (2006) where I could test and record
> performance. however I'm not very familiar with Base...
> 
> could please tell me step-by-step instructions how to load and browse that
> database?

Take this database: https://bugs.freedesktop.org/attachment.cgi?id=63257
Go to "Tables" on the left.
One table appears: "Sales Invoices".
Click on the table. The time between the click and the moment the whole data you could see are loaded is the time for opening the table. When I do this with some older versions of LO I could see how the rows were shown step by step to the bottom of the screen. It lasts more than 4 seconds. When I test it with my system now it's under 2 seconds.
On the bottom of the table is a button for going to the last record (fourth button from the left). Click on this button and start the stop watch. When it has been finished stop the stop watch. You could see the finishing when the cursor has gone to row 30 859. This is the time to go to the last record. Here I got more than 1 minute in the past, but only 5 seconds now. OK, has been better in 3.4.4 (2,5 seconds) but is now very much better than before in LO 4.0.3.3 (I have tested this now with my actual machine; ca. 25 seconds)
Comment 45 tommy27 2013-09-07 13:47:14 UTC
I confirm the improvement on Windows with 32 and 64 machines.

..............................................................
Win7 Pro 64bit  / Intel Core i7 @3.07 GHz  /  4 GB RAM
LO 4.1.1  |  open table: 2 seconds  |  last record: 7 seconds
..............................................................
WinXP Home SP3  32bit / Pentium 4 @3.00 GHz  /  2 GB RAM
LO 4.1.1  |  open table: 5 seconds  |  last record: 22 seconds
..............................................................

the 32bit machine is much slower but it's a PC from 2006 so you cannot pretend too much from it

since the bug report was about "extremely slow search/browse" I'm gonna mark this as RESOLVED WORKSFORME because the time needed is now acceptable

maybe we could open a follow-up report about the performance gap still existing between 4.1.1 and 3.4.4 which was even faster.

anyway much of the performance regression seems gone now.