<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Hi Matthias,<br>
<br>
I think I can follow that, and it makes sense that QGIS does the
calculation independently of the provider for consistency. <br>
<br>
I will indeed look into the details of the view and see if it is
one particular join that is so expensive or that it is just the
total number of joins that causes it. At the same time I'll keep
my eyes open for potential projects that might be able to
contribute, but that's a bit more complicated.<br>
<br>
Thanks for helping me understand the background of the process,<br>
Janneke<br>
<br>
<br>
<br>
On 30/11/2015 14:46, Matthias Kuhn wrote:<br>
</div>
<blockquote cite="mid:565C372A.7070709@opengis.ch" type="cite">
<meta http-equiv="Context-Type" content="text/html; charset=utf-8">
Hi Janneke,<br>
<br>
The reason for individual statements are mainly that expressions
(field calculator) and SQL (table manager) are not completely
equal. Some functions are only supported by QGIS, some only by
postgres/postgis. The expressions should be supported by all data
providers (different database systems, non-database systems) and
therefore QGIS plays safe and just does the calculation always on
its own.<br>
<br>
Some background:<br>
We have this summer started to be able to generate postgres SQL
from expressions (where possible) but this has only been applied
to some isolated areas (like filtering for rendering). It would
theoretically be possible to use it for provider side updates as
well but that's an ambitious task that will need some work by an
experienced developer.<br>
<br>
I'd propose looking into what makes updating your view so
expensive (probably easier) or consider to contribute towards
provider side updates<br>
<br>
Matthias<br>
<br>
<div class="moz-cite-prefix">On 11/29/2015 10:03 PM, Janneke van
Dijk wrote:<br>
</div>
<blockquote cite="mid:565B6810.50605@gmail.com" type="cite">
<div class="moz-cite-prefix">Hi Jürgen,<br>
<br>
After your remarks I thought I would try the individual update
statements (7000 of them ) directly on postgres. Indeed, that
gives an out of memory error after 10 minutes. Not sure if
that is entirely up to the expensive view or that there is
something more going on. I also tried writing the update
statement as an update on the view with the where clause being
'where id in (list of ids)', this again goes quickly (6
seconds).<br>
<br>
To sum it up: unless there is a way in QGIS to update
selections using one update statement with a where clause (as
opposed to individual update statements per record), it is not
possible to work with larger selections on 'expensive' views
in postgres. Would that be difficult to implement, or are
there other reasons for issuing individual statements?<br>
<br>
Thanks again all for your thoughts,<br>
Janneke<br>
<br>
On 29/11/2015 22:36, Jürgen E. Fischer wrote:<br>
</div>
<blockquote cite="mid:20151129193633.GA25947@norbit.de"
type="cite">
<pre wrap="">Hi Janneke,
On Sun, 29. Nov 2015 at 20:44:54 +0300, Janneke van Dijk wrote:
</pre>
<blockquote type="cite">
<pre wrap="">Using the field calculator either from the attribute table or from main
window with the attribute table closed both takes too long to be useful. Not
the actual calculation, but the saving of the edits. To be clear: I
originally used the update expression bar to update the selection, but all
three methods take too long to save.
</pre>
</blockquote>
<pre wrap="">That's because - as Matthias already said - the expression are evaluated for
each record and the changed attribute value is temporary stored in QGIS. When
you commit the changed records are iterated and a separate UPDATE statements is
issued for each record. And that's what takes long - although an expensive
view contributes to the time each UPDATE needs.
The comment about the attribute table was just because for each change also the
attribute table(s) UI is updated. So execution might be quicker if there are
no open attribute tables. But the actual updates should perform the same from
wherever you invoke the feature calculator.
Jürgen
</pre>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
Qgis-user mailing list
<a moz-do-not-send="true" class="moz-txt-link-abbreviated" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a>
List info: <a moz-do-not-send="true" class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a>
Unsubscribe: <a moz-do-not-send="true" class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a></pre>
</blockquote>
<br>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
Qgis-user mailing list
<a moz-do-not-send="true" class="moz-txt-link-abbreviated" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a>
List info: <a moz-do-not-send="true" class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a>
Unsubscribe: <a moz-do-not-send="true" class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a></pre>
</blockquote>
<br>
<pre class="moz-signature" cols="72">--
Matthias Kuhn
OPENGIS.ch - <a moz-do-not-send="true" class="moz-txt-link-freetext" href="https://www.opengis.ch">https://www.opengis.ch</a>
Spatial • (Q)GIS • PostGIS • Open Source</pre>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
Qgis-user mailing list
<a class="moz-txt-link-abbreviated" href="mailto:Qgis-user@lists.osgeo.org">Qgis-user@lists.osgeo.org</a>
List info: <a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a>
Unsubscribe: <a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/qgis-user">http://lists.osgeo.org/mailman/listinfo/qgis-user</a></pre>
</blockquote>
<br>
</body>
</html>