[PHP] Editor slow on multi line edit

NicolasRibot

Hi,

I'm using PHP with PostgreSQL on a small database (3000 rows for the joined table).
Editing multiple lines is very slow and generates hundreds of queries (about 30 per line).
For about 200 lines, it takes more than 3 minutes.

PHP editor is configured with 8 leftJoin to dic tables and 4 MJoin to N-M relations.

On client-side, editor's main form is configured with 'changed' option.

Is there a way to boost the edit process for multiline ?

Thanks
Nicolas

allan

Hi Nicolas,

Are you updating the joined tables and also the MJoin tables (do they have a link table, or is it just a direct reference)? 30 queries certainly sounds like a lot, but each UPDATE needs to be executed itself (unlike INSERT I don't think you can group updates). It is done in a transaction which should hopefully speed things up but 3 minutes is exceptionally slow.

Regards,
Allan

NicolasRibot

Hi Allan,

Yes, Editor form allows to edit joined tables (though in my tests, I only change a value from the main table. I can see with the form.main.submit property set to 'changed' that the changed value is submited, along with MJOINed objects )

These MJoins are defined with a link table.

I agree with you that updates have to be executed one by one, to manage links. And I like the way DTE does it, with proper transactions.

Looking at executed queries for a single line edit, I see that update is done for the changed field, but also delete/inserts for all MJOIN tables. Is this necessary, or is it a bad configuration on my PHP code ?

I did not yet timed the process precisely, but it seems some time is passed in the PHP editor code when rebuilding objects from queries.
If I execute all involved queries out of php, it runs in about half a second for a total edition time of 10s or so.

(by the way, Timing the whole request/response with Firebug shows that DT loading is quite fast, even with big data returned from first ajax call (2Mb of attributes and geometries from ajax, about half a second in UI to load table and leaflet map)

Nicolas

allan

but also delete/inserts for all MJOIN tables

I think there are two things causing that:

1. The MJoin is being submitted as a changed value because of how Editor detects changes - basically to takes a copy of the value and compares it to the value when the form is submitted. The issue there with an Mjoin is that it is an array, so it will always be different (new array, even if the values inside it are different!).
2. The delete / insert is requires by the libraries as they don't implement a diff between the existing table columns and what is now desired.

It is really the first part that I think needs to be addressed to improve performance here (doing a diff isn't something I'm currently planning on introducing). I'm afraid I don't have an immediate fix for this - it is something that I need to have a bit of think about to figure out how to compare two arrays / objects.

Regards,
Allan