We have a bug for LO 3.5: https://bugs.freedesktop.org/show_bug.cgi?id=51239 There is a database attached. Table opens in LO 3.3.4 at once, in 3.5 it lasts about 70 seconds, also in 3.6 Beta 3. Scrolling through a table is horrible slow in 3.6. Beta 3: 400 seconds to the last row - 3.5 about 200 seconds to the last row and 3.3.4 at once. This isn't the old problem with JRE under Linux-rpm. Have tested it with different versions. When using LO 3.6 the old behaviour with the wrong JRE-Version under Linux-rpm comes back - for all versions. The rows will appear after some times bit by bit.
Importance is "highest" and "blocker" if anybody will work any more with the internal HSQLDB.
*** This bug has been marked as a duplicate of bug 51239 ***
Hello Lionel, this isn't a duplicate of https://bugs.freedesktop.org/show_bug.cgi?id=51239 With LO 3.5 you could work - its slow, but not so slow as LO 3.6 Beta 3. LO 3.6 Beta3 is a horror for everybody, who will work with the internal database. Its a little difference between 200 seconds and 400 seconds scrolling with the same JRE trough a table. Is a great difference to LO 3.3.4 with about 3 seconds ... When this is a duplicate we have to change Importance at 51239 to "highest" and "blocker". 51239 was reported for LO 3.5. Opening in LO 3.5 isn't like it is in LO 3.6 Beta 3, where rows appear step by step.
(In reply to comment #3) > this isn't a duplicate of > https://bugs.freedesktop.org/show_bug.cgi?id=51239 > With LO 3.5 you could work - its slow, but not so slow as LO 3.6 Beta 3. I see; it is even slower. I had not understood that. Then, indeed it is probably yet another problem that piles up *on* *top* of bug 51239, and needs its own bug.
Although I understand that this problem here is a disaster for someone working with Database, this is not a blocker. Compared to Crashes this has much less severity, and a cost-benefit calculation does not allow to make not available these <https://wiki.documentfoundation.org/Talk:ReleasePlan/3.6> Bugfixes. But of course an unusable HSQLDB is serious. Lionel, I think during work on Bug 51239 you also will check the effects from here in 3.6, or is there an additional confirmation required?
(In reply to comment #5) > Lionel, I think during work on Bug 51239 you also will check the effects from > here in 3.6, or is there an additional confirmation required? There seems to be two problems: - One that affects LibO 3.5 (and presumably LibO 3.6), which is a factor MANY slowness: bug 51239 - Another one that affects LibO 3.6, which is a factor 2 slowness: this bug So probably the most "critical" of the two is bug 51239 rather than this one. Maybe we even won't care about this one once 51239 is corrected ;)
*** Bug 52121 has been marked as a duplicate of this bug. ***
From bug 51239: (In reply to comment #38) > Marking as fixed since it is now backported to all relevant branches. > > (In reply to comment #37) > > Not sure if this info is relevant now, but FWIW, moving to the end of a large > > table (20,000+ records, 25+ columns) on Mac OS X.6 seems to be much worse with > > the current LO version: > > 84 seconds - LO 3.6.0.4 > > 7 seconds - LO 3.5.5 > > 4 seconds - LO 3.4.6 > > Note: I'm connecting to the local DB in FILE MODE (using HSQLDB 2.8) > > This is probably bug 51976 (which is not fixed yet). Didn't want that to be missed over here. Also, any possibility bug 53281 is a duplicate of this one?
(In reply to comment #6) > There seems to be two problems: > - One that affects LibO 3.5 (and presumably LibO 3.6), which is a factor MANY > slowness: bug 51239 > - Another one that affects LibO 3.6, which is a factor 2 slowness: this bug @Lionel: The first problem is solved. The second problem exists still - but what exactly is the problem respectively what is the reason? Have you any idea?
> > Not sure if this info is relevant now, but FWIW, moving to the end of a large > > table (20,000+ records, 25+ columns) on Mac OS X.6 seems to be much worse with the current LO version: > > 84 seconds - LO 3.6.0.4 > > 7 seconds - LO 3.5.5 > > 4 seconds - LO 3.4.6 I might also add that I've just tested the same DB as I used to generate the above timings with Apache OpenOffice.org 3.4.1 and the timing is about 3 seconds!
The problem still exists under LO 3.6.2.1, OpenSuSE 12.1, 64bit. I tested with https://bugs.freedesktop.org/attachment.cgi?id=63967 . Opening the table, try to scroll with the button "last" to the last record and wait ... wait ... 12 minutes for the 30000 rows. CPU was running with 100%. CPU is Atom(TM) CPU N570 @ 1.66GHz (Netbook). I set this bug to Status "New".
A "Jump to last record problem" is [Reproducible] with Server Installation of "LibreOffice 3.6.2.1 rc English UI/ German Locale [Build-ID: da8c1e6] on German WIN7 Home Premium (64bit) Steps how to reproduce with attachment 63257 [details] of "Bug 51239 - A double-plus-more extremely slow search/browse table in embedded HSQLDB since 3.5.5/3.6.0.beta": 1. Open Sample document from LibO Start Center, menu 'File -> Open' 2. In Database Pane select 'Tables' 3. In Tables Pane open "Sales Invoices" with double click > Table opens, showing 23 records in Status bar below table 4. Click 'Last Record' button in Table Data bar (?) below table data Expected: Shows record 30849 after few moments Actual: needs 2 Minutes or so with high CPU load Was still ok with 3.3.3: 3s for step 4 Was some worse with 3.4.2: 15s for step 4, but from then |< and >| only take 1s or so With 3.5.1.2: 3 Minutes for Step 4 (I did not measure time exactly), from then |< and >| still rather slow, takes 1 minute or so With 3.5.7.1: 3 Minutes for Step 4, from then |< and >| still rather slow, takes 30s or so With 3.6.2.2: 3,25 Minutes for Step 4 (I did not measure time exactly), from then |< and >| still rather slow, takes 20s or so only tested with this version: scrolling with scroll slider after step 4 still is a mess, always stops after few lines for several seconds after I can continue @robert: This bug still needs a lot of information to deserve Status NEW due to <https://wiki.documentfoundation.org/BugTriage#Process> item 5: - What exactly do you mean with "scroll"? Using scroll slider, Scroll buttons (for that 400s would be acceptable), Mouse whell? Please describe exactly! - What is the problem here? Slow Fileopen (not reproducible at all for me) or slow scrolling? Please decide and correct the summary! <http://wiki.documentfoundation.org/BugReport#General_information> item 4! - All OS affected or definitively only Linux? -- We need a precise step by step instruction with precise time measurement to keep apart different problems all causing slowness problems What exactly is the difference to Bug 51239? I wonder whether WIN was affectet there, I only saw Linux and MAC there, and I still see heavy problems with sample document from that bug @Lionel: I am afraid that long discussion here will have no good cost-benefit ration, may be it will be more effective that you narrow town task of the report to any obvious "all OS) problem and we will see what will happen after a first fix?
@Rainer The fastes way to scroll through a table is to jump to last row. You can click the "last record"-button. Then the CPU-load is 100%. A fast CPU is much faster, but with my Netbook the last row is reached after 12 minutes. The opening of the table (step 1) is also affected. Could be, that this is a special Linux-problem. I can't say, because I have no other operating system. When I have read your comment I could not explain, https://bugs.freedesktop.org/show_bug.cgi?id=51239 should be fixed. There was a comment in this bug, which describes a big difference between the version LO 3.5.5 and 3.6.0.4: https://bugs.freedesktop.org/show_bug.cgi?id=51239#c37 I have not tested it with a 3.5.6 or other version of LO, where the bug 51239 should be fixed. When you have tested it with Win7 and different versions it seems to be a problem since 3.4, blown up in 3.5 under Win. When I reported this bug, I have taken the time for different versions: LO 3.3.4, LO 3.5.5.2 and LO 3.6 Beta 3 unter OpenSUSE 11.4. The time to get the last row with the "last record"-button, grows from LO 3.5.5.2 to LO 3.6 Beta 3 with factor 2. See also https://bugs.freedesktop.org/show_bug.cgi?id=51976#c6
Splitting away FILEOPEN-Problem. @Robert, please submit a new Bug (similar to proceeding Lionel did with Bug 52170, Bug 53281)with some time measurement for opening time 3.5 / 3.6 so that we get a clear separation from other similar bugs. This "Go to last record" problem has probably same roots like the painful slow scrolling. For WIN it seems that the big problems started with 3.5.1 or more early @Robert, can you confirm that for Linux? I can't test because my Ubuntu LibO has no Database. I still am not sure whether my WIN problem might have completely different roots. You selected 3.6.0.0.beta3 as version because you found that "go to last record" takes twice as much time as with 3.5.whatever. I only see little slowdown between 3.5.7.1 and 3.6.2.2 But I think that all does not count, I want to see that jump done in 3 seconds as I saw in 3.3.3. Where did you see the mess starting that it takes minutes instead of <10s?
I can confirm that this is not the old Java bug from last year, and that it affects Ubuntu. It has occurred in LibreOffice 3.6.0 and 3.6.1 (document foundation builds)in Ubuntu 12.04 on several computers. Reverting back to the older versions of Java does not help. I have the issues on several computers. Depending on the speed of the processor, the time to go from the first to the last record runs from about 45 seconds to 3 minutes. The real slow part of the problem is when you select to go from the first record to the last record. If you do a search and have the search find the last record, it is on the slow side but tolerable. But if you simply click go to last record, the time is terrible as reported above. Going from the last record by searching for it, and then going back to the first record, and then telling it to go to the last record does not solve the issue. Once you have let LibreOffice take its time to go from the first to the last record, then everything will work until you shut down LibreOffice and re-open it.
I have downloaded and tested some LO-Versions with Timbabase.odb (https://bugs.freedesktop.org/attachment.cgi?id=63967) and the table Sales_Invoices_ID. All tests were made with the same system, OpenSuSE 11.4 32bit rpm. Could be, that the computer isn't as new as yours: AMD Athlon(tm) 64 X2 Dual Core Processor 5000+, 2GB main memory. LO-Version Java-Version Open Table Last Row 3.3.4 1.6.0_22 00:01,70 00:03,10 3.3.4 1.6.0_29 00:11,80 03:57,00 3.4.0 1.6.0_22 00:02,20 00:03,50 3.4.4 1.6.0_22 00:01,80 00:03,60 3.4.4 1.6.0_29 00:14,80 04:35,00 3.4.5 1.6.0_22 00:01,80 00:03,30 3.4.5 1.6.0_29 00:14,60 04:25,00 3.4.6 1.6.0_22 01:02,00 03:20,00 3.4.6 1.6.0_29 01:18,00 09:52,00 3.5.0.1 1.6.0_22 00:02,30 00:08,00 3.5.0.1 1.6.0_29 00:02,60 00:07,61 3.5.0.2 1.6.0_22 00:03,20 03:30,00 3.5.0.2 1.6.0_29 00:03,80 03:31,00 3.5.0.3 1.6.0_22 00:03,30 03:30,00 3.5.0.3 1.6.0_29 00:03,50 03:27,00 3.5.7.1 1.6.0_22 00:03,10 05:42,00 3.5.7.1 1.6.0_29 00:03,60 05:46,00 3.6.2.2 1.6.0_22 00:03,70 05:42,00 3.6.2.2 1.6.0_29 00:04,10 05:46,00 3.6.3.0+ 1.6.0_22 00:03,90 05:37,00 (Build ID: 77362d0) 3.6.3.0+ 1.6.0_29 00:04,10 05:42,00 (Build ID: 77362d0) I have tested two different Java-Versions. There has beeen a difference between the 1.6.0_22 and all newer versions under Linux. Newer version were declared as "very slow". The difference has gone with LO 3.5.0.1, as you could see in the table. The time to open a table is very slow in LO 3.4.6. But this has nothing to do with this reported bug. The best values for moving with the last-row-button to the last row of the opened table I could get with Java 1.6.0_22 and LO 3.3.4 up to LO 3.4.5. There must have been something changed form 3.4.5 to 3.4.6. Jumping to the last row rises form 3,1 seconds to 3 minutes and 20 seconds. Good values for every java-version I get with LO 3.5.0.1. But the changing to 3.5.0.2 rises them form ca. 8 seconds to 3 minutes and 30 seconds. Then a look at the last changing of values: With 3.5.7.1 and all 3.6.*-versions I have tested the time for jumping to the last row rises from 3 minutes 30 seconds to ca. 5 minutes 45 seconds. I could not confirm, that any bug-report for slow internal databases has been solved with a working patch for my system.
(In reply to comment #16) > I have downloaded and tested some LO-Versions with Timbabase.odb > (https://bugs.freedesktop.org/attachment.cgi?id=63967) and the table > Sales_Invoices_ID. > All tests were made with the same system, OpenSuSE 11.4 32bit rpm. (...) Thank you _very_ much for the detailed tests! > 3.4.5 1.6.0_22 00:01,80 00:03,30 > 3.4.5 1.6.0_29 00:14,60 04:25,00 > 3.4.6 1.6.0_22 01:02,00 03:20,00 > 3.4.6 1.6.0_29 01:18,00 09:52,00 > 3.5.0.1 1.6.0_22 00:02,30 00:08,00 > 3.5.0.1 1.6.0_29 00:02,60 00:07,61 > 3.5.0.2 1.6.0_22 00:03,20 03:30,00 > 3.5.0.2 1.6.0_29 00:03,80 03:31,00 There is clearly a loss of performance between 3.4.5 and 3.4.6. My guess is that the culprit is http://cgit.freedesktop.org/libreoffice/core/commit/?id=3623701d65f92017da905f4debf5514045f502c8 made to fix bug 44813. HSDQLDB 1.8 probably just has lousy performance on my hackish query... Looks like I'll have to make that query dynamic after all. > 3.5.0.3 1.6.0_22 00:03,30 03:30,00 > 3.5.0.3 1.6.0_29 00:03,50 03:27,00 > 3.5.7.1 1.6.0_22 00:03,10 05:42,00 > 3.5.7.1 1.6.0_29 00:03,60 05:46,00 There is another loss between 3.5.0.3 and 3.5.7.1; that's probably caused by the fix to bug 47520; see bug 53281.
Java 1.6.0_29 was always slower, Java 1.6.0_22 starts slowness some where after 3.4.5. That is more or less identical with my WIN results @robert WOW! What's the unit of the time data? Minuts:Seconds? Can you please submit a new Bug for a "FILEOPEN too slow" problem, what seems not reproducible for WIN and so might have different reasons? @Lionel: I think info here now should be sufficient to start proceeding on the bug. It's a little unclear where the problem "really" started between 3.4 and 3.5
@Robert Also WOW! I´m impressed. Very good work.
Setting platform to ALL as problem is also present on Mac, even with master daily build from 30/09/2012. Alex
@Rainer, time-date are shown as minutes:seconds,deciseconds. Fileopen doesn't seem to be a problem. There are differences of 2 seconds. The opening of tables has been changed from 3.4.* to 3.5.*. When you open a table in 3.4.* or 3.3.* there was shown at the bottom "row 1 from 47 *" - the GUI only loads the first 47 rows in the cache. When you open a table in 3.5.* the first 93 records where loaded in the cache: "row 1 from 93 *" Fileopen has been a problem under Linux, LO 3.3.* and 3.4.* with JRE > 6.22. This problem has gone, first with LO 3.5.0.1, which is the fastest version with all JREs under Linux.. @all, we should have a look of the patches, which have been committed from version LO 3.5.0.1 to 3.5.0.2. With version 3.4.6 is old, the great problem with fileopen does only exist in this version and the time for "last record" with JRE 1.6.0_22 (which only works under Linux to this time) is nearly the same as in 3.5.0.2. Could be, there has been changed the same from LO 3.4.5 to 3.4.6 as from 3.5.0.1 to 3.5.0.2. I hope we could find the reason.
The difference between this bug and the one last year is that if I insatll the older_22 java from last year and use it for LibreOffice there is absolutely no improvement in the situation going from first record to last reacord time. Last year changing to that version immediately made Base work like a charm.
I thought it was a bug related with the java version but i tested LO 3.6.3.2 with 1.6.0_22 and the last one 1.7.0_09 without any difference..
Just a comment.. I tested the same number of records but using mysql, and the jump to the last record is just immediately so "i think" it's a problem related with the HSQLDB.. Tested in Windows XP sp3: LO Versión 3.6.3.2 Java: 1.6.0_22 / 1.6.0_26 / 1.7.0_09 / Hope that helps.
I'm doing some testing to the problem, I have taken an access database with a table with around 18000 rows and create a base database with it. I have also used the export through odbc feature of access to put it in a mysql database (in the local machine) and I have found these things: -problem is reproducible with the base database -problem is reproducible with the mysql database linked through jdbc connectivity -problem is NOT reproducible with the mysql database linked through odbc connectivity the last was quite surprising but it's real, going to the last row takes only 3-4 seconds and the applications shows quite better responsivity I'm going to try more testing to see if I can find more things Tested in Windows XP sp3: LO Versión 3.6.3.2 Java: 1.6.0_37 mysql odbc driver 5.2.2 (beware, with 5.1 base crashed for me) mysql jdbc driver 5.1.20
(In reply to comment #25) > -problem is reproducible with the base database > -problem is reproducible with the mysql database linked through jdbc > connectivity > -problem is NOT reproducible with the mysql database linked through odbc > connectivity > the last was quite surprising Not really. JDBC-in-LibreOffice is just slow in general. That's because (our C++/Java experts tell me) calling Java from C(++) is slow, unless the C(++) code that calls Java is itself called by Java code. IMHO, in this case, this performance problem just compounds the general JDBC slowness to reach unacceptable. That is, without this bug, ODBC takes 0.03 seconds and JDBC 3 seconds. This bug comes in, makes everything 100x slower, ODBC takes 3 seconds (nobody bats an eyelash) and JDBC takes 300s (people scream). What you call "base database", namely embedded HSQLDB 1.8, is accessed through JDBC.
(In reply to comment #17) > There is clearly a loss of performance between 3.4.5 and 3.4.6. > My guess is that the culprit is > http://cgit.freedesktop.org/libreoffice/core/commit/ > ?id=3623701d65f92017da905f4debf5514045f502c8 > made to fix bug 44813. HSDQLDB 1.8 probably just has lousy performance on my > hackish query... Looks like I'll have to make that query dynamic after all. During the hackfest, I made that query dynamic. One then wins about 20 to 30% on HSQLDB, but will potentially lose performance on other database engines that have better query optimisation engines: we exchange more calls to SQLPrepare() or some such for a query that is easier to optimise for the database. I decided not to punish, but rather reward, database engines with good query optimisation, and will thus leave this particular bug (which was *only* about the performance loss introduced by the above commit) as it is. For *other* performance issues with embedded HSQLDB and/or JDBC in general, see bug 52170 and/or bug 57872. Will attach the patch to make the query dynamic for archiving.
Created attachment 71097 [details] make refresh query dynamic
Lionel Elie Mamane committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=cb9e5e786beef004aedf6d6cc7dbd989bd6a05be fdo#51976 make "refetch one row" query easier to optimise The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
migrate - a distant dep. of this is still open and needs to be a 4.0 issue now :-)