Attribute form and attribute table very slow when using Value Relation widget with bigger data set

phidrho commented 1 year ago

What is the bug or the crash?

I have a bigger dataset for value relations input in attribute form. I have set all data properly in GPKG and indexed it. I created point layer which represents person and I need to link foreign keys to persons address - city and streetname - which are features in their own tables (non-geometric in my example but they could also be geometric).

So, I linked foreign key fields with Value Relation widgets and when I try to create a new feature in point layer, opening of attribute form lasts around 20s for QGIS to create input form. Attribute table also has very slow reaction on every event.

I created a simulation of my real data with fake data in GPKG that contains tables which have lots of features, but are not big in terms of a DBMS:

cities (7540 features, attribute table)
- id (primary key)
- name
- area
streets (77960 features, attribute table)
- id (primary key)
- name
- cities_id (foreign key, indexed)
persons (point geometry table)
- fid
- geom
- name
- lastname
- cities_id (foreign key, indexed)
- streets_id (foreign key, indexed)
- house_number

ADDITIONAL NOTES:

one city is considered a big city with lots of streets - 2700 (city name: Aba, city id: 14)
one city has no streets (city name: Zhangwan, city id: 7422)

Steps to reproduce the issue

Download example project with GPGK - qgis_relation_editor.zip.

Open project and try to add some data to persons layer - see video demo.

NOTE: In last version I tried to hide "Streets" upon opening attribute form, but got nothing in terms of speed increase.

Versions

QGIS version 3.28.1-Firenze QGIS code revision fde3b8fbb8c Qt version 5.15.3 Python version 3.9.5 GDAL/OGR version 3.6.0 PROJ version 9.1.0 EPSG Registry database version v10.074 (2022-08-01) GEOS version 3.11.1-CAPI-1.17.1 SQLite version 3.39.4 PDAL version 2.4.3 PostgreSQL client version unknown SpatiaLite version 5.0.1 QWT version 6.1.6 QScintilla2 version 2.13.1 OS version Windows 10 Version 2009

Active Python plugins actions_for_relations 1.2.0 document_management_system 0.3.1 EquirectangularViewer 1.4 GroupStats 2.2.6 plugin_reloader 0.9.2 QGIS3-getWKT 1.4 QuickWKT 3.1 SelectByRelationship 0.3.3 db_manager 0.1.20 processing 2.12.99

Supported QGIS version

[X] I'm running a supported QGIS version according to the roadmap.

New profile

[X] I tried with a new QGIS profile

Additional context

I tried to select manually all streets for big city (id = 14), and DBMS retrieved all 2700 results in 0.001 seconds.

SELECT
    id,
    name,
    length,
    cities_id
FROM
    streets
WHERE
    cities_id = 14;

Seems to me like a N+1 Query problem or sometimes referred as N+1 Selects problem - see for example Understanding and fixing N+1 query

Also might be useful - SPEEDING UP YOUR PYQGIS SCRIPTS

elpaso commented 1 year ago

Your analysis is correct: the value-relation widget does retrieve all records from related tables (city and streets) for every record of the persons table.

There is no way to avoid that, given the current design of the widgets, one reason is that each record of persons could set a different filter to retrieve the features from the related tables but there is also a stronger blocker which is the way the widgets are created.

The workaround is to configure the relations in the project settings and use relation-reference widget.

immagine

phidrho commented 1 year ago

Hi @elpaso,

thank you for your comment.

I just tried it, but then I get empty dropdown for Streets if same Filter is used:

elpaso commented 1 year ago

Yeah, sorry, I didn't notice you had a filter. That doesn't work in relation reference widgets.

phidrho commented 1 year ago

Your analysis is correct: the value-relation widget does retrieve all records from related tables (city and streets) for every record of the persons table.

I'm not sure, but I think that problem is not that it retrieves all records of city and streets for each person - it would be done much faster, the problem seems deeper, since it takes so long, here what I think is happening:

it retrieves a person
than retrieves first city
than retrieves all streets (for first city)
than it retrieves second city
than again retrieves all streets (for second city) ...
than it retrieves last city
than again retrieves all streets (for last city)

-> first person done

than it goes to second person
than retrieves first city
than retrieves all streets (for first city) ...

You get the pattern, maybe not in this exact order, it takes very long to loop through everything this way, there is 77960 * 7540 = 587 818 400 single steps for each person. When I create new person it takes around 15 seconds on my PC as shown in video.

Solution to this (on theoretical level) should be to fetch all cities and all streets and generate some fast data structure to hold it in memory (for each Value Relation) whole time while program is running and access it when needed for representation.

phidrho commented 1 year ago

Hi @elpaso,

The workaround is to configure the relations in the project settings and use relation-reference widget.

I slightly reorganized my data - I added city_name field to streets table so that I can use Relations Reference widget, and I do not collect cities_id for a new feature in persons table, I will fill that data manually or with some DB trigger.

form_setup

Now it works really fast (as expected), but now I stumbled upon another bug, which is already reported (https://github.com/qgis/QGIS/issues/37269).

limitation

Here is a new version of QGIS project and DB - relation_editor_bug_v2.zip.

qgis / QGIS