Discussion :

[mister3957]

Bonjour,

J'ai une table qui référence des codes iso et une autre table qui possède des colonnes selon le code iso.

Par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
CREATE TABLE t_iso(
   id BIGSERIAL,
   code CHAR(2)
);
INSERT INTO t_iso (code) VALUES ('fr');
INSERT INTO t_iso (code) VALUES ('en');
INSERT INTO t_iso (code) VALUES ('be');

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
CREATE TABLE t_ref (
   id BIGSERIAL,
   name CHARACTER VARYING(255),
   fr INTERGER,
   en INTERGER,
   be INTERGER
);
INSERT INTO t_ref (name, fr, en, be) VALUES ('test1', 1, 2, null);
INSERT INTO t_ref (name, fr, en, be) VALUES ('test2', null, 3, 4);
INSERT INTO t_ref (name, fr, en, be) VALUES ('test1', 5, 6, 7);

Je n'ai pas le choix sur la structure de ces deux tables, ça m'est imposé.

Je dois faire une table reliant le t_ref.name avec les t_iso lorsque la valeur n'est pas null :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
CREATE TABLE t_ref_2(
   name CHARACTER VARYING(255),
   id_iso BIGINT REFERENCES t_iso(id),
   val INTERGER
);

Peut-être naivement, j'ai fait une procédure stockée avec un curseur sur la table des iso et du dynamic sql pour gérer le nom des colonnes, comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
CREATE FUNCTION fct_fill() RETURNS VOID AS $PROC$
	DECLARE
		r t_iso%rowtype;
	BEGIN
		FOR r IN SELECT * FROM t_iso LOOP
			EXECUTE '
				INSERT INTO t_ref_2(name, id_iso, val)
					SELECT
						name
						r.id,
						' || r.code || '
					FROM
						t_ref ref
					WHERE
						ref.' || r.code || ' IS NOT NULL
			';
		END LOOP;
	END;
$PROC$ language 'plpgsql';
SELECT fct_fill();

(C'est approximatif, je n'ai pas le code sous les yeux, mais voilà la démarche)

Le soucis c'est que c'est long à s'exécuter, le disque ne gratte pas et le process est à 100%

Dans mon contexte, il y a 649 codes iso différents (sur 2 et 3 caractère) et plus de 400 milles lignes dans t_ref.

En analysant un peu, j'ai fait un "RAISE NOTICE" dans la boucle du curseur et au début ça enchaîne bien (à peu près une 20ène "d'EXECUTE" par seconde) puis au fur et à mesure ça ralenti jusqu'à demander plus de 10 secondes sur les 100 dernières boucles.

En soit c'est pas illogique quant au fait que le serveur doit conserver les résultats au fur et à mesure que les requêtes s'exécutent au sein de cette transaction implicite avant de valider l'ensemble en cas de succès ou d'en faire un rollback en cas d'échec et les données s'accumulent d'où le ralentissement.

Mais il y a sûrement moyen d'optimiser ça non ?

- Soit sur une espèce d'autocommit activée spécialement pour l'occaz ?
- Soit via une seule requête sans passer par une fonction / curseur / EXECUTE ?

Merci pour vos réponses,

A bientôt

[al1_24]

Pourquoi se fatiguer à faire une procédure et une boucle quand une simple requête peut faire tout le travail ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
INSERT INTO t_ref_2
    (   name
    ,   id_iso
    ,   val
    )
SELECT  src.name
    ,   iso.id  AS id_iso
    ,   src.val
FROM    (   SELECT  ref.name
                ,   'fr'    AS  code
                ,   ref.fr  AS  val
            FROM    t_ref   AS ref
            WHERE   ref.fr  IS NOT NULL
        UNION ALL
            SELECT  ref.name
                ,   'en'    AS  code
                ,   ref.en  AS  val
            FROM    t_ref   AS ref
            WHERE   ref.en  IS NOT NULL
        UNION ALL
            SELECT  ref.name
                ,   'be'    AS  code
                ,   ref.be  AS  val
            FROM    t_ref   AS ref
            WHERE   ref.be  IS NOT NULL
        )   src
    INNER JOIN
        t_iso   iso
        ON  src.code    = iso.code
;

Et encore, cela pourrait être encore plus facile si ton SGBD prend en charge l'instruction UNPIVOT.
Mais comme tu ne l'as pas précisé...

[mister3957]

Oups, désolé, je suis en 9.4.

Tu penses que je peux faire des jointures sur 649 tables (nombre de code iso) ?

Je me renseigne sur UNPIVOT, je connais pas

Merci

[estofilo]

Ca produit combien de lignes dans t_ref2 ?

Au max ça doit faire 649 x " 400 milles lignes dans t_ref", soit dans les 260 millions de lignes.
Il faudrait connaitre la proportion de NULL dans les colonnes iso pour avoir une estimation réaliste.

Quoiqu'il en soit, une optimisation possible est d'attendre la fin du remplissage de la table avant de créer la contrainte 'intégrité référentielle:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

id_iso BIGINT REFERENCES t_iso(id),

Sinon elle est testée à chaque insert pour rien.

[mister3957]

Ca produit combien de lignes dans t_ref2 ?

Au max ça doit faire 649 x " 400 milles lignes dans t_ref", soit dans les 260 millions de lignes.
Il faudrait connaitre la proportion de NULL dans les colonnes iso pour avoir une estimation réaliste.

Quoiqu'il en soit, une optimisation possible est d'attendre la fin du remplissage de la table avant de créer la contrainte 'intégrité référentielle:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

id_iso BIGINT REFERENCES t_iso(id),

Sinon elle est testée à chaque insert pour rien.

Bonjour,

Merci pour ta réponse.

Je pourrai te dire ce chiffre ce soir, mais dans t_ref il y a 649 colonnes "iso" qui contiennent toutes au moins une colonne avec une valeur dedans, et 1 à 2% qui ont entre 2 et 20 valeurs.

Je vais retirer les contraintes pour les rajouter à la fin du script.

A bientôt