2

I am currently working on a SpatiaLite layer that is updated after insertions and modifications through triggers.

When I copy-paste features on the "HABITATS_POLY" layer, it triggers updates on another layer ("GESTION_CODES"). The results from this second layer are then utilized to update the original layer, effectively completing the process in three steps.

Unfortunately, I'm encountering a significant performance issue during this sequence. QGIS experiences extreme slowness and, in some cases, freezes entirely.

Despite my attempts to optimize the triggers in various ways, I'm still grappling with the challenge. I think that the root cause may lie in the systematic search for codes across the entire GESTION_CODES table. While this table typically contains up to a maximum of a hundred rows, and often fewer, the repetitive nature of this search hundreds of times could be contributing to the slowdown.

I haven't identified an alternative approach that meets my requirements and resolves the performance issue.

Hereafter, the triggers that are executed :

AFTER INSERT ON HABITATS_POLY: [Insert a unique occurrence of each code entered in HABITATS_POLY (from the fields: EUNIS_1, EUNIS_2, EUNIS_3, and EUNIS_4) into the GESTION_CODE table (and other columns not important here)]

    CREATE TRIGGER A_insert_eunis_gest AFTER INSERT ON HABITATS_POLY
BEGIN
    INSERT INTO GESTION_CODES (EUNIS, LIB_PHYSIO, Visibilite)
    SELECT DISTINCT EUNIS, LIB_PHYSIO, Visibilite
    FROM (
        SELECT NEW.EUNIS_1 AS EUNIS, NEW.LIB_PHYSIO, NEW.Visibilite
        UNION
        SELECT NEW.EUNIS_2, NEW.LIB_PHYSIO, NEW.Visibilite
        UNION
        SELECT NEW.EUNIS_3, NEW.LIB_PHYSIO, NEW.Visibilite
        UNION
        SELECT NEW.EUNIS_4, NEW.LIB_PHYSIO, NEW.Visibilite
    ) AS merged_data
    WHERE EUNIS IS NOT NULL
        AND EUNIS NOT IN (SELECT EUNIS FROM GESTION_CODES);
END;

AFTER INSERT ON GESTION_CODES: [For every feature in the "EUNIS" field, a corresponding "EUNIS22" code is searched in the BD_EUNIS22 database.]

CREATE TRIGGER C_search_eunis22
AFTER INSERT ON GESTION_CODES
FOR EACH ROW
BEGIN

    SELECT COUNT(*) 
    FROM BD_EUNIS22
    WHERE "Habitat EUNIS12" = NEW.EUNIS
    LIMIT 2;

    UPDATE GESTION_CODES
    SET "EUNIS22" =
        CASE
            WHEN (SELECT COUNT(*) FROM BD_EUNIS22 WHERE "Habitat EUNIS12" = NEW.EUNIS) = 1 THEN (SELECT "Habitat EUNIS22" FROM BD_EUNIS22 WHERE "Habitat EUNIS12" = NEW.EUNIS)
            WHEN (SELECT COUNT(*) FROM BD_EUNIS22 WHERE "Habitat EUNIS12" = NEW.EUNIS) = 0 THEN '-'            
        ELSE '[Multiple]'
        END,
        CHOIX_EUNIS22 = 
            CASE
                WHEN (SELECT COUNT(*) FROM BD_EUNIS22 WHERE "Habitat EUNIS12" = NEW.EUNIS) > 1 THEN 'Voir liste'
            END
    WHERE EUNIS = NEW.EUNIS;
END;

