Improving efficiency of database searches under SQLite (SpatiaLite - QGIS)

Question

I'm new to SQL, but I've always heard that it's a very efficient way of searching databases. For the same task, in VBA I get a result in a few seconds for 1000 queries. In SQL it takes several minutes.

I've tried going back and forth between my databases and my code to try and improve the execution speed of my triggers, without success.

Here's how my project looks (I work on QGIS, with SpatiaLite (SQLite) layers):

I have SpatiaLite layers :

ESPECES_PT (point geometry layer) [Important fields : ESPECE ; COMPART ; REGION]
ESPECES_ATTRIBUTS (no geometry) [Important fields : ESPECE ; COMPART ; REGION]

When a name is entered in "ESPECE", the table ESPECES_ATTRIBUTS is updated with the same "ESPECE", "REGION" and "COMPART" (if it does not already exist) and triggers are executed to search in several databases, a list of information. *I use 2 layers because the 1rst one can contain multiple identical names (not the second one)

There are 2 kinds of databases

TAXREF_
BDC_

All these databases are fragmented into 7 "compartments" [TAXREF_FLORE ; TAXREF_ENTOMO ; TAXREF_MAMMIFERES ; etc.] [BDC_FLORE ; BDC_ENTOMO ; BDC_MAMMIFERES ; etc.]

According to the contents of REGION and COMPART, the value of ESPECE will be searched in different databases. [For example: If COMPART = 'Flore' and REGION = 'Occitanie' then ESPECE will be searched into TAXREF_FLORE and in BDC_FLORE (in BDC_, the query will then look for "REGION" to get the correspondances)

The picture after shows (with the utmost clarity) how it work:

[On table ESPECES_PT : ESPECE = 'Ophrys provincialis' ; REGION = 'Occitanie' ; COMPART = 'Flore'] [On table ESPECES_ATTRIBUTS : ESPECE = 'Ophrys provincialis' ; REGION = 'Occitanie' ; COMPART = 'Flore' ; NOM_SCIENT_VALIDE is updated to the current valid name]

[On ESPECES_ATTRIBUTS : the query is performed from TAXREF_FLORE AND BDC_FLORE where several data are retrieved (in yellow) according to the value in NOM_VALIDE (table TAXREF) and REGION (table BDC).

This process is quite long and I'm wondering if you would have some tips and improvement to propose in order to improve it.

Here are the different important triggers :

When I add features on table ESPECES_PT (more or less the same for updates)

CREATE TRIGGER A_insert_esp 
AFTER INSERT ON ESPECES_PT
BEGIN
    UPDATE ESPECES_PT
    SET "COMPART" = COALESCE(ESPECES_PT.COMPART, (SELECT "COMPART" FROM ZONE_ETUDE_POLY), '[Voir Table ESPECES_ATTRIBUTS]'),
        "REGION" = COALESCE(ESPECES_PT.REGION, (SELECT "REGION" FROM ZONE_ETUDE_POLY), '[Voir Table ESPECES_ATTRIBUTS]'),
        "DATA_PROP" = (SELECT "Company" FROM C_copyright)
    WHERE pkuid = NEW.pkuid;

    INSERT INTO ESPECES_ATTRIBUTS (ESPECE, Visibilite, ENJEU_SPE, IMPORT_ZE, ENJEU_ZE, COMPART, REGION)
SELECT DISTINCT
    NEW.ESPECE,
    NEW.Visibilite,
    CASE 
        WHEN NEW.ENJEU_SPE LIKE '%1%' THEN 'Très fort'
        WHEN NEW.ENJEU_SPE LIKE '%2%' THEN 'Fort'
        WHEN NEW.ENJEU_SPE LIKE '%3%' THEN 'Modéré'
        WHEN NEW.ENJEU_SPE LIKE '%4%' THEN 'Faible'
        WHEN NEW.ENJEU_SPE LIKE '%5%' THEN 'Très faible'
        WHEN NEW.ENJEU_SPE LIKE '%6%' THEN NULL
        ELSE coalesce(NEW.ENJEU_SPE <> '[Voir Table ESPECES_ATTRIBUTS]',NULL)
    END,
    CASE 
        WHEN NEW.IMPORT_ZE LIKE '%1%' THEN 'Très fort'
        WHEN NEW.IMPORT_ZE LIKE '%2%' THEN 'Fort'
        WHEN NEW.IMPORT_ZE LIKE '%3%' THEN 'Modéré'
        WHEN NEW.IMPORT_ZE LIKE '%4%' THEN 'Faible'
        WHEN NEW.IMPORT_ZE LIKE '%5%' THEN 'Très faible'
        WHEN NEW.IMPORT_ZE LIKE '%6%' THEN NULL
        ELSE coalesce(NEW.IMPORT_ZE <> '[Voir Table ESPECES_ATTRIBUTS]',NULL)
    END,
    CASE 
        WHEN NEW.ENJEU_ZE LIKE '%1%' THEN 'Très fort'
        WHEN NEW.ENJEU_ZE LIKE '%2%' THEN 'Fort'
        WHEN NEW.ENJEU_ZE LIKE '%3%' THEN 'Modéré'
        WHEN NEW.ENJEU_ZE LIKE '%4%' THEN 'Faible'
        WHEN NEW.ENJEU_ZE LIKE '%5%' THEN 'Très faible'
        WHEN NEW.ENJEU_ZE LIKE '%6%' THEN NULL
        ELSE coalesce(NEW.ENJEU_ZE <> '[Voir Table ESPECES_ATTRIBUTS]',NULL)
    END,
    CASE 
        WHEN ESPECES_PT.COMPART <> '[Voir Table ESPECES_ATTRIBUTS]' AND ESPECES_PT.COMPART IS NOT NULL THEN ESPECES_PT.COMPART
    END,
    CASE 
        WHEN ESPECES_PT.REGION <> '[Voir Table ESPECES_ATTRIBUTS]' AND ESPECES_PT.REGION IS NOT NULL THEN ESPECES_PT.REGION
    END
FROM ESPECES_PT
WHERE ESPECES_PT.pkuid = NEW.pkuid 
    AND NEW.ESPECE IS NOT NULL 
    AND NEW.ESPECE NOT IN (SELECT ESPECE FROM ESPECES_ATTRIBUTS);

UPDATE ESPECES_ATTRIBUTS
SET NOM_SIMPLE = (
    WITH RECURSIVE split(value, str) AS (
        SELECT null, NEW.ESPECE || ' ' FROM ESPECES_ATTRIBUTS WHERE ESPECES_ATTRIBUTS.rowid = last_insert_rowid()
        UNION ALL
        SELECT substr(str, 0, instr(str, ' ')), substr(str, instr(str, ' ')+1) FROM split WHERE str != ''
    )
    SELECT 
        CASE 
            WHEN NEW.ESPECE LIKE '%var.%' OR NEW.ESPECE LIKE '%subsp.%' THEN
                COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1), '') 
                || COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 1), '' ) 
                || COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 2), '') 
                || TRIM(COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 3), ''))
            ELSE
                COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1), '') 
                || TRIM(COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 1), '' ))
        END
)
WHERE ESPECES_ATTRIBUTS.rowid = last_insert_rowid();

