Re: [Qgis-developer] very slow on creating a new empty spatialite db

2015-08-07 Thread a . furieri 

On Fri, 07 Aug 2015 16:43:11 +0200, Ing. Pierluigi De Rosa wrote:

Dear all,

I'm experiencing than when I create a new empty spatialite from qgis 
it

takes long time to do it.
 snip -
sql = 'SELECT InitSpatialMetadata()'



Hi Pierluigi,

InitSpatialMetadata() will insert about 5,000 rows into the
spatial_ref_sys table.
SQLite is a full ACID DBMS, so performing so many INSERTs
outside a TRANSACTION will certainly be a very slow operation.

solution #1:
---
sql = 'BEGIN; SELECT InitSpatialMetadata(); COMMIT;'

solution #2:

sql = 'SELECT InitSpatialMetadata(1);'

both solutions have the same identical effect:
in the first case you'll explicitly define a Transaction
in your own SQL code.
in the second case by passing to InitSpatialMetadata()
a TRUE argument you'll authorize this function to silently
handle an internal Transaction.

bye Sandro
___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer

[Qgis-developer] very slow on creating a new empty spatialite db

2015-08-07 Thread Ing. Pierluigi De Rosa 
Dear all,

I'm experiencing than when I create a new empty spatialite from qgis it
takes long time to do it.
Here belo is the code I tried:

---
# -*- coding: utf-8 -*-
from qgis.core import *
import qgis.utils
from pyspatialite import dbapi2 as db
import timeit

start = timeit.default_timer()

dbase='/home/pierluigi/Scrivania/db.sqlite'
conn = db.connect(dbase)
cur = conn.cursor()

sql = 'SELECT InitSpatialMetadata()'
cur.execute(sql)
conn.commit()

stop = timeit.default_timer()
print stop - start
---

If i run this code my pc start swapping so much and to prepare the empty
db (the total variable) it takes 722.3559 sec or better ABOUT 12
MINUTES!!!

Of course the same is if I just save a layer into spatialite format.

Why is this happens?
Is there a work around for my code to speed up che db creation?

I'm running qgis on ubuntu 14.04, qgis 2.8 LTS from launchpad unstable
repo.

Thanks
Pierluigi


-- 



Ing. Pierluigi De Rosa (PhD)
Studio Associato GFOSSERVICES

Presso CAMPUS Via F.lli Cairoli 25 - 06127 Perugia (PG) 
fax: 075 7823038
cel: 3497558268
web: www.gfosservices.it
skype: pierluigi.derosa 

___
Qgis-developer mailing list
Qgis-developer@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/qgis-developer