AFTER UPDATING EUNIS22 ON GESTION_CODES: [Once the codes have been updated in GESTION_CODES, they are transferred back to their respective columns (EUNIS22_1, EUNIS22_2, EUNIS22_3 or EUNIS22_4 in HABITATS_POLY.]

CREATE TRIGGER G_rapat_eunis_to_eunis22_updt 
AFTER UPDATE OF EUNIS22 ON GESTION_CODES 
FOR EACH ROW 

BEGIN 
UPDATE HABITATS_POLY 
SET
        "EUNIS22_1" = COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_1" AND "EUNIS_1" IS NOT NULL), "-"),
        "EUNIS22_2" = COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_2" AND "EUNIS_2" IS NOT NULL), "-"),
        "EUNIS22_3" = COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_3" AND "EUNIS_3" IS NOT NULL), "-"),
        "EUNIS22_4" = COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_4" AND "EUNIS_4" IS NOT NULL), "-");

END 

AFTER INSERT ON HABITATS_POLY: [Update again the fields EUNIS22, this turned out to be necessary to ensure that all the features were properly updated]

CREATE TRIGGER B_rapat_eunis22_insert
AFTER INSERT ON HABITATS_POLY
FOR EACH ROW
BEGIN    
    UPDATE HABITATS_POLY
    SET 
        "EUNIS22_1" = COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_1" AND "EUNIS_1" IS NOT NULL), "-"),
        "EUNIS22_2" = COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_2" AND "EUNIS_2" IS NOT NULL), "-"),
        "EUNIS22_3" = COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_3" AND "EUNIS_3" IS NOT NULL), "-"),
        "EUNIS22_4" = COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_4" AND "EUNIS_4" IS NOT NULL), "-");
END;

What solution could I implement to improve this process ?

I believe utilizing a VIEW might not be practical since BD_EUNIS22 and GESTION_CODES cannot be downsized. In VBA, I'm used to working with virtual temporary tables. Perhaps there's a way (and a necessity) to implement a similar approach here? Both layers, HABITATS_POLY and GESTION_CODES, are operational layers actively used by the user. I have indexed the following columns : EUNIS_1, EUNIS_2, EUNIS_3 and EUNIS_4 on HABITATS_POLY. And column EUNIS on GESTION_CODES.

EDIT1: Some changes to the code, to add conditions to WHERE, and remove CASE. Without improvements on the performances.


EDIT2: Here is the trigger I am testing (with different small changes). But it didn't improve the process.

    CREATE TRIGGER B_rapat_eunis22_insert
AFTER INSERT ON HABITATS_POLY
BEGIN

UPDATE HABITATS_POLY 
SET
    "EUNIS22_1" = CASE WHEN "EUNIS_1" IS NOT NULL THEN COALESCE(gc1."EUNIS22", "-") ELSE NULL END,
    "EUNIS22_2" = CASE WHEN "EUNIS_2" IS NOT NULL THEN COALESCE(gc2."EUNIS22", "-") ELSE NULL END,
    "EUNIS22_3" = CASE WHEN "EUNIS_3" IS NOT NULL THEN COALESCE(gc3."EUNIS22", "-") ELSE NULL END,
    "EUNIS22_4" = CASE WHEN "EUNIS_4" IS NOT NULL THEN COALESCE(gc4."EUNIS22", "-") ELSE NULL END
FROM GESTION_CODES AS gc1
LEFT JOIN GESTION_CODES AS gc2 ON gc2."EUNIS" = "EUNIS_2"
LEFT JOIN GESTION_CODES AS gc3 ON gc3."EUNIS" = "EUNIS_3"
LEFT JOIN GESTION_CODES AS gc4 ON gc4."EUNIS" = "EUNIS_4"
    WHERE
        (gc1."EUNIS" = "EUNIS_1" AND "EUNIS_1" IS NOT NULL) OR 
        (gc2."EUNIS" = "EUNIS_2" AND "EUNIS_2" IS NOT NULL) OR 
        (gc3."EUNIS" = "EUNIS_3" AND "EUNIS_3" IS NOT NULL) OR 
        (gc4."EUNIS" = "EUNIS_4" AND "EUNIS_4" IS NOT NULL);
END;

EDIT3: Here is how the sequence of triggers works:

Process sequence

12
  • Edit2: The triggers B and G are the ones slowering the process. I tried another way, but it didn't improve. Code presented in EDIT2, on the post.
    – Giene
    Commented Dec 30, 2023 at 22:37
  • 1
    Have barely used triggers in sqlite, so not sure if it is a good idea or if it even works. Nonetheless, I would try to replace the .... IS NOT NULL filters with habitats_poly.rowid = NEW.rowid for both slow triggers. This should explicitly update only the new row, which is the idea anyway I think.
    – Pieter
    Commented Dec 30, 2023 at 23:03
  • Great ! Thank you ! I've been working on it since weeks... I remember removing "WHERE rowid = NEW.rowid;" thinking that I didn't need it because of the row I insert are already new. But it prevents from searching again and again on this same row when a second one is added, etc. (If I understand well). Well now it is all instantly done ! But... this lead to another issue, all the first new row have their EUNIS22 field populated with "-", because the correspondence in GESTION_CODES, doesn't exist yet. A few adjustments to come.
    – Giene
    Commented Dec 31, 2023 at 11:07
  • In general it is significantly faster if you can use a UNIQUE index and filtering on a value is typically also faster than on IS NOT NULL...
    – Pieter
    Commented Dec 31, 2023 at 13:32
  • I am using a composite index for EUNIS_1, EUNIS_2, EUNIS_3 and EUNIS_4. Is this what you mean? For now, I don't see any alternative to using 'IS NOT NULL' since, in the case of a NULL value, the result should differ from that when no result is found. I will look for another way and update my answer if I find something good.
    – Giene
    Commented Dec 31, 2023 at 14:46

2 Answers 2

2

EDIT: Finaly found it. I regrouped all the triggers and I worked with rowid and pkuid.

CREATE TRIGGER A_insert_eunis_gest AFTER INSERT ON HABITATS_POLY
BEGIN
    -- Insérer dans GESTION_CODES les lignes distinctes de EUNIS_1, EUNIS_2, EUNIS_3, EUNIS_4
    INSERT INTO GESTION_CODES (EUNIS, LIB_PHYSIO, Visibilite)
    SELECT DISTINCT EUNIS, NEW.LIB_PHYSIO, NEW.Visibilite
    FROM (
        SELECT NEW.EUNIS_1 AS EUNIS, NEW.LIB_PHYSIO, NEW.Visibilite WHERE NEW.EUNIS_1 IS NOT NULL
        UNION
        SELECT NEW.EUNIS_2, NEW.LIB_PHYSIO, NEW.Visibilite WHERE NEW.EUNIS_2 IS NOT NULL
        UNION
        SELECT NEW.EUNIS_3, NEW.LIB_PHYSIO, NEW.Visibilite WHERE NEW.EUNIS_3 IS NOT NULL
        UNION
        SELECT NEW.EUNIS_4, NEW.LIB_PHYSIO, NEW.Visibilite WHERE NEW.EUNIS_4 IS NOT NULL
    ) AS merged_data
    WHERE 
        merged_data.EUNIS IS NOT NULL AND 
        NOT EXISTS (SELECT 1 FROM GESTION_CODES WHERE EUNIS = merged_data.EUNIS);

    -- Mettre à jour les champs EUNIS22, CHOIX_EUNIS22 dans GESTION_CODES
    UPDATE GESTION_CODES
    SET 
        "EUNIS22" =
            CASE
                WHEN (SELECT COUNT(*) FROM BD_EUNIS22 WHERE "Habitat EUNIS12" = GESTION_CODES.EUNIS) = 1 THEN (SELECT "Habitat EUNIS22" FROM BD_EUNIS22 WHERE "Habitat EUNIS12" = GESTION_CODES.EUNIS)
                WHEN (SELECT COUNT(*) FROM BD_EUNIS22 WHERE "Habitat EUNIS12" = GESTION_CODES.EUNIS) = 0 THEN '-'            
                ELSE '[Multiple]'
            END,
        
        "CHOIX_EUNIS22" = 
            CASE WHEN (SELECT COUNT(*) FROM BD_EUNIS22 WHERE "Habitat EUNIS12" = GESTION_CODES.EUNIS) > 1 THEN 'Voir liste' END
    WHERE 
        GESTION_CODES.EUNIS IS NOT NULL AND 
        GESTION_CODES.rowid = last_insert_rowid();

    -- Mettre à jour les champs EUNIS22 dans HABITATS_POLY
    UPDATE HABITATS_POLY
    SET 
        "EUNIS22_1" = CASE WHEN NEW.EUNIS_1 IS NOT NULL THEN COALESCE((SELECT G.EUNIS22 FROM GESTION_CODES G WHERE G.EUNIS = NEW.EUNIS_1), "-") END,
        "EUNIS22_2" = CASE WHEN NEW.EUNIS_2 IS NOT NULL THEN COALESCE((SELECT G.EUNIS22 FROM GESTION_CODES G WHERE G.EUNIS = NEW.EUNIS_2), "-") END,
        "EUNIS22_3" = CASE WHEN NEW.EUNIS_3 IS NOT NULL THEN COALESCE((SELECT G.EUNIS22 FROM GESTION_CODES G WHERE G.EUNIS = NEW.EUNIS_3), "-") END,
        "EUNIS22_4" = CASE WHEN NEW.EUNIS_4 IS NOT NULL THEN COALESCE((SELECT G.EUNIS22 FROM GESTION_CODES G WHERE G.EUNIS = NEW.EUNIS_4), "-") END

    WHERE 
        HABITATS_POLY.pkuid = NEW.pkuid;
END;
0

Here are the final triggers, after changes suggested by Pieter and adjustments to make sure every row is properly updated. The process is way faster (11 seconds for 1000 features)

    CREATE TRIGGER A_insert_eunis_gest AFTER INSERT ON HABITATS_POLY
BEGIN
    INSERT INTO GESTION_CODES (EUNIS, LIB_PHYSIO, Visibilite)
    SELECT DISTINCT EUNIS, LIB_PHYSIO, Visibilite
    FROM (
        SELECT NEW.EUNIS_1 AS EUNIS, NEW.LIB_PHYSIO, NEW.Visibilite
        UNION
        SELECT NEW.EUNIS_2, NEW.LIB_PHYSIO, NEW.Visibilite
        UNION
        SELECT NEW.EUNIS_3, NEW.LIB_PHYSIO, NEW.Visibilite
        UNION
        SELECT NEW.EUNIS_4, NEW.LIB_PHYSIO, NEW.Visibilite
    ) AS merged_data
    WHERE EUNIS IS NOT NULL
        AND EUNIS NOT IN (SELECT EUNIS FROM GESTION_CODES);
END;

CREATE TRIGGER C_search_eunis22
AFTER INSERT ON GESTION_CODES
FOR EACH ROW
BEGIN

    SELECT COUNT(*) 
    FROM BD_EUNIS22
    WHERE "Habitat EUNIS12" = NEW.EUNIS
    LIMIT 2;

    UPDATE GESTION_CODES
    SET "EUNIS22" =
        CASE
            WHEN (SELECT COUNT(*) FROM BD_EUNIS22 WHERE "Habitat EUNIS12" = NEW.EUNIS) = 1 THEN (SELECT "Habitat EUNIS22" FROM BD_EUNIS22 WHERE "Habitat EUNIS12" = NEW.EUNIS)
            WHEN (SELECT COUNT(*) FROM BD_EUNIS22 WHERE "Habitat EUNIS12" = NEW.EUNIS) = 0 THEN '-'            
        ELSE '[Multiple]'
        END,
        CHOIX_EUNIS22 = 
            CASE
                WHEN (SELECT COUNT(*) FROM BD_EUNIS22 WHERE "Habitat EUNIS12" = NEW.EUNIS) > 1 THEN 'Voir liste'
            END
        WHERE EUNIS = NEW.EUNIS;
    END;

CREATE TRIGGER G_rapat_eunis_to_eunis22_updt
AFTER UPDATE OF EUNIS22 ON GESTION_CODES
FOR EACH ROW
BEGIN
    UPDATE HABITATS_POLY
    SET 
        "EUNIS22_1" = COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_1"), "-"),
        "EUNIS22_2" = COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_2"), "-"),
        "EUNIS22_3" = COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_3"), "-"),
        "EUNIS22_4" = COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_4"), "-")
    WHERE 
        "EUNIS_1" IS NOT NULL AND "EUNIS22_1" <> COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_1"), "-")
        AND "EUNIS_2" IS NOT NULL AND "EUNIS22_2" <> COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_2"), "-")
        AND "EUNIS_3" IS NOT NULL AND "EUNIS22_3" <> COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_3"), "-")
        AND "EUNIS_4" IS NOT NULL AND "EUNIS22_4" <> COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_4"), "-");
END;

CREATE TRIGGER B_rapat_eunis22_insert
AFTER INSERT ON HABITATS_POLY
FOR EACH ROW
BEGIN    
    UPDATE HABITATS_POLY
    SET 
        "EUNIS22_1" = COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_1"), "-"),
        "EUNIS22_2" = COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_2"), "-"),
        "EUNIS22_3" = COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_3"), "-"),
        "EUNIS22_4" = COALESCE((SELECT "EUNIS22" FROM GESTION_CODES WHERE "EUNIS" = "EUNIS_4"), "-")

WHERE rowid = NEW.rowid
      AND 
      (
        "EUNIS_1" IS NOT NULL OR
        "EUNIS_2" IS NOT NULL OR
        "EUNIS_3" IS NOT NULL OR
        "EUNIS_4" IS NOT NULL);
END;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.