<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:36.0pt;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
span.EmailStyle18
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="NO-BOK" link="#0563C1" vlink="#954F72">
<div class="WordSection1">
<p class="MsoNormal"><span lang="EN-US">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I am struggling with the performance of SQLite (I think), esp. when I use it in a python loop executed in parallel processes (using xargs) .<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I am analyzing characteristics of a relatively large number (270k) of overlapping lake catchments which were generated in GRASS and now are stored in a PostGIS DB. I split the data in (10) chunks and analyse each chunk
in it`s own GRASS 70 mapset (with SQLite backend) where I am looping over the catchments one by one (in python).<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">At first I tried to import the individual catchments using v.in.ogr. But v.in.ogr was slowing down the process significantly. It took 11 seconds to import a single, not very complex polygon (which is probably related
to: <a href="https://trac.osgeo.org/grass/ticket/2185">https://trac.osgeo.org/grass/ticket/2185</a> ?; btw. my GRASSDB is not on NFS). So I switched to using gdal_rasterize and linked the resulting raster to GRASS (r.external) (as I am planning to work with
rasters ater anyway). Rasterization and import takes all together less than a second. It made no difference for the speed of v.in.ogr if I imported the attribute table or not. However, converting the raster to vector in GRASS takes less than a second, so the
topology creation does not seem to be the issue and also an attribute table is created…<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Then I add a relatively large number of columns (400 or something) using v.db.addcolumn. That again takes 19 seconds for my single test process. If I run all 10 chunks in parallel (I have 24 cores and lots of memory available),
adding the columns takes 30 seconds for each catchment, almost twice as much). During the loop the time spend on adding the columns continues increasing up to almost 30 min (at that point I canceled the process)… There is obviously something not working as
it should be…<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Analysing (r.univar) ca. 40 raster maps takes for the smaller catchments all together less than 5 seconds.
<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">After that I removed all SQLite related code from my script and loaded results directly back to PostgreSQL/PostGIS. Then the smaller catchments are done in less than 5 seconds…<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Does anyone have an idea what cause this performance loss is due to? Is it no good practice to call a python script (v.db.addcolumn) in a python loop, or is this related to SQLite journal files or …<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">I am grateful for any hints!<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Cheers<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">Stefan<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US">P.S.: I can share the script if that helps identifying the issue…<o:p></o:p></span></p>
</div>
</body>
</html>