UPDATE ESPECES_ATTRIBUTS
SET NOM_SCIENT_VALIDE = 
   CASE 
        WHEN ESPECES_ATTRIBUTS.COMPART = 'Flore' THEN
            COALESCE(
                (SELECT "NOM_VALIDE" FROM TAXREF_FLORE WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SIMPLE), 
                    CASE 
                        WHEN (SELECT COUNT(*) FROM TAXREF_FLORE WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 1 THEN (SELECT "NOM_VALIDE" FROM TAXREF_FLORE WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE)
                        WHEN (SELECT COUNT(*) FROM TAXREF_FLORE WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 0 THEN 'Absent'
                        ELSE
                            '(Synonymes)'
                    END)            
        WHEN ESPECES_ATTRIBUTS.COMPART = 'Amphibiens' THEN 
            COALESCE(
                (SELECT "NOM_VALIDE" FROM TAXREF_AMPHIBIENS WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SIMPLE), 
                    CASE 
                        WHEN (SELECT COUNT(*) FROM TAXREF_AMPHIBIENS WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 1 THEN (SELECT "NOM_VALIDE" FROM TAXREF_AMPHIBIENS WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE)
                        WHEN (SELECT COUNT(*) FROM TAXREF_AMPHIBIENS WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 0 THEN 'Absent'
                        ELSE
                            '(Synonymes)'
                    END)                  
        WHEN ESPECES_ATTRIBUTS.COMPART = 'Arthropodes' THEN 
            COALESCE(
                (SELECT "NOM_VALIDE" FROM TAXREF_ENTOMO WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SIMPLE), 
                    CASE 
                        WHEN (SELECT COUNT(*) FROM TAXREF_ENTOMO WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 1 THEN (SELECT "NOM_VALIDE" FROM TAXREF_ENTOMO WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE)
                        WHEN (SELECT COUNT(*) FROM TAXREF_ENTOMO WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 0 THEN 'Absent'
                        ELSE
                            '(Synonymes)'
                    END)            
        WHEN ESPECES_ATTRIBUTS.COMPART = 'Avifaune' THEN 
            COALESCE(
                (SELECT "NOM_VALIDE" FROM TAXREF_OISEAUX WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SIMPLE), 
                    CASE 
                        WHEN (SELECT COUNT(*) FROM TAXREF_OISEAUX WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 1 THEN (SELECT "NOM_VALIDE" FROM TAXREF_OISEAUX WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE)
                        WHEN (SELECT COUNT(*) FROM TAXREF_OISEAUX WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 0 THEN 'Absent'
                        ELSE
                            '(Synonymes)'
                    END)     
        WHEN ESPECES_ATTRIBUTS.COMPART = 'Mammifères' THEN  
            COALESCE(
                (SELECT "NOM_VALIDE" FROM TAXREF_MAMMIFERES WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SIMPLE), 
                    CASE 
                        WHEN (SELECT COUNT(*) FROM TAXREF_MAMMIFERES WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 1 THEN (SELECT "NOM_VALIDE" FROM TAXREF_MAMMIFERES WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE)
                        WHEN (SELECT COUNT(*) FROM TAXREF_MAMMIFERES WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 0 THEN 'Absent'
                        ELSE
                            '(Synonymes)'
                    END)                    
        WHEN ESPECES_ATTRIBUTS.COMPART = 'Poissons' THEN 
            COALESCE(
                (SELECT "NOM_VALIDE" FROM TAXREF_POISSONS WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SIMPLE), 
                    CASE 
                        WHEN (SELECT COUNT(*) FROM TAXREF_POISSONS WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 1 THEN (SELECT "NOM_VALIDE" FROM TAXREF_POISSONS WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE)
                        WHEN (SELECT COUNT(*) FROM TAXREF_POISSONS WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 0 THEN 'Absent'
                        ELSE
                            '(Synonymes)'
                    END)                               
        WHEN ESPECES_ATTRIBUTS.COMPART = 'Reptiles' THEN 
            COALESCE(
                (SELECT "NOM_VALIDE" FROM TAXREF_REPTILES WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SIMPLE), 
                    CASE 
                        WHEN (SELECT COUNT(*) FROM TAXREF_REPTILES WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 1 THEN (SELECT "NOM_VALIDE" FROM TAXREF_REPTILES WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE)
                        WHEN (SELECT COUNT(*) FROM TAXREF_REPTILES WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 0 THEN 'Absent'
                        ELSE
                            '(Synonymes)'
                    END) 
        ELSE 'Nouveau compartiment'
    END
WHERE ESPECES_ATTRIBUTS.rowid = last_insert_rowid();

UPDATE ESPECES_ATTRIBUTS
    SET  "CHOIX_ESPECE" = 'Voir liste'
WHERE ESPECES_ATTRIBUTS.rowid = last_insert_rowid() AND NOM_SCIENT_VALIDE = '(Synonymes)';

INSERT INTO SYNONYMES (ID, ESPECE, NOM_SCIENT_VALIDE)
SELECT DISTINCT 
    EA.ID,
    EA.ESPECE,
    T.NOM_VALIDE
FROM ESPECES_ATTRIBUTS EA
JOIN TAXREF_FLORE T ON T.LB_NOM = EA.NOM_SIMPLE
WHERE EA.ID = last_insert_rowid() 
    AND EA.NOM_SCIENT_VALIDE = '(Synonymes)' 
    AND EA.COMPART IS NOT NULL 
    AND NOT EXISTS (
        SELECT 1 
        FROM SYNONYMES S 
        WHERE S.ESPECE = EA.ESPECE 
            AND S.NOM_SCIENT_VALIDE = T.NOM_VALIDE
    )
    AND (
        (EA.COMPART = 'Flore' AND EXISTS (SELECT 1 FROM TAXREF_FLORE WHERE LB_NOM = EA.NOM_SIMPLE))
        OR (EA.COMPART = 'Amphibiens' AND EXISTS (SELECT 1 FROM TAXREF_AMPHIBIENS WHERE LB_NOM = EA.NOM_SIMPLE))
        OR (EA.COMPART = 'Arthropodes' AND EXISTS (SELECT 1 FROM TAXREF_ENTOMO WHERE LB_NOM = EA.NOM_SIMPLE))
        OR (EA.COMPART = 'Avifaune' AND EXISTS (SELECT 1 FROM TAXREF_OISEAUX WHERE LB_NOM = EA.NOM_SIMPLE))
        OR (EA.COMPART = 'Mammifères' AND EXISTS (SELECT 1 FROM TAXREF_MAMMIFERES WHERE LB_NOM = EA.NOM_SIMPLE))
        OR (EA.COMPART = 'Poissons' AND EXISTS (SELECT 1 FROM TAXREF_POISSONS WHERE LB_NOM = EA.NOM_SIMPLE))
        OR (EA.COMPART = 'Reptiles' AND EXISTS (SELECT 1 FROM TAXREF_REPTILES WHERE LB_NOM = EA.NOM_SIMPLE))
    );
END

The code to update "ESPECE" into "NOM_SCIENT_VALIDE" (in table ESPECES_ATTRIBUTS)

CREATE TRIGGER B_update_nom_scient
AFTER UPDATE OF COMPART, REGION, ESPECE ON ESPECES_ATTRIBUTS 
FOR EACH ROW
BEGIN
    UPDATE ESPECES_ATTRIBUTS
    SET NOM_SIMPLE = (
        WITH RECURSIVE split(value, str) AS (
            SELECT null, NEW.ESPECE || ' ' 
            FROM ESPECES_ATTRIBUTS
            WHERE ID = NEW.ID
            UNION ALL
            SELECT substr(str, 0, instr(str, ' ')), substr(str, instr(str, ' ')+1)
            FROM split
            WHERE str != ''
        )
        SELECT
            CASE
                WHEN NEW.ESPECE LIKE '%var.%' OR NEW.ESPECE LIKE '%subsp.%' THEN
                     COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1), '') ||
                     COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 1), '' ) ||
                     COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 2), '') ||
                     TRIM(COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 3), ''))
                ELSE
                     COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1), '') ||
                     TRIM(COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 1), '' )) 
            END
    )
    WHERE ID = NEW.ID;

    UPDATE ESPECES_ATTRIBUTS
    SET NOM_SCIENT_VALIDE = 
       CASE 
            WHEN ESPECES_ATTRIBUTS.COMPART = 'Flore' THEN
                COALESCE(
                    (SELECT "NOM_VALIDE" FROM TAXREF_FLORE WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SIMPLE), 
                        CASE 
                            WHEN (SELECT COUNT(*) FROM TAXREF_FLORE WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 1 THEN (SELECT "NOM_VALIDE" FROM TAXREF_FLORE WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE)
                            WHEN (SELECT COUNT(*) FROM TAXREF_FLORE WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 0 THEN 'Absent'
                            ELSE
                                '(Synonymes)'
                        END)            
            WHEN ESPECES_ATTRIBUTS.COMPART = 'Amphibiens' THEN 
                COALESCE(
                    (SELECT "NOM_VALIDE" FROM TAXREF_AMPHIBIENS WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SIMPLE), 
                        CASE 
                            WHEN (SELECT COUNT(*) FROM TAXREF_AMPHIBIENS WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 1 THEN (SELECT "NOM_VALIDE" FROM TAXREF_AMPHIBIENS WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE)
                            WHEN (SELECT COUNT(*) FROM TAXREF_AMPHIBIENS WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 0 THEN 'Absent'
                            ELSE
                                '(Synonymes)'
                        END)                  
            WHEN ESPECES_ATTRIBUTS.COMPART = 'Entomofaune' THEN 
                COALESCE(
                    (SELECT "NOM_VALIDE" FROM TAXREF_ENTOMO WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SIMPLE), 
                        CASE 
                            WHEN (SELECT COUNT(*) FROM TAXREF_ENTOMO WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 1 THEN (SELECT "NOM_VALIDE" FROM TAXREF_ENTOMO WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE)
                            WHEN (SELECT COUNT(*) FROM TAXREF_ENTOMO WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 0 THEN 'Absent'
                            ELSE
                                '(Synonymes)'
                        END)            
            WHEN ESPECES_ATTRIBUTS.COMPART = 'Oiseaux' THEN 
                COALESCE(
                    (SELECT "NOM_VALIDE" FROM TAXREF_OISEAUX WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SIMPLE), 
                        CASE 
                            WHEN (SELECT COUNT(*) FROM TAXREF_OISEAUX WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 1 THEN (SELECT "NOM_VALIDE" FROM TAXREF_OISEAUX WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE)
                            WHEN (SELECT COUNT(*) FROM TAXREF_OISEAUX WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 0 THEN 'Absent'
                            ELSE
                                '(Synonymes)'
                        END)     
            WHEN ESPECES_ATTRIBUTS.COMPART = 'Mammifères' THEN  
                COALESCE(
                    (SELECT "NOM_VALIDE" FROM TAXREF_MAMMIFERES WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SIMPLE), 
                        CASE 
                            WHEN (SELECT COUNT(*) FROM TAXREF_MAMMIFERES WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 1 THEN (SELECT "NOM_VALIDE" FROM TAXREF_MAMMIFERES WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE)
                            WHEN (SELECT COUNT(*) FROM TAXREF_MAMMIFERES WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 0 THEN 'Absent'
                            ELSE
                                '(Synonymes)'
                        END)                                                   
            WHEN ESPECES_ATTRIBUTS.COMPART = 'Reptiles' THEN 
                COALESCE(
                    (SELECT "NOM_VALIDE" FROM TAXREF_REPTILES WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SIMPLE), 
                        CASE 
                            WHEN (SELECT COUNT(*) FROM TAXREF_REPTILES WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 1 THEN (SELECT "NOM_VALIDE" FROM TAXREF_REPTILES WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE)
                            WHEN (SELECT COUNT(*) FROM TAXREF_REPTILES WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) = 0 THEN 'Absent'
                            ELSE
                                '(Synonymes)'
                        END) 
            ELSE NULL
        END

    WHERE ID = NEW.ID AND ESPECES_ATTRIBUTS.COMPART is not NULL;
END;

The code for searching the databases

CREATE TRIGGER B_update_spe_stat
AFTER UPDATE OF NOM_SCIENT_VALIDE ON ESPECES_ATTRIBUTS 
FOR EACH ROW
BEGIN

    UPDATE ESPECES_ATTRIBUTS
        SET  "CHOIX_ESPECE" = 'Voir liste'
    WHERE ID = NEW.ID AND NOM_SCIENT_VALIDE = '(Synonymes)';

    UPDATE ESPECES_ATTRIBUTS
        SET 
            NOM_VERN  = 
                CASE 
                    WHEN "COMPART" = 'Flore' THEN (SELECT "NOM_VERN" FROM TAXREF_FLORE WHERE "NOM_VALIDE" = ESPECES_ATTRIBUTS.NOM_SCIENT_VALIDE)
                    WHEN "COMPART" = 'Amphibiens' THEN (SELECT "NOM_VERN" FROM TAXREF_AMPHIBIENS WHERE "NOM_VALIDE" = ESPECES_ATTRIBUTS.NOM_SCIENT_VALIDE)
                    WHEN "COMPART" = 'Entomofaune' THEN (SELECT "NOM_VERN" FROM TAXREF_ENTOMO WHERE "NOM_VALIDE" = ESPECES_ATTRIBUTS.NOM_SCIENT_VALIDE)
                    WHEN "COMPART" = 'Oiseaux' THEN (SELECT "NOM_VERN" FROM TAXREF_OISEAUX WHERE "NOM_VALIDE" = ESPECES_ATTRIBUTS.NOM_SCIENT_VALIDE)
                    WHEN "COMPART" = 'Mammifères' THEN (SELECT "NOM_VERN" FROM TAXREF_MAMMIFERES WHERE "NOM_VALIDE" = ESPECES_ATTRIBUTS.NOM_SCIENT_VALIDE)
                    WHEN "COMPART" = 'Reptiles' THEN (SELECT "NOM_VERN" FROM TAXREF_REPTILES WHERE "NOM_VALIDE" = ESPECES_ATTRIBUTS.NOM_SCIENT_VALIDE)
                END,
        
            (CHOROLOGIE, FLORAISON, HABIT_S, CARAC_ECO, INDIC_PHYTO, TYPE_BIOLOGIQUE, FORMATION_VEGETALE, ENJEU_paca, ZH, MESSICOLE) = 
                    (SELECT
                        "CHOROLOGIE",
                        "floraison",
                        "HABITAT SIMPLE",
                        "CARACTERISATION_ECOLOGIQUE_(HABITAT_OPTIMAL)",
                        "INDICATION_PHYTOSOCIOLOGIQUE_CARACTERISTIQUE",
                        "TYPE_BIOLOGIQUE",
                        "FORMATION_VEGETALE",
                        "ENJEU PACA",
                        "Statut ZH",
                        "Statut messicole"
                    FROM TAXREF_FLORE
                    WHERE "COMPART" = 'Flore'  AND "NOM_VALIDE" = ESPECES_ATTRIBUTS.NOM_SCIENT_VALIDE)

    WHERE ID = NEW.ID;

    INSERT INTO SYNONYMES (ID, ESPECE, NOM_SCIENT_VALIDE)
    SELECT DISTINCT
        EA.ID,
        EA.ESPECE,
        T.NOM_VALIDE
    FROM
        ESPECES_ATTRIBUTS EA
    JOIN
        TAXREF_FLORE T ON T.LB_NOM = EA.NOM_SIMPLE
    WHERE
        EA.ID = NEW.ID
        AND EA.NOM_SCIENT_VALIDE = '(Synonymes)'
        AND NOT EXISTS (
            SELECT 1
            FROM SYNONYMES S
            WHERE S.ESPECE = EA.ESPECE AND S.NOM_SCIENT_VALIDE = T.NOM_VALIDE
        )
        AND (
            (EA.COMPART = 'Flore' AND EXISTS (SELECT 1 FROM TAXREF_FLORE WHERE LB_NOM = EA.NOM_SIMPLE))
            OR (EA.COMPART = 'Amphibiens' AND EXISTS (SELECT 1 FROM TAXREF_AMPHIBIENS WHERE LB_NOM = EA.NOM_SIMPLE))
            OR (EA.COMPART = 'Arthropodes' AND EXISTS (SELECT 1 FROM TAXREF_ENTOMO WHERE LB_NOM = EA.NOM_SIMPLE))
            OR (EA.COMPART = 'Avifaune' AND EXISTS (SELECT 1 FROM TAXREF_OISEAUX WHERE LB_NOM = EA.NOM_SIMPLE))
            OR (EA.COMPART = 'Mammifères' AND EXISTS (SELECT 1 FROM TAXREF_MAMMIFERES WHERE LB_NOM = EA.NOM_SIMPLE))
            OR (EA.COMPART = 'Reptiles' AND EXISTS (SELECT 1 FROM TAXREF_REPTILES WHERE LB_NOM = EA.NOM_SIMPLE))
        );

    UPDATE ESPECES_ATTRIBUTS 
    SET PN = 
        CASE 
            WHEN COMPART = 'Flore' THEN (SELECT CODE_STATUT FROM BDC_FLORE WHERE NOM_VALIDE_COMPLET = NOM_SCIENT_VALIDE AND CD_TYPE_STATUT = 'PN')
            WHEN COMPART = 'Amphibiens' THEN (SELECT CODE_STATUT FROM BDC_AMPHIBIENS WHERE NOM_VALIDE_COMPLET = NOM_SCIENT_VALIDE AND CD_TYPE_STATUT = 'PN')
            WHEN COMPART = 'Entomofaune' THEN (SELECT CODE_STATUT FROM BDC_ENTOMO WHERE NOM_VALIDE_COMPLET = NOM_SCIENT_VALIDE AND CD_TYPE_STATUT = 'PN')
            WHEN COMPART = 'Oiseaux' THEN (SELECT CODE_STATUT FROM BDC_OISEAUX WHERE NOM_VALIDE_COMPLET = NOM_SCIENT_VALIDE AND CD_TYPE_STATUT = 'PN')
            WHEN COMPART = 'Mammifères' THEN (SELECT CODE_STATUT FROM BDC_MAMMIFERES WHERE NOM_VALIDE_COMPLET = NOM_SCIENT_VALIDE AND CD_TYPE_STATUT = 'PN')
            WHEN COMPART = 'Reptiles' THEN (SELECT CODE_STATUT FROM BDC_REPTILES WHERE NOM_VALIDE_COMPLET = NOM_SCIENT_VALIDE AND CD_TYPE_STATUT = 'PN')
            ELSE NULL
        END,
    
    PR = 
        CASE 
            WHEN COMPART = 'Flore' THEN (SELECT CODE_STATUT FROM BDC_FLORE WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'PR')
            WHEN COMPART = 'Amphibiens' THEN (SELECT CODE_STATUT FROM BDC_AMPHIBIENS WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'PR')
            WHEN COMPART = 'Entomofaune' THEN (SELECT CODE_STATUT FROM BDC_ENTOMO WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'PR')
            WHEN COMPART = 'Oiseaux' THEN (SELECT CODE_STATUT FROM BDC_OISEAUX WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'PR')
            WHEN COMPART = 'Mammifères' THEN (SELECT CODE_STATUT FROM BDC_MAMMIFERES WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'PR')
            WHEN COMPART = 'Poissons' THEN (SELECT CODE_STATUT FROM BDC_POISSONS WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'PR')
            WHEN COMPART = 'Reptiles' THEN (SELECT CODE_STATUT FROM BDC_REPTILES WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'PR')
            ELSE NULL
        END,
    
    ListeRN = 
        CASE 
            WHEN COMPART = 'Flore' THEN (SELECT CODE_STATUT FROM BDC_FLORE WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CD_TYPE_STATUT = 'LRN')
            WHEN COMPART = 'Amphibiens' THEN (SELECT CODE_STATUT FROM BDC_AMPHIBIENS WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CD_TYPE_STATUT = 'LRN')
            WHEN COMPART = 'Entomofaune' THEN (SELECT CODE_STATUT FROM BDC_ENTOMO WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CD_TYPE_STATUT = 'LRN')
            WHEN COMPART = 'Oiseaux' THEN (SELECT CODE_STATUT FROM BDC_OISEAUX WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CD_TYPE_STATUT = 'LRN')
            WHEN COMPART = 'Mammifères' THEN (SELECT CODE_STATUT FROM BDC_MAMMIFERES WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CD_TYPE_STATUT = 'LRN')
            WHEN COMPART = 'Poissons' THEN (SELECT CODE_STATUT FROM BDC_POISSONS WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CD_TYPE_STATUT = 'LRN')
            WHEN COMPART = 'Reptiles' THEN (SELECT CODE_STATUT FROM BDC_REPTILES WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CD_TYPE_STATUT = 'LRN')
            ELSE NULL
        END,
    
    ListeRR = 
        CASE 
            WHEN COMPART = 'Flore' THEN (SELECT CODE_STATUT FROM BDC_FLORE WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'LRR')
            WHEN COMPART = 'Amphibiens' THEN (SELECT CODE_STATUT FROM BDC_AMPHIBIENS WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'LRR')
            WHEN COMPART = 'Entomofaune' THEN (SELECT CODE_STATUT FROM BDC_ENTOMO WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'LRR')
            WHEN COMPART = 'Oiseaux' THEN (SELECT CODE_STATUT FROM BDC_OISEAUX WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'LRR')
            WHEN COMPART = 'Mammifères' THEN (SELECT CODE_STATUT FROM BDC_MAMMIFERES WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'LRR')
            WHEN COMPART = 'Poissons' THEN (SELECT CODE_STATUT FROM BDC_POISSONS WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'LRR')
            WHEN COMPART = 'Reptiles' THEN (SELECT CODE_STATUT FROM BDC_REPTILES WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'LRR')
            ELSE NULL
        END,
    
    ZNIEFF = 
        CASE 
            WHEN COMPART = 'Flore' THEN 'Déterminante ZNIEFF - ' || (SELECT CORRESPONDANCE_REGION FROM BDC_FLORE WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'ZDET')
            WHEN COMPART = 'Amphibiens' THEN 'Déterminante ZNIEFF - ' || (SELECT CORRESPONDANCE_REGION FROM BDC_AMPHIBIENS WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'ZDET')
            WHEN COMPART = 'Entomofaune' THEN 'Déterminante ZNIEFF - ' || (SELECT CORRESPONDANCE_REGION FROM BDC_ENTOMO WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'ZDET')
            WHEN COMPART = 'Oiseaux' THEN 'Déterminante ZNIEFF - ' || (SELECT CORRESPONDANCE_REGION FROM BDC_OISEAUX WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'ZDET')
            WHEN COMPART = 'Mammifères' THEN 'Déterminante ZNIEFF - ' || (SELECT CORRESPONDANCE_REGION FROM BDC_MAMMIFERES WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'ZDET')
            WHEN COMPART = 'Poissons' THEN 'Déterminante ZNIEFF - ' || (SELECT CORRESPONDANCE_REGION FROM BDC_POISSONS WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'ZDET')
            WHEN COMPART = 'Reptiles' THEN 'Déterminante ZNIEFF - ' || (SELECT CORRESPONDANCE_REGION FROM BDC_REPTILES WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND CD_TYPE_STATUT = 'ZDET')
            ELSE NULL
        END,
    
    EXO_INV = 
        CASE 
            WHEN COMPART = 'Flore' THEN (SELECT STATUT_EXOTIQUE FROM BDC_FLORE WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION)
            WHEN COMPART = 'Amphibiens' THEN (SELECT CD_TYPE_STATUT FROM BDC_AMPHIBIENS WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND (CD_TYPE_STATUT = 'REGLII' OR CD_TYPE_STATUT = 'REGLLUTTE'))
            WHEN COMPART = 'Entomofaune' THEN (SELECT CD_TYPE_STATUT FROM BDC_ENTOMO WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND (CD_TYPE_STATUT = 'REGLII' OR CD_TYPE_STATUT = 'REGLLUTTE'))
            WHEN COMPART = 'Oiseaux' THEN (SELECT CD_TYPE_STATUT FROM BDC_OISEAUX WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND (CD_TYPE_STATUT = 'REGLII' OR CD_TYPE_STATUT = 'REGLLUTTE'))
            WHEN COMPART = 'Mammifères' THEN (SELECT CD_TYPE_STATUT FROM BDC_MAMMIFERES WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND (CD_TYPE_STATUT = 'REGLII' OR CD_TYPE_STATUT = 'REGLLUTTE'))
            WHEN COMPART = 'Poissons' THEN (SELECT CD_TYPE_STATUT FROM BDC_POISSONS WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND (CD_TYPE_STATUT = 'REGLII' OR CD_TYPE_STATUT = 'REGLLUTTE'))
            WHEN COMPART = 'Reptiles' THEN (SELECT CD_TYPE_STATUT FROM BDC_REPTILES WHERE NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND CORRESPONDANCE_REGION = NEW.REGION AND (CD_TYPE_STATUT = 'REGLII' OR CD_TYPE_STATUT = 'REGLLUTTE'))
            ELSE NULL
        END
    WHERE ESPECES_ATTRIBUTS.ID = NEW.ID;
END;

EDIT1: EXPLAIN QUERY PLAN

To reply to the last message:

EXPLAIN SELECT * FROM ESPECES_PT WHERE ESPECE = 'Ophrys provincialis';

Returns :

EXPLAIN SELECT * FROM ESPECES_ATTRIBUTS WHERE ESPECE = 'Ophrys provincialis';

Returns

EXPLAIN QUERY PLAN SELECT * FROM ESPECES_PT WHERE ESPECE = 'Ophrys provincialis';

Welcome to Code Review! The current question title, which states your concerns about the code, is too general to be useful here. Please edit to the site standard, which is for the title to simply state the task accomplished by the code. Please see How to get the best value out of Code Review: Asking Questions for guidance on writing good question titles. — Toby Speight, Commented Feb 2 at 13:10
Please do not edit the question, especially the code, after an answer has been posted. Changing the question may cause answer invalidation. Everyone needs to be able to see what the reviewer was referring to. What to do after the question has been answered. — pacmaninbw, Commented Feb 2 at 14:26
You can ask a followup question with links back to this question if you want to update the code. — pacmaninbw, Commented Feb 2 at 14:29

J_H · Accepted Answer · 2024-02-02 04:03:38Z

I've always heard that[SQL] is a very efficient way of searching databases.

Yes. It can be. When used carefully.

try and improve the execution speed of my triggers

To be clear, procedural logic like triggers is outside of Codd & Date's relational calculus. Triggers can add business value, when used carefully. It sounds like they're more hindrance than help, here.

triggers are executed to search in several databases

That sounds like trouble. Leaving ACID transactions aside, there's two big ideas any established RDBMS will bring to the table:

indexes
query planner

The trigger logic you describe prevents the backend query planner from seeing what is needed and choosing appropriate access paths.

Consider letting your app worry about that logic, one level up. Likely it will result in exposing greater statistical knowledge to the backend, and better query plans. If there's a "relational integrity" argument to be made, fine, but worry about that after your queries are doing sensible things. It sounds like you're affecting only a "few" rows, so queries should complete "quickly", but due to levels of indirection that is not currently happening.

the value of ESPECE will be searched in different databases

That sounds like trouble. Ideally a JOIN relation would make the desired values available within a single database.

I see lengthy CASE / WHEN logic that would naturally be expressed as a JOIN relation.

    WHEN (SELECT COUNT(*) FROM TAXREF_AMPHIBIENS WHERE ...) = 0 THEN 'Absent'

You really want to look at EXPLAIN SELECT ... query plans, to see if the backend is doing what you think it should be doing. That is, simple queries should work out to simple plans rather than tablescans, or worse, to probes of external databases.

Here, it's not obvious to me that COUNT(*) ... = 0 will be optimized the same way the more natural WHERE NOT EXISTS query would be.

I forgot to specify that I'm a beginner 😅 I've been searching to improve my code according to your advises. But I'm not sure if you recommend me to adjust my code, or rather rethink the way my project works. I've made some changes to my code. But I can't find a way to make a JOIN relation. I think I can do it when I need to search one database, but the searched database differs according to the content of COMPART. — Giene, Commented Feb 2 at 6:27
Maybe, instead of using 7 databases of 100k rows each, I should use one with 700k rows. — Giene, Commented Feb 2 at 7:06
I will create a table with all the tables combined (named BDC_STATS). Then I will create an index on NOM_VALIDE_COMPLET, CORRESPONDANCE_REGION and CD_TYPE_STATUT. Then I will try something like this: (see edit) — Giene, Commented Feb 2 at 8:27
Yes, putting it all in a single database sounds like an improvement, whether it’s one giant table, or preferably, several JOINed tables. An index can help. The most important thing to do is to pay attention to EXPLAIN SELECT … query plan output, and adjust your WHERE clause and/or your CREATE INDEX so sensible things happen. Typically we can get away with touching < 1% of rows, leaving most data slumbering dormant on disk. Some queries will retrieve many rows and then just ignore them; we strive to avoid that. — J_H, Commented Feb 2 at 13:07
Show us the EXPLAIN SELECT ... query plan. Also, "fast" triggers are fine, but when you've managed to develop "slow" triggers, you're usually better off moving their complexity up into the app layer, where you can study the details and record timing measurements. — J_H, Commented Feb 5 at 3:20

Giene · Accepted Answer · 2024-02-07 07:06:48Z

I have grouped my databases. Now I have BDC_STAT and BD_TAXREF to search into. I like this way because I don't have to specify the value in the field "COMPART". It is more practical.

But in terms of performances, nothing changed.

Here are the new version of my triggers:

When I add features on table ESPECES_PT

CREATE TRIGGER A_insert_esp 
AFTER INSERT ON ESPECES_PT
BEGIN
    UPDATE ESPECES_PT
    SET "REGION" = COALESCE(ESPECES_PT.REGION, (SELECT "REGION" FROM ZONE_ETUDE_POLY), '[Voir Table ESPECES_ATTRIBUTS]'),
        "DATA_PROP" = (SELECT "Company" FROM C_c)
    WHERE pkuid = NEW.pkuid;

    INSERT INTO ESPECES_ATTRIBUTS (ESPECE, Visibilite, ENJEU_SPE, IMPORT_ZE, ENJEU_ZE, REGION)
    SELECT DISTINCT
        NEW.ESPECE,
        NEW.Visibilite,
        coalesce((SELECT TYPER FROM TABLE_VAL WHERE CORR = 'ENJEU' AND (NEW.ENJEU_SPE LIKE '%' || VALEURC || '%' OR NEW.ENJEU_SPE LIKE '%' || VALEURT || '%')),NULL),
        coalesce((SELECT TYPER FROM TABLE_VAL WHERE CORR = 'ENJEU' AND (NEW.IMPORT_ZE LIKE '%' || VALEURC || '%' OR NEW.IMPORT_ZE LIKE '%' || VALEURT || '%')),NULL),
        coalesce((SELECT TYPER FROM TABLE_VAL WHERE CORR = 'ENJEU' AND (NEW.ENJEU_ZE LIKE '%' || VALEURC || '%' OR NEW.ENJEU_ZE LIKE '%' || VALEURT || '%')),NULL),
        coalesce(NULLIF(ESPECES_PT.REGION, '[Voir Table ESPECES_ATTRIBUTS]'), NULL)

    FROM ESPECES_PT
    WHERE ESPECES_PT.pkuid = NEW.pkuid 
        AND NEW.ESPECE IS NOT NULL 
        AND NEW.ESPECE NOT IN (SELECT ESPECE FROM ESPECES_ATTRIBUTS);

    UPDATE ESPECES_ATTRIBUTS
    SET NOM_SIMPLE = (
        WITH RECURSIVE split(value, str) AS (
            SELECT null, NEW.ESPECE || ' ' FROM ESPECES_ATTRIBUTS WHERE ESPECES_ATTRIBUTS.rowid = last_insert_rowid()
            UNION ALL
            SELECT substr(str, 0, instr(str, ' ')), substr(str, instr(str, ' ')+1) FROM split WHERE str != ''
        )
        SELECT 
            CASE 
                WHEN NEW.ESPECE LIKE '%var.%' OR NEW.ESPECE LIKE '%subsp.%' THEN
                    COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1), '') 
                    || COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 1), '' ) 
                    || COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 2), '') 
                    || TRIM(COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 3), ''))
                ELSE
                    COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1), '') 
                    || TRIM(COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 1), '' ))
            END
    )
    WHERE ESPECES_ATTRIBUTS.rowid = last_insert_rowid();

    UPDATE ESPECES_ATTRIBUTS
    SET NOM_SCIENT_VALIDE = 
        COALESCE(
            (SELECT "NOM_VALIDE" FROM BD_TAXREF WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SIMPLE), 
                    CASE 
                        WHEN (SELECT COUNT(*) FROM BD_TAXREF WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE LIMIT 2) = 1 THEN (SELECT "NOM_VALIDE" FROM BD_TAXREF WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE)
                        WHEN NOT EXISTS (SELECT 1 FROM BD_TAXREF WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) THEN 'Absent'
                        ELSE
                            '(Synonymes)'
                    END)
    WHERE ESPECES_ATTRIBUTS.rowid = last_insert_rowid();

    UPDATE ESPECES_ATTRIBUTS
        SET  "CHOIX_ESPECE" = 'Voir liste'
    WHERE ESPECES_ATTRIBUTS.rowid = last_insert_rowid() AND NOM_SCIENT_VALIDE = '(Synonymes)';

    INSERT INTO SYNONYMES (ID, ESPECE, NOM_SCIENT_VALIDE)
    SELECT DISTINCT 
        EA.ID,
        EA.ESPECE,
        T.NOM_VALIDE
    FROM ESPECES_ATTRIBUTS EA
    JOIN BD_TAXREF T ON T.LB_NOM = EA.NOM_SIMPLE
    WHERE EA.ID = last_insert_rowid() 
        AND EA.NOM_SCIENT_VALIDE = '(Synonymes)' 
        AND EA.COMPART IS NOT NULL 
        AND NOT EXISTS (
            SELECT 1 
            FROM SYNONYMES S 
            WHERE S.ESPECE = EA.ESPECE 
                AND S.NOM_SCIENT_VALIDE = T.NOM_VALIDE
        )
        AND (EXISTS (SELECT 1 FROM BD_TAXREF WHERE LB_NOM = EA.NOM_SIMPLE));
END

The code to update "ESPECE" into "NOM_SCIENT_VALIDE" (in table ESPECES_ATTRIBUTS)

    CREATE TRIGGER B_update_nom_scient
    AFTER UPDATE OF ESPECE ON ESPECES_ATTRIBUTS 
    FOR EACH ROW
    BEGIN
        UPDATE ESPECES_ATTRIBUTS
        SET NOM_SIMPLE = (
            WITH RECURSIVE split(value, str) AS (
                SELECT null, NEW.ESPECE || ' ' 
                FROM ESPECES_ATTRIBUTS
                WHERE ID = NEW.ID
                UNION ALL
                SELECT substr(str, 0, instr(str, ' ')), substr(str, instr(str, ' ')+1)
                FROM split
                WHERE str != ''
            )
            SELECT
                CASE
                    WHEN NEW.ESPECE LIKE '%var.%' OR NEW.ESPECE LIKE '%subsp.%' THEN
                         COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1), '') ||
                         COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 1), '' ) ||
                         COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 2), '') ||
                         TRIM(COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 3), ''))
                    ELSE
                         COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1), '') ||
                         TRIM(COALESCE((SELECT value || ' ' FROM split WHERE value IS NOT NULL LIMIT 1 OFFSET 1), '' )) 
                END
        )
        WHERE ID = NEW.ID;
    
        UPDATE ESPECES_ATTRIBUTS
            SET NOM_SCIENT_VALIDE = 
                COALESCE(
                    (SELECT "NOM_VALIDE" FROM BD_TAXREF WHERE "NOM_VALIDE_SIMPLE" = ESPECES_ATTRIBUTS.NOM_SIMPLE), 
                        CASE 
                            WHEN (SELECT COUNT(*) FROM BD_TAXREF WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE LIMIT 2) = 1 THEN (SELECT "NOM_VALIDE" FROM BD_TAXREF WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE)
                            WHEN NOT EXISTS (SELECT 1 FROM BD_TAXREF WHERE "LB_NOM" = ESPECES_ATTRIBUTS.NOM_SIMPLE) THEN 'Absent'
                            ELSE
                                '(Synonymes)'
                        END)
        WHERE ID = NEW.ID;
    END;


