Fonction, cursor, dynamique sql & perfs
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:
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:
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:
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:
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
