Bug report #9619

Loading a spatialite table reads the entire db before loading (causing delays).

Added by Donovan Cameron over 5 years ago. Updated over 5 years ago.

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

Description

I noticed that I have some large spatialite db (+10GB) that load slowly when adding a layer to the map canvas, they need to read the entire sqlite file from disk (slow), before drawing the bbox in the canvas (fast).

Eg, if the sqlite db is 10GB, qgis will read 10GB from disk before loading the bbox features (can see traffic in conky).

It would be better if the layer loaded only what was required from the sqlite db for the bbox in the canvas.

Subsequent panning and zooming is normal and only loads the bbox features quickly =)
For small sqlite db, this is probably not an issue, but for larger sqlite db's, it can be a bottleneck when loading layers.

Affected Version is 2.2.0 (not available in pull-down yet).

I've seen this on both Windows 7 64bit (via OSGeo4W) and Arch Linux 64-bit.

History

#1 Updated by Donovan Cameron over 5 years ago

  • Target version set to Version 2.2

If this is normal behaviour of spatialite/sqlite, close as needed.

But It does seem like that initial reading of the entire file from disk is a place for improvement (if possible).

#2 Updated by Nathan Woodrow over 5 years ago

I suspect this is just normal sqlite behaviour (untested)

#3 Updated by Etienne Tourigny over 5 years ago

I don't use large spatialite files, but 10GB is rather large for a single file. Perhaps you should split it up in several files if possible (unless there is a single table/layer.

#4 Updated by Donovan Cameron over 5 years ago

I've got QGIS For Android running, and it doesn't have this issue. It can list layers quickly, almost instant, and load/re-open existing projects without delays.

#5 Updated by Donovan Cameron over 5 years ago

  • Status changed from Open to Closed

I just did the update to QGIS from OSGeo4W (it installed qgis-common) so that fixed the DLL issue on Windows 8.1 and on that OS it looks to be a lot quicker than the 2.0.1 machines.

But I'm still noticing QGIS for Android is way faster than Windows and Arch Linux QGIS when it comes to loading spatialite layers or opening existing projects.

#6 Updated by Donovan Cameron over 5 years ago

Etienne Tourigny wrote:

I don't use large spatialite files, but 10GB is rather large for a single file. Perhaps you should split it up in several files if possible (unless there is a single table/layer.

I thought that was the case too. My most troublesome db is almost 2GB with a road network of >1 million records that's really slow (fast on android though).

Also available in: Atom PDF