<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=Windows-1252">
<style type="text/css" style="display:none;"> P {margin-top:0;margin-bottom:0;} </style>
</head>
<body dir="ltr">
<div style="font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);" class="elementToProof">
Sorry, but I am out of ideas, I simply do not know enough about MSSQL to provide some insight. But I see, you are using a stored procedure instead of referring a table as source directly. Are you sure that this is not the culprit? It might work fine inside
MSSQL but might pose problems when facing with the outside.</div>
<div style="font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);" class="elementToProof">
<br>
</div>
<div style="font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);" class="elementToProof">
Just to rule out the file format, did you try writing to Shapefile or some other format to test wether it is a problem with the GeoPackage format itself or maybe the ogr2ogr driver needs some tweaking?</div>
<div style="font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);" class="elementToProof">
<br>
</div>
<div style="font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);" class="elementToProof">
What does querying with ogrinfo on your stored procedure give you as an answer? Maybe there is something in there for information as well ... missing spatial index on your side?</div>
<div id="appendonsend"></div>
<div style="font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);">
<br>
</div>
<hr style="display: inline-block; width: 98%;">
<div dir="ltr" id="divRplyFwdMsg"><span style="font-family: Calibri, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);"><b>Von:</b> Dennis Burgess <dmburgess@linktechs.net><br>
<b>Gesendet:</b> Samstag, 10. Februar 2024 22:36<br>
<b>An:</b> Frank Broniewski <broniewski@a-a.lu>; QGIS-User@lists.osgeo.org <QGIS-User@lists.osgeo.org><br>
<b>Betreff:</b> Re: [Qgis-user] Optimize QGIS Command_Line</span>
<div> </div>
</div>
<div style="font-size: 12pt; color: rgb(0, 0, 0); font-family: Calibri, Helvetica, sans-serif;" dir="ltr" id="x_divtagdefaultwrapper">
<p style="margin-top: 0px; margin-bottom: 0px;">Originally its in MSSQL, we have done a makevalid() on all lines before we use OGR2OGR to export it to a GeoPackage. That file is on the local NVME drive. We did try setting the spatial index to no, this had
very little difference in time. </p>
<p style="margin-top: 0px; margin-bottom: 0px;"><br>
</p>
<div id="x_Signature">
<div style="font-size: 12pt; color: rgb(0, 0, 0); font-family: Calibri, Helvetica, sans-serif, EmojiFont, "Apple Color Emoji", "Segoe UI Emoji", NotoColorEmoji, "Segoe UI Symbol", "Android Emoji", EmojiSymbols;" dir="ltr" id="x_divtagdefaultwrapper">
<div style="margin-top: 14pt; margin-bottom: 14pt;"></div>
<p style="margin-top: 0px; margin-bottom: 0px;"><span style="font-family: Tahoma; font-size: 10pt; color: rgb(31, 73, 125);"><b>Dennis Burgess</b></span></p>
<p style="margin-top: 0px; margin-bottom: 0px;"><span style="font-family: Tahoma; font-size: 13px;"><br>
</span></p>
<p style="margin-top: 0px; margin-bottom: 0px;"><span style="font-family: Tahoma; font-size: 13px;"> </span></p>
<div style="margin-top: 14pt; margin-bottom: 14pt;"></div>
</div>
</div>
</div>
<hr style="direction: ltr; display: inline-block; width: 98%;">
<div dir="ltr" id="x_divRplyFwdMsg"><span style="font-family: Calibri, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);"><b>From:</b> Frank Broniewski <broniewski@a-a.lu><br>
<b>Sent:</b> Wednesday, February 7, 2024 12:35:52 AM<br>
<b>To:</b> QGIS-User@lists.osgeo.org; Dennis Burgess<br>
<b>Subject:</b> AW: [Qgis-user] Optimize QGIS Command_Line</span>
<div> </div>
</div>
<div style="direction: ltr; font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);">
Hi Dennis,</div>
<div style="direction: ltr; font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="direction: ltr; font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);">
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.</div>
<div id="x_appendonsend"></div>
<div style="direction: ltr; font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);">
<br>
</div>
<div style="direction: ltr;"><span style="font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);">This page
<a href="https://gdal.org/drivers/vector/sqlite.html" data-auth="NotApplicable" id="OWA8f5d80f7-f849-6cb6-9189-171a104edb3d" class="OWAAutoLink" data-loopstyle="linkonly">
https://gdal.org/drivers/vector/sqlite.html</a> has some more insights on performance, but maybe you know it already ...</span></div>
<div style="direction: ltr;"><span style="font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);"><br>
</span></div>
<div style="direction: ltr;"><span style="font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);">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. </span></div>
<div style="direction: ltr;"><span style="font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);">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. </span></div>
<div style="direction: ltr;"><span style="font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);"><br>
</span></div>
<div style="direction: ltr;"><span style="font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);">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 ...</span></div>
<div style="direction: ltr;"><span style="font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);"><br>
</span></div>
<div style="direction: ltr;"><span style="font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);"><br>
</span></div>
<div style="direction: ltr; font-family: "Century Gothic", sans-serif; font-size: 10pt; color: rgb(0, 0, 0);">
<br>
</div>
<hr style="direction: ltr; display: inline-block; width: 98%;">
<div id="x_divRplyFwdMsg" dir="ltr"><span style="font-family: Calibri, sans-serif; font-size: 11pt; color: rgb(0, 0, 0);"><b>Von:</b> QGIS-User <qgis-user-bounces@lists.osgeo.org> im Auftrag von Dennis Burgess via QGIS-User <qgis-user@lists.osgeo.org><br>
<b>Gesendet:</b> Dienstag, 6. Februar 2024 15:23<br>
<b>An:</b> QGIS-User@lists.osgeo.org <QGIS-User@lists.osgeo.org><br>
<b>Betreff:</b> Re: [Qgis-user] Optimize QGIS Command_Line</span>
<div> </div>
</div>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
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 </p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
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. </p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
The second one</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
"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 </p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
This would e one of them. Last job took 49 minutes, orginal input is around 1.2 gig. </p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
Dedicated platform for processing: 72 cores, 32 gig of RAM, running on NVME disk.</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
SQL 2019 Enterprise: 80 cores, 512gig ram, NVME disks.</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
</p>
<div style="padding: 3pt 0in 0in; border-top: 1pt solid rgb(225, 225, 225);">
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
<b>From:</b> Johannes Kröger (WhereGroup) <johannes.kroeger@wheregroup.com><br>
<b>Sent:</b> Tuesday, February 6, 2024 2:01 AM<br>
<b>To:</b> Dennis Burgess <dmburgess@linktechs.net>; QGIS-User@lists.osgeo.org<br>
<b>Subject:</b> Re: [Qgis-user] Optimize QGIS Command_Line</p>
</div>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
</p>
<p style="direction: ltr; margin-top: 0px; margin-bottom: 0px;">Hey Dennis,</p>
<p style="direction: ltr; margin-top: 0px; margin-bottom: 0px;">can you share those command lines (maybe with sensitive information stripped)? Otherwise it is hard to guess what the issue might be.</p>
<p style="direction: ltr; margin-top: 0px; margin-bottom: 0px;">What does your CPU, RAM and I/O utilisation look like during those long running processes? Same for the MSSQL database server host system.</p>
<p style="direction: ltr; margin-top: 0px; margin-bottom: 0px;">Cheers, Hannes</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
On 05.02.24 14:46, Dennis Burgess via QGIS-User wrote:</p>
<blockquote style="margin-top: 5pt; margin-bottom: 5pt;">
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
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.</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
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.</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
I’m sure I am doing something wrong on both of these. Just trying to understand what it would be. </p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
Thanks for any assistance !</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
<br>
Dennis</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
</p>
<p style="direction: ltr; margin: 0in 0in 0.0001pt; font-family: Calibri, sans-serif; font-size: 11pt;">
<br>
<br>
</p>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">_______________________________________________</span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">QGIS-User mailing list</span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;"><a href="mailto:QGIS-User@lists.osgeo.org" id="OWA183e2a3c-eb27-d845-1da2-e126e04b6081" class="x_OWAAutoLink" data-loopstyle="linkonly">QGIS-User@lists.osgeo.org</a></span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">List info: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-user" id="OWA70308aaa-881f-a492-3e3b-4e7b8e5fc696" class="x_OWAAutoLink" data-loopstyle="linkonly" data-auth="NotApplicable">https://lists.osgeo.org/mailman/listinfo/qgis-user</a></span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">Unsubscribe: <a href="https://lists.osgeo.org/mailman/listinfo/qgis-user" id="OWA05c16313-47f8-6428-8e22-b44cd35e8424" class="x_OWAAutoLink" data-loopstyle="linkonly" data-auth="NotApplicable">https://lists.osgeo.org/mailman/listinfo/qgis-user</a></span></div></pre>
</blockquote>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">-- </span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">Johannes Kröger / GIS-Entwickler/-Berater</span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;"> </span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">---------------------------------------------</span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">Aufwind durch Wissen!</span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">Web-Seminare und Online-Schulungen</span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">bei der <a href="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" id="OWA1dee217d-242a-a44c-094a-574b383955ee" class="x_OWAAutoLink" data-loopstyle="linkonly" data-auth="NotApplicable">www.foss-academy.com</a></span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">---------------------------------------------</span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;"> </span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">WhereGroup GmbH</span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">c/o KK03 GmbH</span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">Lange Reihe 29</span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">20099 Hamburg</span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">Germany</span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;"> </span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">Tel: +49 (0)228 / 90 90 38 - 36</span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">Fax: +49 (0)228 / 90 90 38 - 11</span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;"> </span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;"><a href="mailto:johannes.kroeger@wheregroup.com" id="OWA5f5d63b8-4d7f-ac7e-9969-6818a2d3ddcd" class="x_OWAAutoLink" data-loopstyle="linkonly">johannes.kroeger@wheregroup.com</a></span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;"><a href="http://www.wheregroup.com" id="OWA1763b7da-1563-32ad-87fd-c7c03ce6f8b7" class="x_OWAAutoLink" data-loopstyle="linkonly" data-auth="NotApplicable">www.wheregroup.com</a></span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">Geschäftsführer:</span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">Olaf Knopp, Peter Stamm </span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">Amtsgericht Bonn, HRB 9885</span></div></pre>
<pre style="margin: 0in 0in 0.0001pt;"><div style="direction: ltr;"><span style="font-family: "Courier New"; font-size: 10pt;">-------------------------------</span></div></pre>
</body>
</html>