Opened 2 years ago

Closed 2 years ago

Last modified 2 years ago

#23152 closed Cleanup/optimization (fixed)

Geodjango spatialite test database initialization very slow on some systems

Reported by: tomlottermann@… Owned by: Doug Goldstein
Component: GIS Version: master
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

While testing some of my gis models, I noticed that the test database setup ran really slowly (about 16 minutes on my harddisk).

I dug a little bit deeper into the code and noticed that the execution of load_spatialite_sql() in contrib/gis/db/spatialite/creation.py went really slowly. To be more precise the line:

cur.execute("SELECT InitSpatialMetaData()")

I found this issue here:
http://hub.qgis.org/issues/8340

This basically says that the InitSpatialMetaData() command is really slow on some systems, because it executes in many transactions.
One could speed it up by using one of the following solutions:

  1. pass 1 to the function. This makes (at least recent versions of spatialite) it execute in a single transaction. I am not really sure for which versions of spatialite this works.
    cur.execute("SELECT InitSpatialMetaData(1)")
    
  1. manually control the transaction:
    cur.execute("BEGIN ;")
    cur.execute("SELECT InitSpatialMetaData()")
    cur.execute("COMMIT ;")
    

For people who have the test database outside the memory this might be a huge problem.

Change History (6)

comment:1 Changed 2 years ago by Tim Graham

Easy pickings: unset
Triage Stage: UnreviewedAccepted
Type: BugCleanup/optimization
Version: 1.7-rc-2master

From that link, it looks like this is only supported on Spatialite 4.1+ so we may need some conditional logic to avoid breaking older versions.

comment:2 Changed 2 years ago by Doug Goldstein

I've implemented this and submitted it as part of the following pull request: https://github.com/django/django/pull/3423 Would you accept the same pull request against the 1.7 branch for a future stable bump?

comment:3 Changed 2 years ago by Doug Goldstein

Owner: changed from nobody to Doug Goldstein
Status: newassigned

comment:4 Changed 2 years ago by Tim Graham <timograham@…>

Resolution: fixed
Status: assignedclosed

In 57e40551e41eaf31ff96672beea9b1f732f2e331:

Fixed #23152 -- Added support for transactional Spatialite metadata initialization.

Thanks Doug Goldstein for the initial patch.

comment:5 Changed 2 years ago by Tim Graham <timograham@…>

In 9c9f35ed27d1762f8da5ac0683d350952debf709:

[1.7.x] Fixed #23152 -- Added support for transactional Spatialite metadata initialization.

Thanks Doug Goldstein for the initial patch.

Backport of 57e40551e4 from master

comment:6 Changed 2 years ago by Tim Graham <timograham@…>

In e548d08f243335b4f11190417830a5f3027efd2a:

Renamed SpatiaLite feature flag introduced in refs #23152.

Thanks Doug Goldstein for the suggestion.

Note: See TracTickets for help on using tickets.
Back to Top