[gdal-dev] Tips on Speeding up OGR2OGR GeoPackage Creation

[Rahkonen Jukka]

Hi,

There are some performance hints in https://gdal.org/drivers/vector/sqlite.html#target-drivers-vector-sqlite-performance-hints but everything in SQLite is stored into one single file that must have a proper organization all the time and that inevitably sets some limits. Ogr2ogr is made to use automatically the most obvious tricks for increasing the performance, like creating a new table first and indexing it afterwards. You may find other hints from SQLite forums, for example this looks interesting https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite but some tricks like "without_rowid" cannot be used for GeoPackage because the GeoPackage standard does not allow that.

If would help to evaluate if the speed that you have now is reasonable or not if you could provide some metrics like inserted features or megabytes per second.

-Jukka Rahkonen-



Lähettäjä: gdal-dev <gdal-dev-bounces at lists.osgeo.org> Puolesta Dennis Burgess
Lähetetty: torstai 20. lokakuuta 2022 15.07
Vastaanottaja: gdal-dev at lists.osgeo.org
Aihe: [gdal-dev] Tips on Speeding up OGR2OGR GeoPackage Creation

We have a large table in MSSQL, this table we extract data from and drop it into a GeoPackage for further processing.  However, the extraction can take some time, its only using 5% of the CPU available, and pulling the data is fairly quick in debug mode, its really its generation and index creation that is slow.  Any tips on speeding this up?  We have SSDs and NVME drives, 10gig networking etc..  So.  It should be as quick as it can be, but the 5% CPU is what I think the killer is.  On this server 24 cores, so 5% would be right at one core..  Is there no multi-threading or other options here?


[LTI-Full_175px]
Dennis Burgess, Mikrotik Certified Trainer
MTCNA, MTCRE, MTCWE, MTCTCE, MTCINE, MTCSE, HE IPv6 Sage, Cambium ePMP Certified
Author of "Learn RouterOS- Second Edition"
Link Technologies, Inc -- Mikrotik & WISP Support Services
Office: 314-735-0270  Website: http://www.linktechs.net<https://eur06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.linktechs.net%2F&data=05%7C01%7Cjukka.rahkonen%40maanmittauslaitos.fi%7Cdefc186c65ee4c75330708dab2977a8d%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C1%7C638018660925600304%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=J1OWaQUH7p7F%2FpaPKRKFPoPwGwRadewZKRv%2FlRYV%2BUw%3D&reserved=0>
Need to Automate MikroTik Backups:  https://cloud.linktechs.net<https://eur06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcloud.linktechs.net%2F&data=05%7C01%7Cjukka.rahkonen%40maanmittauslaitos.fi%7Cdefc186c65ee4c75330708dab2977a8d%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C1%7C638018660925600304%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=B5Mke%2F1KVgT8wTbp2tVo2eoyvE60qhDyzqdEpZCASCM%3D&reserved=0>
Create Wireless Coverage's with www.towercoverage.com<https://eur06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.towercoverage.com%2F&data=05%7C01%7Cjukka.rahkonen%40maanmittauslaitos.fi%7Cdefc186c65ee4c75330708dab2977a8d%7Cc4f8a63255804a1c92371d5a571b71fa%7C0%7C1%7C638018660925600304%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=E9o9%2BFk9OXa6LWRCm50qpZGWLne6ecTPOJ9ZWwmc6eQ%3D&reserved=0>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20221020/789d6a05/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 4424 bytes
Desc: image001.png
URL: <http://lists.osgeo.org/pipermail/gdal-dev/attachments/20221020/789d6a05/attachment.png>