https://issues.qgis.org/https://issues.qgis.org/favicon.ico2013-07-24T14:52:30ZQGIS Issue TrackingQGIS Application - Bug report #8340: Very slow Spatialite DB creation on qgis 2.0/masterhttps://issues.qgis.org/issues/8340?journal_id=435772013-07-24T14:52:30ZDaniel Vazdanielvaz@gmail.com
<ul></ul><p>It seems ok to me. But I am using Ubuntu 13.04 and qgis compiled from source.</p> QGIS Application - Bug report #8340: Very slow Spatialite DB creation on qgis 2.0/masterhttps://issues.qgis.org/issues/8340?journal_id=435882013-07-25T02:48:15ZGiovanni Manghigiovanni.manghi@gmail.com
<ul></ul><p>Daniel Vaz wrote:</p>
<blockquote>
<p>It seems ok to me. But I am using Ubuntu 13.04 and qgis compiled from source.</p>
</blockquote>
<p>it seems specific of some library version, I'm on Ubuntu 12.04</p> QGIS Application - Bug report #8340: Very slow Spatialite DB creation on qgis 2.0/masterhttps://issues.qgis.org/issues/8340?journal_id=439052013-08-08T05:35:49ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Priority</strong> changed from <i>Normal</i> to <i>Severe/Regression</i></li><li><strong>Operating System</strong> deleted (<del><i>Linux/Ubuntu</i></del>)</li></ul><p>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.</p> QGIS Application - Bug report #8340: Very slow Spatialite DB creation on qgis 2.0/masterhttps://issues.qgis.org/issues/8340?journal_id=439862013-08-11T15:21:17ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Subject</strong> changed from <i>Very slow Spatialite DB creation on qgis-master/ubuntu</i> to <i>Very slow Spatialite DB creation on qgis master</i></li></ul> QGIS Application - Bug report #8340: Very slow Spatialite DB creation on qgis 2.0/masterhttps://issues.qgis.org/issues/8340?journal_id=440562013-08-14T00:49:53ZAndreas Neumann
<ul></ul><p>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.</p> QGIS Application - Bug report #8340: Very slow Spatialite DB creation on qgis 2.0/masterhttps://issues.qgis.org/issues/8340?journal_id=440572013-08-14T00:51:35ZGiovanni Manghigiovanni.manghi@gmail.com
<ul></ul><p>Andreas Neumann wrote:</p>
<blockquote>
<p>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.</p>
</blockquote>
<p>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.</p> QGIS Application - Bug report #8340: Very slow Spatialite DB creation on qgis 2.0/masterhttps://issues.qgis.org/issues/8340?journal_id=440842013-08-15T15:53:54ZDaniel Vazdanielvaz@gmail.com
<ul></ul><p>Maybe it's a local issue like you said. I can't reproduce it here.</p>
<p>Please if you can provide some steps to follow, I will try to reproduce it.</p> QGIS Application - Bug report #8340: Very slow Spatialite DB creation on qgis 2.0/masterhttps://issues.qgis.org/issues/8340?journal_id=440862013-08-15T16:27:31ZGiovanni Manghigiovanni.manghi@gmail.com
<ul></ul><p>Daniel Vaz wrote:</p>
<blockquote>
<p>Maybe it's a local issue like you said. I can't reproduce it here.</p>
<p>Please if you can provide some steps to follow, I will try to reproduce it.</p>
</blockquote>
<p>no fancy steps to follow. I don't understand what I can have that is not ok, especially on Windows...</p> QGIS Application - Bug report #8340: Very slow Spatialite DB creation on qgis 2.0/masterhttps://issues.qgis.org/issues/8340?journal_id=441482013-08-19T00:59:10ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Resolution</strong> set to <i>invalid</i></li><li><strong>Status</strong> changed from <i>Open</i> to <i>Closed</i></li></ul><p>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.</p> QGIS Application - Bug report #8340: Very slow Spatialite DB creation on qgis 2.0/masterhttps://issues.qgis.org/issues/8340?journal_id=450532013-09-27T07:28:19ZPaolo Cavallinicavallini@faunalia.it
<ul><li><strong>Status</strong> changed from <i>Closed</i> to <i>Reopened</i></li><li><strong>Priority</strong> changed from <i>Severe/Regression</i> to <i>High</i></li><li><strong>Resolution</strong> deleted (<del><i>invalid</i></del>)</li></ul><p>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.</p> QGIS Application - Bug report #8340: Very slow Spatialite DB creation on qgis 2.0/masterhttps://issues.qgis.org/issues/8340?journal_id=450712013-09-27T23:03:25ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Target version</strong> set to <i>Future Release - High Priority</i></li></ul><p>Paolo Cavallini wrote:</p>
<blockquote>
<p>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.</p>
</blockquote>
<p>and the resulting db is useless</p>
<p>see <a class="issue tracker-1 status-5 priority-10 priority- closed" href="https://issues.qgis.org/issues/8638" title="Spatialite layers are added as tables without geometries or "Error 11 Connection to database fail... (Closed)">#8638</a></p>
<p>this ticket should be a blocker.</p> QGIS Application - Bug report #8340: Very slow Spatialite DB creation on qgis 2.0/masterhttps://issues.qgis.org/issues/8340?journal_id=455622013-10-12T10:42:25ZGiovanni Manghigiovanni.manghi@gmail.com
<ul><li><strong>Subject</strong> changed from <i>Very slow Spatialite DB creation on qgis master</i> to <i>Very slow Spatialite DB creation on qgis 2.0/master</i></li></ul> QGIS Application - Bug report #8340: Very slow Spatialite DB creation on qgis 2.0/masterhttps://issues.qgis.org/issues/8340?journal_id=455772013-10-13T08:11:36ZJukka Rahkonenjukka.rahkonen@mmmtike.fi
<ul></ul><p>Hi,</p>
<p>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:</p>
<p>spatial_ref_sys table successfully created 2013-10-13T06:53:15.840Z<br />... snip ...<br />geom_cols_ref_sys view 'geom_cols_ref_sys' successfully created 2013-10-13T06:53:23.913Z <br />spatial_ref_sys table successfully populated 2013-10-13T07:11:34.246Z</p>
<p>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.</p> QGIS Application - Bug report #8340: Very slow Spatialite DB creation on qgis 2.0/masterhttps://issues.qgis.org/issues/8340?journal_id=455812013-10-13T08:54:21ZJukka Rahkonenjukka.rahkonen@mmmtike.fi
<ul></ul><p>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</p>
<p>select initspatialmetadata(1);</p>
<p>This takes 3 seconds with my computer. Similar thread from Spatialite users forum<br /><a class="external" href="https://groups.google.com/forum/#!msg/spatialite-users/La8BUrVKX_g/lGJKxnQzp1sJ">https://groups.google.com/forum/#!msg/spatialite-users/La8BUrVKX_g/lGJKxnQzp1sJ</a></p>
<p>Jukka Rahkonen</p> QGIS Application - Bug report #8340: Very slow Spatialite DB creation on qgis 2.0/masterhttps://issues.qgis.org/issues/8340?journal_id=455822013-10-13T10:02:26Zaperi2007 -aperi2007@gmail.com
<ul></ul><p>I guess the problem is due to a commit/transactional problem.</p>
<p>Infact I guess the actual code is simply call many insert without declare a trancaction before of call it.</p>
<p>If is not declared a transaction the sqlite will add a transaction to every single insert.<br />So it is obviusly slow.</p>
<p>As example:</p>
<p>this is slow:</p>
<p>begin transaction<br /> insert<br />end transaction<br />begin transaction<br /> insert<br />end transaction<br />begin transaction<br /> insert<br />end transaction<br />...<br />begin transaction<br /> insert<br />end transaction</p>
<hr />
<p>Instead this is more fast:</p>
<p>begin transaction<br /> insert<br /> insert<br /> insert<br /> insert<br />end transaction</p>
<p>I guess it should be tried.</p> QGIS Application - Bug report #8340: Very slow Spatialite DB creation on qgis 2.0/masterhttps://issues.qgis.org/issues/8340?journal_id=455832013-10-13T10:10:26Zaperi2007 -aperi2007@gmail.com
<ul></ul><p>I don't know the code,<br />but as reported from the last documentation from spatialite 4.1.1:</p>
<p><a class="external" href="http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.1.0.html#p16">http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.1.0.html#p16</a></p>
<pre>
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
</pre>
<p>So the default method is more slower.<br />To have all in a single and fast transaction, is necessary to set "transaction=1":</p>
<p>InitSpatialMetaData( 1 )</p> QGIS Application - Bug report #8340: Very slow Spatialite DB creation on qgis 2.0/masterhttps://issues.qgis.org/issues/8340?journal_id=455842013-10-13T10:41:19ZEven Rouaulteven.rouault@spatialys.com
<ul></ul><p>For reference, this is also confirmed in OGR. See <a class="external" href="http://trac.osgeo.org/gdal/ticket/5270">http://trac.osgeo.org/gdal/ticket/5270</a> for the fix that has been applied.</p> QGIS Application - Bug report #8340: Very slow Spatialite DB creation on qgis 2.0/masterhttps://issues.qgis.org/issues/8340?journal_id=455852013-10-13T12:05:11ZJürgen Fischerjef@norbit.de
<ul><li><strong>Status</strong> changed from <i>Reopened</i> to <i>Closed</i></li></ul><p>Fixed in changeset <a class="changeset" href="https://issues.qgis.org/projects/qgis/repository/revisions/e04b426f00f86a154ff74ed6bda5727086596b0f" title="spatialite: run InitSpatialMetadata(1) on SL >= 4.1 (fixes #8340)">e04b426f00f86a154ff74ed6bda5727086596b0f</a>.</p>