I have a layer with millions of features. I need to join its table with another large table based on a common column.
I tried to execute the join but it takes to much to run.
Reading around I understood that, in sqlite, performances can be improved altering the "PRAGMA cache_size" which should be helpful if you want to use more RAM and less disk i/o.
(I'm working on a server where I have hundreds of Gb of ram and I would like to exploit them)
I tested the following command:
db.execute sql="PRAGMA cache_size = 50000000;"
but then when I run:
db.select sql="PRAGMA cache_size;"
I get
DBMI-SQLite driver error:
Error in sqlite3_step():
database schema has changed
DBMI-SQLite driver error:
Unable to describe table:
database schema has changed
DBMI-SQLite driver error:
Unable to describe table:
database schema has changed
ERROR: SQLite database connection is still busy
dbmi: Protocol error
Any suggestion about what I can do to solve this issue?
I totally agree with you but unfortunately recently I was involved in an already-running project where data have been stored using sqlite
DO you have any suggestion on how to smoothly move all the sqlite database into postgresql?
thank you
regards
On 21/01/25 08:52, Luí s Moreira de Sousa wrote:
Dear Ivan,
I have worked with GRASS in similar circumstances. The best advice I can offer is to replace SQLite with Postgres. As you load up on the number of features and raster size, data-base exceptions and halts become increasingly frequent and increasingly harder to address.
basically you create a new mapset and set the back-end to Postgres with db.connect [0]. Then use g.copy [1] to add in the maps you need in the new mapset.
Since you have a load of RAM available, you may also experiment creating a new mapset with a SQLite database in memory. Just mind its volatile nature, you will need to copy any relevant results to another mapset with a persistent back-end.
On Tuesday, 21 January 2025 at 09:13, Ivan Marchesini via grass-user <grass-user@lists.osgeo.org> wrote:
Hi Luis
thank you for your reply
I totally agree with you but unfortunately recently I was involved in an
already-running project where data have been stored using sqlite
DO you have any suggestion on how to smoothly move all the sqlite
database into postgresql?
thank you
regards
On 21/01/25 08:52, Luí s Moreira de Sousa wrote:
> Dear Ivan,
>
> I have worked with GRASS in similar circumstances. The best advice I can offer is to replace SQLite with Postgres. As you load up on the number of features and raster size, data-base exceptions and halts become increasingly frequent and increasingly harder to address.
>
> Also, beware of parallel access to SQLite.
>
> Regards.