Massive operations in LibO - hard?

1 - Simple actions. We open Calc. We open the Base, open the Base table (about 100,000 records). While it is opening, none of the LibO windows are available. I can’t work in Calc! Next, Calc works with his table and makes a calculation (about 100,000 records). LibO application windows are again unavailable. I can’t work in Base! Finally, it was over. I close the Base table, and … it cannot close in any way. I just delete the process in the task manager…

2 - If everything is working properly. I close the Base table. The processor is loaded by about 35%-40%. The table closes in about 10 seconds. Finally closed.

3 - And that’s not all! Let’s open an empty Base table. And then we will close this table. The situation described above is repeated! The effectiveness of such work is reduced to 0 …

It’s very, very inconvenient to work like that. Why is this happening? Why don’t other apps spend so much time on simple actions - at least open, close? And do they not interfere with each other?

FORMAT DB: HSQLDB embedded, saved as ODB


Thank you all for your help! You helped me understand. I’m trying Firebird!

Version: 7.0.2.2 (x64)
Build ID: 8349ace3c3162073abd90d81fd06dcfb6b36b994
CPU threads: 4; OS: Windows 10.0 Build 19041; UI render: Skia/Raster; VCL: win
Locale: ru-RU (ru_RU); UI: en-US
Calc: threaded


EDIT_ebot - Text formatted

Hello,

You have not specified the database you are using.

On Ubuntu 20 with LO v7.0.2.2 using PostgresSQL have a 81,800+ record table with 20 columns of mixed type data and there are no problems as you noted. Did take about 20 seconds to copy from Base to Calc, but no other noticeable delays.

FORMAT DB: HSQLDB embedded, saved as ODB.

I am considering not even copying. Simple operations - opening a table, closing a table in the database, lead to “sticking” of LibO applications and CPU load up to 30-40%. One gets the impression that a flight to Mars is being calculated. Again the same! I just close the table, the application does something, then it doesn’t respond!

Have you tried converting your HSQLDB embedded file to a Firebird (FB) embedded file to see if those operations run faster? Keep a copy of your ‘old’ HSQLDB Base file as a backup before you do the ‘conversion’ to FB.

As I understand, the HSQLDB embedded setup uses a ZIP compression process to store everything to a single ZIP file to disk, and if something were to go wrong with that process, it might account for the delays you experience (just a guess). You might also want to try the SHUTDOWN COMPACT command using Tools > SQL. Make sure you keep a backup copy of your database first BEFORE you do this! Once that COMPACT process has completed, check to see if it improves the situation. See HERE

I hope that in the next few days I can try to do it.

I tried to create a Firebird base but with no success. I am getting Error code: 1
firebird_sdbc error: * Unable to complete network request to host “GMN-6”. * Failed to locate host machine. * The specified name was not found in the hosts file or Domain Name Services. caused by
‘isc_create_database’
I don’t understand why do something that initially does not work. I read about connection string and hostname, but how do I write the connection string correctly? Why is there no wizard to build this string? Why is everything so difficult?

1 firebird_sdbc error: * Unable to complete network request to host "GMN-6

It surprises me that converting from a HSQLDB embedded to a Firebird embedded Base ‘back-end’ (Database Management System - DBMS) should give you an error message like this (since with HSQLDB embedded you would not need to connect to a host machine, simply because, by definition, it’s an ‘embedded’ type setup.) So therefore, you shouldn’t need to enter any connection strings when doing the transfer from HSQLDB embedded to Firebird embedded drive Base files. So I’m baffled. Maybe I’m missing something?

NOTE: you should be able to just double-click to open your HSQLDB file using the Firebird Migration assistant, but you must have Enable experimental features check-box ticked (under OPTIONS>ADVANCED menu). Did you try that?

Anyone else got some other ideas here?

Firebird Migration? This is the first time I hear about it. Thanks! I will find and download. The error message I received when creating a new Firebird database.

Ah, I got it! Enabled the option, it works! Then I’ll test it! Thanks!

Here (below) is some more information about migrating your HSQLDB embedded Base file to Firebird embedded. Please note there are some potential problems with data and query conversion.

1. Experiences migrating from HSQL to Firebird in Libreoffice 6.2.5

2. Migrate from HSQLDB

Alternatively, migrating to a server-based setup with PostgreSQL or MySQL, etc (as Ratslinger mentions) might provide a more robust solution in the long-run, but it is not a trivial process.

In my opinion, it is almost as easy to migrate to a server since Firebird embedded still has so many outstanding problems. While most can be worked around (bandaid - too much patchwork), there does not appear to be any progress through development to remedy the situations. In fact the list keeps increasing and there are more problems than actually reported.

It is not clear as to the future of Firebird embedded.

Hello,

Using HSQLDB embedded database is not recommended outside of testing and educational purposes.

Took my 80k+ records table and copied about 50K records to a Base table with HSQLDB embedded. Opening the table was much slower than my PostgreSQL DB with 80K+ records. Closing Base was very slow.

Used the HSQLDB table and copied to a Firebird Base table. The opening was faster than HSQLDB but still slower than PostgreSQL. Base closing was much faster than the HSQL embedded.

Again using the HSQLDB table, copied to a HSQL split database. The result was nearly the same as the Firebird table.

When dealing with larger tables (this test was only 50k+ records) it is much better to use a server. There are many available - PostgreSQL, MariaDB, MySQL, HSQLDB and more.

Of course your CPU speed and amount of memory will always be a factor.

@Frants,

The copy mentioned in my comment was using Base as a data source into Calc. Sorry if I presented confusion.

See my COMMENT.

My PC: H110M-DS2-CF; Intel (R) Core ™ i5-6400 CPU @ 2.70GHz; RAM 16 GB. Maybe this is not suitable for calculating the number Pi, but it still copes with the office. If everything is so complicated with HSQLDB, then you need to warn the user. At least this: do not try to do it at home. Or: only recommended for harsh experts. Why would a beginner need it? I would use Firbird, but this base is not created in any way. After all the steps of the wizard, it is proposed to save New Database.odb (although the creation of a Firebird was chosen) and the Error code: 1 firebird_sdbc error: * Unable to complete network request to host “GMN-6”. * Failed to locate host machine. * The specified name was not found in the hosts file or Domain Name Services. caused by ‘isc_create_database’. Just wonderful! Best regards!