[Qgis-user] updating postgres updateable view much slower from attribute table than using db manager

[Matthias Kuhn]

Hi,

Short explanation for the difference:

* If you do it with the db manager you send a request to the database
"please update all records with this SQL".
* If you do it with the field calculator, QGIS requests all records,
evaluates the expression locally and sends dozens of update statements.

That said, the time is pretty long anyway. I am sure that there's
potential for improvement also with local evaulation. But this can be
limited by several factors:

How long does it take with the field calculator and the attribute table
closed?
(There have been some bottlenecks for updating the table in the past)
How long is the network latency (a simple "ping [server]" gives already
a good hint)

Best
Matthias

On 11/29/2015 04:42 PM, Janneke van Dijk wrote:
> Hi Nyall,
>
> Tested it with 12.1.1, that doesn't make a difference as far as I can
> tell (40 minutes now). I tested the view without the many joins for
> lookup values (it had about 13 joins and reduced it to 4), which made
> it take around 15 seconds, which would be acceptable. I haven't yet
> tested if it may be one specific join that causes the behaviour or
> that it is the number of joins.
>
> I'm still curious to understand what the difference is between the
> update statement in the db manager and the way the data are updated
> from the attribute table. Could it be that the update via db manager
> only needs to 'calculate' the joins once, where the update via the
> attribute table has to loop through all the selected features,
> 'calculating' the joins for every record that needs to be saved? If
> that is the case, would there be a way to bypass this behaviour (by
> using the original where clause for making the selection maybe)?
>
> greetings,
> Janneke
>
> On 28/11/2015 01:42, Nyall Dawson wrote:
>>
>>
>> On 27 Nov 2015 10:18 PM, "Janneke van Dijk" <janneke.qgis at gmail.com>
>> wrote:
>> >
>> > Dear list,
>> >
>> > I have an updateable spatial view in Postgres loaded into QGIS
>> 2.10. When I update 6999 records in this view in the db manager with
>> the following statement:
>> > update v_prepare_assessment_id SET districtid = 8 where locationid
>> > 13000 and locationid < 20000
>> > it takes 2.7 seconds to complete.
>> >
>> > When I open the attribute table, select the same records, then use
>> the update bar in the top to update the districtid to 8, using
>> 'update selected' , then save edits, it takes more than 10 minutes
>> (after which I closed the project).
>> >
>> > Doing the same update directly on the attribute table of the
>> underlying postgres table works nicely (takes about 3 seconds) after
>> save edits.
>> >
>> > Does anyone know why the update via the attribute table on the view
>> takes so long as to be unusable, while the db manager works nicely
>> with the view? Seeing that working directly on the underlying table
>> from the attribute table works well too it's seems to be a
>> combination of the attribute table approach with an updateable view
>> that seems to cause the issue.
>> >
>> > Thanks for any thoughts on the matter,
>>
>> Please try with 2.12.1 or the current nightly snapshots.
>>
>> I made a change since 2.12 which may have improved this already.
>>
>> Nyall
>> >
>> > Janneke
>> > _______________________________________________
>> > Qgis-user mailing list
>> > Qgis-user at lists.osgeo.org <mailto:Qgis-user at lists.osgeo.org>
>> > List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
>> > Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user
>>
>
>
>
> _______________________________________________
> Qgis-user mailing list
> Qgis-user at lists.osgeo.org
> List info: http://lists.osgeo.org/mailman/listinfo/qgis-user
> Unsubscribe: http://lists.osgeo.org/mailman/listinfo/qgis-user

-- 
Matthias Kuhn
OPENGIS.ch - https://www.opengis.ch
Spatial • (Q)GIS • PostGIS • Open Source

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20151129/8f85718a/attachment.html>