[Qgis-user] Optimize QGIS Command_Line

Tue Feb 6 22:35:52 PST 2024

Hi Dennis,

concerning your first command, is your GeoPackage stored locally or on a network drive? Writing SQLite/GepPackage databases onto a network drive can have a severe performance impact. Another idea, did you try setting -lco SPATIAL_INDEX=no and creating the index after the import? This might speed up your import as well.

This page https://gdal.org/drivers/vector/sqlite.html  has some more insights on performance, but maybe you know it already ...

Concerning your second command, without knowing how many lines / polygons you want to dissolve here (seems like a lot looking at the file size) it looks pretty normal to me. Keep in mind that dissolving is a rather cost intensive process, when not dissolving by attribute. As every geometry has to be checked against all the others if they can be dissolved spatially - spatial indexes aside.
I do not know the internals of the algorithm itself, but I would guess it is not well suited for a threaded / multi process approach and it just runs on one core of your CPU doing all the work - like most GIS tools do.

A speedier approach could be to spatially divide your data into smaller parts, do the dissolve on each part to reduce the number of features, and then finally dissolve the results into the final one ...

________________________________
Von: QGIS-User <qgis-user-bounces at lists.osgeo.org> im Auftrag von Dennis Burgess via QGIS-User <qgis-user at lists.osgeo.org>
Gesendet: Dienstag, 6. Februar 2024 15:23
An: QGIS-User at lists.osgeo.org <QGIS-User at lists.osgeo.org>
Betreff: Re: [Qgis-user] Optimize QGIS Command_Line

ogr2ogr -f gpkg geom_multi_38.gpkg "MSSQL:server=xxxxxx;database=xxxxxxxxx;uid=xxxxxxxxx;pwd=xxxxxxxx;MultipleActiveResultSets=true;" -sql "EXEC xxxxxx 42039, 38" -overwrite  -gt 1000000 -lco SPATIAL_INDEX=yes  OGR_SQLITE_CACHE=10240MB OGR_SQLITE_SYNCHRONOUS=OFF

This one takes around 30 min to export to gpkg file, if I run the same exec command on sql it takes less than 40 seconds to pull down ..  these are geometry lines.   Around 940 lines.

The second one

"c:\program files\qgis 3.26.2\apps\qgis\bin\qgis_process" run native:dissolve --INPUT=geom_multi_55096.gpkg --OUTPUT=geom_multi_diss_38.gpkg

This would e one of them.  Last job took 49 minutes, orginal input is around 1.2 gig.

Dedicated platform for processing: 72 cores, 32 gig of RAM, running on NVME disk.

SQL 2019 Enterprise: 80 cores, 512gig ram, NVME disks.

From: Johannes Kröger (WhereGroup) <johannes.kroeger at wheregroup.com>
Sent: Tuesday, February 6, 2024 2:01 AM
To: Dennis Burgess <dmburgess at linktechs.net>; QGIS-User at lists.osgeo.org
Subject: Re: [Qgis-user] Optimize QGIS Command_Line

Hey Dennis,

can you share those command lines (maybe with sensitive information stripped)? Otherwise it is hard to guess what the issue might be.

What does your CPU, RAM and I/O utilisation look like during those long running processes? Same for the MSSQL database server host system.

Cheers, Hannes

On 05.02.24 14:46, Dennis Burgess via QGIS-User wrote:

I have two  command lines, one reads from my MSSQL database and creates a GeoPackage on the local disk.  This takes WAY too long, just to create it , it uses 5% or less CPU, and is SUPER slow. Looking for methods to speed this up.  The data is around 1.2 to 1.4 gig of pop, but it should not take 50 min to create.

Also I am doing a dissolve on this data, once its on the disk, this also takes quite a while, even though when I do it from QGIS it takes like 10 min vs an hour or more on the computer with the command line.

I’m sure I am doing something wrong on both of these. Just trying to understand what it would be.

Thanks for any assistance !

Dennis

_______________________________________________

QGIS-User mailing list

QGIS-User at lists.osgeo.org<mailto:QGIS-User at lists.osgeo.org>

List info: https://lists.osgeo.org/mailman/listinfo/qgis-user

Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user

--

Johannes Kröger / GIS-Entwickler/-Berater

---------------------------------------------

Aufwind durch Wissen!

Web-Seminare und Online-Schulungen

bei der www.foss-academy.com<https://imsva91-ctp.trendmicro.com:443/wis/clicktime/v1/query?url=http%3a%2f%2fwww.foss%2dacademy.com&umid=7036B80A-10B7-5306-BAB5-505120F413E8&auth=079c058f437b7c6303d36c6513e5e8848d0c5ac4-0b980f826f3d937e36ac4d15a89c87a459c73be8>

---------------------------------------------

WhereGroup GmbH

c/o KK03 GmbH

Lange Reihe 29

20099 Hamburg

Germany

Tel: +49 (0)228 / 90 90 38 - 36

Fax: +49 (0)228 / 90 90 38 - 11

johannes.kroeger at wheregroup.com<mailto:johannes.kroeger at wheregroup.com>

www.wheregroup.com<http://www.wheregroup.com>

Geschäftsführer:

Olaf Knopp, Peter Stamm

Amtsgericht Bonn, HRB 9885

-------------------------------
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20240207/e5c162e7/attachment.htm>