Very slow Spatialite DB creation on qgis 2.0/master

[qgib]

Author Name: Giovanni Manghi (@gioman)
Original Redmine Issue: 8340
Affected QGIS version: master
Redmine category:data_provider/spatialite

---

On qgis master (nightly+ubuntugis) on Ubuntu, the creation of a new SL db is very slow, it takes minutes, so long that at the beginning I was thinking it was a freeze.

Does not occur on Windows and also on other Linux distros, like Debian (qgis self compiled).

---

Related issue(s): #18042 (duplicates)
Redmine related issue(s): 9448

---

[qgib]

Author Name: Daniel Vaz (Daniel Vaz)

---

It seems ok to me. But I am using Ubuntu 13.04 and qgis compiled from source.

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

Daniel Vaz wrote:

It seems ok to me. But I am using Ubuntu 13.04 and qgis compiled from source.

it seems specific of some library version, I'm on Ubuntu 12.04

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

Now I see this also on a (clean) Windows/osgeo4w installation on both master and 1.8, while in the standalone 1.8 the creation of the SL is fast as expected.

---

• priority_id was changed from Normal to Severe/Regression
• operating_system was changed from Linux/Ubuntu to

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

• subject was changed from Very slow Spatialite DB creation on qgis-master/ubuntu to Very slow Spatialite DB creation on qgis master

[qgib]

Author Name: Andreas Neumann (@andreasneumann)

---

For me the registration of a new SL db takes not minutes, but 2-5 seconds. But still it is suboptimal to have seconds of unresponsiveness. Tested on OSGeo4W nightly and on Ubuntu 13.04 (self-compiled). But these are both rather fast machines.

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

Andreas Neumann wrote:

For me the registration of a new SL db takes not minutes, but 2-5 seconds. But still it is suboptimal to have seconds of unresponsiveness. Tested on OSGeo4W nightly and on Ubuntu 13.04 (self-compiled). But these are both rather fast machines.

Hi Andreas, I have initially seen this on my Ubuntu machine and concluded it was a local issue, but then I started seeing this on my (clean) Windows VM that I use for testing with osgeo4w... I will check again.

[qgib]

Author Name: Daniel Vaz (Daniel Vaz)

---

Maybe it's a local issue like you said. I can't reproduce it here.

Please if you can provide some steps to follow, I will try to reproduce it.

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

Daniel Vaz wrote:

Maybe it's a local issue like you said. I can't reproduce it here.

Please if you can provide some steps to follow, I will try to reproduce it.

no fancy steps to follow. I don't understand what I can have that is not ok, especially on Windows...

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

I tested on another Windows machine and is ok. Still slow on my Linux machine, but not slow as before. So I guess that this is likely an issue with my pc.

---

• resolution was changed from to invalid
• status_id was changed from Open to Closed

[qgib]

Author Name: Paolo Cavallini (@pcav)

---

Confirmed here, on several machines, both Windows and Ubuntu. Unclear why it is slower on some machines than others. In worst cases it takes more than 10 minutes.

---

• status_id was changed from Closed to Reopened
• priority_id was changed from Severe/Regression to High
• resolution was changed from invalid to

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

Paolo Cavallini wrote:

Confirmed here, on several machines, both Windows and Ubuntu. Unclear why it is slower on some machines than others. In worst cases it takes more than 10 minutes.

and the resulting db is useless

see #17370

this ticket should be a blocker.

---

• fixed_version_id was configured as Future Release - High Priority

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

• subject was changed from Very slow Spatialite DB creation on qgis master to Very slow Spatialite DB creation on qgis 2.0/master

[qgib]

Author Name: Jukka Rahkonen (Jukka Rahkonen)

---

Hi,

Metadata table "spatialite_history" gathers log data from all statements. For me with Windows Vista 32-bit and QGIS 2.0.1 the log looks like this:

spatial_ref_sys table successfully created 2013-10-13T06:53:15.840Z
... snip ...
geom_cols_ref_sys view 'geom_cols_ref_sys' successfully created 2013-10-13T06:53:23.913Z
spatial_ref_sys table successfully populated 2013-10-13T07:11:34.246Z

Thus it took only 8 seconds to do everything that is needed except populating the spatial_ref_sys table and then populating the table took more than 18 minutes.

[qgib]

Author Name: Jukka Rahkonen (Jukka Rahkonen)

---

I think I found it. I could repeat the slow "InitSpatialMetadata" with Spatialite-gui and learned that the right way to do it with Spatialite 4.1.1 is as

select initspatialmetadata(1);

This takes 3 seconds with my computer. Similar thread from Spatialite users forum
https://groups.google.com/forum/#!msg/spatialite-users/La8BUrVKX_g/lGJKxnQzp1sJ

Jukka Rahkonen

[qgib]

Author Name: aperi2007 - (aperi2007 -)

---

I guess the problem is due to a commit/transactional problem.

Infact I guess the actual code is simply call many insert without declare a trancaction before of call it.

If is not declared a transaction the sqlite will add a transaction to every single insert.
So it is obviusly slow.

As example:

this is slow:

begin transaction
insert
end transaction
begin transaction
insert
end transaction
begin transaction
insert
end transaction
...
begin transaction
insert
end transaction

---

Instead this is more fast:

begin transaction
insert
insert
insert
insert
end transaction

I guess it should be tried.

[qgib]

Author Name: aperi2007 - (aperi2007 -)

---

I don't know the code,
but as reported from the last documentation from spatialite 4.1.1:

http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.1.0.html#p16

    if the optional argument transaction is set to TRUE the whole operation will be handled as a single Transaction (faster): 
	the default setting is transaction=FALSE (slower, but safer).
    if the optional argument mode is not specified then any possible ESPG SRID definition will be inserted into the spatial_ref_sys table.
    if the mode arg 'WGS84' (alias 'WGS84_ONLY') is specified, then only WGS84-related EPSG SRIDs will be inserted
    if the mode arg 'NONE' (alias 'EMPTY') is specified, no EPSG SRID will be inserted at all

So the default method is more slower.
To have all in a single and fast transaction, is necessary to set "transaction=1":

InitSpatialMetaData( 1 )

[qgib]

Author Name: Even Rouault (@rouault)

---

For reference, this is also confirmed in OGR. See http://trac.osgeo.org/gdal/ticket/5270 for the fix that has been applied.

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

Fixed in changeset "e04b426f00f86a154ff74ed6bda5727086596b0f".

---

• status_id was changed from Reopened to Closed