The code for searching the databases

CREATE TRIGGER B_update_spe_stat
AFTER UPDATE OF NOM_SCIENT_VALIDE ON ESPECES_ATTRIBUTS 
FOR EACH ROW
BEGIN

    UPDATE ESPECES_ATTRIBUTS
    SET "CHOIX_ESPECE" = 'Voir liste'
    WHERE ID = NEW.ID AND NOM_SCIENT_VALIDE = '(Synonymes)';

    UPDATE ESPECES_ATTRIBUTS
    SET 
        (NOM_VERN, CHOROLOGIE, FLORAISON, HABIT_S, CARAC_ECO, INDIC_PHYTO, TYPE_BIOLOGIQUE, FORMATION_VEGETALE, ENJEU_paca, ZH, MESSICOLE) = 
            (SELECT
                "NOM_VERN",
                "CHOROLOGIE",
                "floraison",
                "HABITAT SIMPLE",
                "CARACTERISATION_ECOLOGIQUE_(HABITAT_OPTIMAL)",
                "INDICATION_PHYTOSOCIOLOGIQUE_CARACTERISTIQUE",
                "TYPE_BIOLOGIQUE",
                "FORMATION_VEGETALE",
                "ENJEU PACA",
                "Statut ZH",
                "Statut messicole"
            FROM BD_TAXREF
            WHERE "NOM_VALIDE" = NEW.NOM_SCIENT_VALIDE)
    WHERE ID = NEW.ID;

    INSERT INTO SYNONYMES (ID, ESPECE, NOM_SCIENT_VALIDE)
    SELECT DISTINCT
        EA.ID, EA.ESPECE, T.NOM_VALIDE
    FROM
        ESPECES_ATTRIBUTS EA
    JOIN
        BD_TAXREF T ON T.LB_NOM = EA.NOM_SIMPLE
    WHERE
        EA.ID = NEW.ID
        AND EA.NOM_SCIENT_VALIDE = '(Synonymes)'
        AND NOT EXISTS (SELECT 1 FROM SYNONYMES S WHERE S.ESPECE = EA.ESPECE AND S.NOM_SCIENT_VALIDE = T.NOM_VALIDE)
        AND (EXISTS (SELECT 1 FROM BD_TAXREF WHERE LB_NOM = EA.NOM_SIMPLE));

    UPDATE ESPECES_ATTRIBUTS
    SET 
        PN = (SELECT BS.CODE_STATUT FROM BDC_STAT BS WHERE BS.NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND BS.CD_TYPE_STATUT = 'PN'),
        PR = (SELECT BS.CODE_STATUT FROM BDC_STAT BS WHERE BS.NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND BS.CORRESPONDANCE_REGION = NEW.REGION AND BS.CD_TYPE_STATUT = 'PR'),
        ListeRN = (SELECT BS.CODE_STATUT FROM BDC_STAT BS WHERE BS.NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND BS.CORRESPONDANCE_REGION = NEW.REGION AND BS.CD_TYPE_STATUT = 'LRN'),
        ListeRR = (SELECT BS.CODE_STATUT FROM BDC_STAT BS WHERE BS.NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND BS.CORRESPONDANCE_REGION = NEW.REGION AND BS.CD_TYPE_STATUT = 'LRR'),
        ZNIEFF = Coalesce('Déterminante ZNIEFF - ' || (SELECT BS.CORRESPONDANCE_REGION FROM BDC_STAT BS WHERE BS.NOM_VALIDE_COMPLET = NEW.NOM_SCIENT_VALIDE AND BS.CORRESPONDANCE_REGION = NEW.REGION AND BS.CD_TYPE_STATUT = 'ZDET'),NULL),
        EXO_INV = (SELECT BS.STATUT_EXOTIQUE FROM BDC_STAT BS WHERE BS.CORRESPONDANCE_REGION = NEW.REGION AND (BS.CD_TYPE_STATUT = 'REGLII' OR BS.CD_TYPE_STATUT = 'REGLLUTTE'))
    WHERE ID = NEW.ID;
    
