# 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

edit retag close merge delete

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.

( 2020-11-03 20:33:00 +0200 )edit

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!

( 2020-11-04 06:27:40 +0200 )edit

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...

( 2020-11-04 09:03:25 +0200 )edit

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

( 2020-11-04 16:31:53 +0200 )edit

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?

( 2020-11-05 08:43:54 +0200 )edit

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?

( 2020-11-05 10:16:04 +0200 )edit

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.

( 2020-11-05 14:40:16 +0200 )edit

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

( 2020-11-05 15:41:31 +0200 )edit

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.

( 2020-11-05 21:05:45 +0200 )edit

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.

( 2020-11-05 21:21:18 +0200 )edit

Sort by » oldest newest most voted

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.

more

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

( 2020-11-04 18:29:29 +0200 )edit

See my COMMENT.

more