Midvatten spatialite db slow in network

[StefanoMenichetti]

Ciao at everyone, I've this problem, with my collegue share a midvatten project on network folder and since spatialite is a "personal" db we modify and change records one at once, but, performance, comparing to copy same project in local disk, is dramatically slow. Why ? It's a problem of spatialite ? Moving to a postgis midvatten db we may have improvements ? Thank you very much in advance, Stefano

[HenrikSpa]

Hi! When do you find the performance to be slow? Is it while editing the attribute table using QGIS, or by using the Midvatten plugin features like the import features or plot features?

We also share Midvatten-databases (sqlite) on network shares. My experience is that if only one person works with the database during the day, the performance is acceptable, especially when only reading data, but not as fast as using it on local disk. The performance is also acceptable when multiple users read from the database simultaneously but without writing.

However, I've stumbled upon a performance issue using the logger editing tool (under Midvatten>Edit data). When I was the only person accessing the database, loading the tool was very fast. However, after one other user accessed the database, loading the tool was very slow for me, even some time after the other user stopped accessing the database. I didn't manage to find the cause of this performance slowdown, but suspect it has something to do with the network share itself.

Moving to postgis could improve performance a lot, but depends on the network speed and the performance of the server. It would also improve stability of the databases and decrease the amount of "malformed" tables that can happen if more than one user edits the database.

However, if there is a specific task you find slow, perhaps it could be made faster, so please get back to me about what you find slow.

Regards, Henrik

[HenrikSpa]

As of late I've also noticed that the performance over network is painfully slow for some of our SQLite-databases. It's worse now than it's ever been. I've not found the cause for this yet, but we might have to convert the affected databases to PostgreSQL instead of SQLite if we can't find the cause.

[StefanoMenichetti]

Hi HenrikSpa we've a Postgres database now :-) but we need some help. Our version: PostgreSQL 12.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit

We obtain these errors when attemp to establish connecton with Midvatten:

2023-02-07T11:50:05 WARNING Sql failed, see log message panel. 2023-02-07T11:50:05 WARNING DB error! SQL causing this error:SELECT DISTINCT parameter FROM w_qual_lab ORDER BY parameter Msg: ERRORE: la relazione "w_qual_lab" non esiste LINE 1: SELECT DISTINCT parameter FROM w_qual_lab ORDER BY parameter ^

2023-02-07T11:50:05 WARNING Sql failed, see log message panel. 2023-02-07T11:50:05 WARNING
2023-02-07T11:50:05 WARNING DB error! SQL causing this error:SELECT DISTINCT parameter FROM w_qual_lab ORDER BY parameter Msg: ERRORE: la relazione "w_qual_lab" non esiste LINE 1: SELECT DISTINCT parameter FROM w_qual_lab ORDER BY parameter ^

2023-02-07T11:50:05 WARNING Database might not be a valid Midvatten database! 2023-02-07T11:50:05 WARNING msg: ERRORE: la relazione "about_db" non esiste LINE 1: SELECT description FROM about_db LIMIT 1

Can you help us ?

Thank you very much

Stefano

[HenrikSpa]

Hi! I will release a new Midvatten version soon, maybe even today, which fixes some bugs for postgresql-databases. However, I'm not sure that is the cause of your problems. You could try the steps below to create and connect to the database.

I'm not sure on what step you are so I start from the beginning:

1. Create a new postgresql-database with postgis extension and a schema named "public" (default name).
2. Create a postgresql user on your postgresql server. The user must be allowed to connect to your new database and sure have permissions (grants) to use the public scema, create tables and read and write to tables.
3. Create a connection to your Postgresql-database using QGIS "Layer > Add Layer > Add PostGIS Layers". Click "Test connection" to make sure that your user works.
4. Open Midvatten settings and go to tab DB. Select postgis as Database Type and choose your new connection that you created in step 1.
5. Go to menu Midvatten > Database Management > Populate a postgis database ...
6. Then load the layers using Midvatten > Load default db-layers ...

Regards, Henrik

[HenrikSpa]

I just uploaded version 1.7.0 which includes fixes for postgresql databases (among other things).

[StefanoMenichetti]

Thank you for quick answer Henrik, I'll ask to my Postgres db admin and reply. Surely, he tells me that lack the PostGIS extension

At soon,

Stefano

Stefano

[StefanoMenichetti]

Hi Henrik, we've finally a Postgres and Postgis connection ready and we've generated succesfully midvatten tables in public schema. A question that my DBA poses to me it's the following. Do we necessarily have to create a database for each project? Is it not possible to alternatively use different schemas for each project within the same database? Thank you very much in advance

Stefano

[StefanoMenichetti]

Hi Henrik, we've finally a Postgres and Postgis connection ready and we've generated succesfully midvatten tables in public schema. A question that my DBA poses to me it's the following. Do we necessarily have to create a database for each project? Is it not possible to alternatively use different schemas for each project within the same database? Thank you very much in advance

Stefano