END;

I reduce the number of CASE, used Coalesce more often, WHERE NOT EXISTS instead of counting and added a limit of 2 when I need to count (to check if more than 1 value already exists in the database).

These columns are indexed: ESPECE on the table : ESPECES_ATTRIBUTS and CORRESPONDANCE_REGION, CD_TYPE_STATUT, NOM_VALIDE_COMPLET on the table BDC_STAT

Despite all of this, I cannot improve the efficiency of my queries.

When I read the answer I think there are things that I'm missing (i. e. the query plans) but nothing came out from my attempts to work with EXPLAIN SELECT). For now...

Show us the EXPLAIN SELECT ... query plans, please. The usual explanation behind "slow query" is that we retrieved way more rows than necessary to accomplish the business goal. Could be from a tablescan to locate a single row, or from retrieving many rows only to ignore most of them up at the app level. Triggers are trouble. Don't worry much about UPDATE transactions at this point, as they are harder to diagnose due to their side effects. Just worry about SELECT results. Having retrieved a primary key ID, we can always quickly UPDATE that particular row later. — J_H, Commented Feb 5 at 5:22
I haven't seen this message a while ago : "Show us the EXPLAIN SELECT ... query plan. Also, "fast" triggers are fine, but when you've managed to develop "slow" triggers, you're usually better off moving their complexity up into the app layer, where you can study the details and record timing measurements." As I'm working under QGIS, with SpatiaLite layers, I don't know if there is a way to move my queries "up into the app layer". I will look into this. — Giene, Commented Feb 5 at 6:27
For now, I converted a part of my code into default values. The triggers will no longer on string parsing, as it is done before triggers' start. (It didn't improve the overall efficiency of my queries) I think I will have to modify my database again. Currently, there are multiple rows for different information. I intend to transform these rows into columns to retrieve the entire dataset with just one (or two) queries, rather than searching through separate rows for each field to update. — Giene, Commented Feb 7 at 7:05
Well, I've looked at the issue from every angle, I can't find any solution. Even with a smaller database and after improving as much as I can my code, it is still excessively slow... — Giene, Commented Feb 15 at 8:21

Giene · Accepted Answer · 2024-03-26 01:42:01Z

It took me some time because I had to reassess my approach and revisit my project from the ground up. Additionally, I had to overhaul the databases, which involved revising all the code for database creation

However, I now have a more streamlined database and have reorganized my triggers, allowing me to achieve the same results in around 5 seconds (and the code now accomplishes even more than before). I've identified further potential improvements for the future, but it is already quite good.

Stack Exchange Network

Improving efficiency of database searches under SQLite (SpatiaLite - QGIS)

3 Answers 3

Your Answer

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

Hot Network Questions

Improving efficiency of database searches under SQLite (SpatiaLite - QGIS)

3 Answers 3

Your Answer

Sign up or log in

Post as a guest

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

Related

Hot Network Questions

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