Bug report #3002

Slow conversion to spatialite using "Save as"

Added by Jean-Roc Morreale about 9 years ago. Updated over 7 years ago.

Status:Closed
Priority:Low
Assignee:-
Category:Data Provider
Affected QGIS version:master Regression?:No
Operating System:Mandriva Easy fix?:No
Pull Request or Patch supplied:No Resolution:
Crashes QGIS or corrupts data:No Copied to github as #:13062

Description

Saving a shapefile to SQLite using the right click menu "Save as" is anormally slow, the qgis' process is constantly switching between jbd2_log_wait_commit and blkdev_issue_flush with less than 1% of the cpu. The convertion takes nearly one hour to complete.

Converting the same file with ogr2ogr is taking less than 30sec

ogr2ogr -f "SQLite" ~/rgc2009.sqlite ~/rgc2009.shp -a_srs EPSG:2154 -dsco SPATIALITE=yes

I've joined a test file, steps to reproduce :
1. add the shapefile to a new project
2. right click and Save as
3. choose SQLite and save

History

#1 Updated by Jean-Roc Morreale about 9 years ago

There is the link to download the *.shp
http://www.megaupload.com/?d=6LIGZ1XW

#2 Updated by Anne Ghisla over 8 years ago

Tested with QGIS , on a dual core running Fedora.

It takes 50 minutes to convert, but also more than one minute to display the shapefile, and the process takes on average less than 2%CPU and around 5% memory.

#3 Updated by Paolo Cavallini almost 8 years ago

  • Pull Request or Patch supplied set to No
  • Subject changed from Slow convertion to spatialite using "Save as" to Slow conversion to spatialite using "Save as"
  • Assignee deleted (nobody -)

#4 Updated by Giovanni Manghi almost 8 years ago

  • Target version changed from Version 1.7.0 to Version 1.7.4

#5 Updated by Paolo Cavallini over 7 years ago

  • Status changed from Open to Feedback
  • Affected QGIS version set to master
  • Crashes QGIS or corrupts data set to No

From the developer of SpatiaLite:

Apparently the problem is not related to SpatiaLite, but OGR: the export function generates a strange DB SQLite in FDO format.
The slowness is due to the fact that every record generate a separate INSERT, instead of the driver opening a transaction (BEGIN/COMMIT).
With GDAL 1.9.0 at least the DB format should be ok. Please test it, now that GDAL 1.9RC1 is out.

#6 Updated by J├╝rgen Fischer over 7 years ago

Paolo Cavallini wrote:

Apparently the problem is not related to SpatiaLite, but OGR: the export function generates a strange DB SQLite in FDO format.

That should not happen when SpatiaLite is selected. No idea if it changes performance. See also #4615.

#7 Updated by Jean-Roc Morreale over 7 years ago

  • Status changed from Feedback to Closed

Fixed when compiling against gdal/ogr 1.9.0

Also available in: Atom PDF