Forums PostgreSQL.fr

mailgifson

Membre

Comment optimiser/réaliser ce traitement ?

Bonjour,

Je viens vers vous car j'ai un gros problème de performance sur un traitement, réalisé actuellement dans une fonction PLPGSQL, et je ne vois pas comment optimiser ce traitement (ou même comment le réaliser autrement).

C'est un sujet un peu compliqué alors je vais essayer de le présenter au mieux.

Ce que je souhaite :

Je possède une table "echanges" contenant des échanges effectués avec des contacts.
Parmi les informations disponibles dans cette table, il y a des informations sur les contacts avec qui l'échange s'effectue (nom, prénom, adresse, téléphone, adresse e-mail, ...).
Ces informations renseignées dépendent du type d'échange (des fois il y a que l'adresse, des fois que le téléphone, ...). Les seules données toujours présentes sont le nom et le prénom.

Ces contacts sont regroupés dans des groupes.

L'objectif est d'avoir pour chacun de ces groupes une notion de "fiche contact", qui regroupe les informations de ces contacts pour en faire une table référentiel.
Ainsi si j'ai l'information de l'adresse d'un contact dans un échange et que j'ai l'information du téléphone de ce même contact dans un autre échange, je devrais avoir une fiche contact contenant le nom, le prénom, l'adresse et le téléphone de renseignés.

Vous aurez compris qu'il n'y a pas d'identifiant me permettant de trouver les échanges communs à un même contact.

Pour réaliser cela, j'ai un algorithme fonctionnant avec un système de poids, qui me permet de déterminer s'il s'agit d'un contact déjà connu.
Cet algorithme est appliqué sur chacune des lignes de mes échanges et détermine si le contact possède déjà une fiche ou non.
- Si l'algorithme trouve une équivalence, la fiche contact est mise à jour avec les nouvelles informations.
- Si l'algorithme ne trouve pas d'équivalence, une nouvelle fiche contact est créée.

Au niveau de la volumétrie, elle est plutôt importante puisque j'ai 320.000 échanges par mois.

J'ai essayé de réaliser cet algorithme via une fonction postgresql (PLPGSQL).

Premier test

Le premier test réalisé est une fonction contenant deux boucles for imbriquées.
La première traite ligne à ligne mes échanges.
La deuxième va chercher les équivalences possibles dans mes fiches contacts et applique l'algorithme.

Cela fonctionne avec quelques lignes mais ça tourne sans fin avec un mois de données.

Deuxième test

J'ai modifié la première fonction pour réaliser un deuxième test.
Ce test est une fonction réceptionnant un identifiant de ligne de ma table des échanges.
La fonction va récupérer les informations de ma ligne d'échange.
Va récupérer les équivalences possibles dans les fiches contacts et boucle pour appliquer l'algorithme.

J’espérais avec cette méthode que postgresql réalise des commits intermédiaires (à chaque appel de la fonction) mais en fait ça ne fonctionne pas.

Même résultat avec ce deuxième test : Cela fonctionne avec quelques lignes mais ça tourne sans fin avec un mois de données.

Mes problématiques :

- Lorsque j’exécute sur une très faible volumétrie (quelques lignes), cela fonctionne très bien mais quand je passe sur une volumétrie plus importante (un moins de données), ça tourne des heures (je n'ai jamais réussi à arriver au bout même après plusieurs heures d'exécution).

- Je n'ai aucune visibilité sur l'avancement de la fonction, c'est une sorte de boite noire. Est-ce qu'il y a des moyens d'avoir des informations sur l'avancement du traitement (nombre de lignes traitées, ...) ?

- Les fonctions ne permettent pas de réaliser des commit intermédiaires. Je pensais qu'en effectuant une procédure appelant une fonction (deuxième test) cela me permettrait de faire des commits intermédiaires, mais non.

- La difficulté est que ma table "fiche contact" peut potentiellement changer après chaque traitement d'une ligne de ma table échange. Je ne vois donc pas comment réaliser cela sans passer par une fonction qui va scruter mes fiches pour chaque ligne d'échange.

Dernière modification par mailgifson (16/10/2020 17:08:26)

gleu

Administrateur

Re : Comment optimiser/réaliser ce traitement ?

- Lorsque j’exécute sur une très faible volumétrie (quelques lignes), cela fonctionne très bien mais quand je passe sur une volumétrie plus importante (un moins de données), ça tourne des heures (je n'ai jamais réussi à arriver au bout même après plusieurs heures d'exécution).

C'est impossible de vous aider ainsi. Il faudrait déterminer ce qui est lent dans la fonction (en supposant qu'il n'y en a qu'une). Pour cela, soit vous utilisez un profiler, soit vous ajoutez des instructions style RAISE LOG pour savoir où il en est.

- Je n'ai aucune visibilité sur l'avancement de la fonction, c'est une sorte de boite noire. Est-ce qu'il y a des moyens d'avoir des informations sur l'avancement du traitement (nombre de lignes traitées, ...) ?

Hé bien, vu que la fonction est un code que vous avez écrit, seul vous pouvez ajouter un code à cette fonction pour avoir ces informations. Le plus simple, comme dit précédemment, serait d'ajouter des instructions RAISE LOG avec un message indiquant la progression.

- Les fonctions ne permettent pas de réaliser des commit intermédiaires. Je pensais qu'en effectuant une procédure appelant une fonction (deuxième test) cela me permettrait de faire des commits intermédiaires, mais non.

Vous ne pouvez pas faire de COMMIT intermédiaires dans une fonction, quelque soit la façon dont elle est appelée.

---

Guillaume.

rjuju

Administrateur

Re : Comment optimiser/réaliser ce traitement ?

Vous ne pouvez pas faire de COMMIT intermédiaires dans une fonction, quelque soit la façon dont elle est appelée.

Il serait peut-être possible d'utiliser une procédure plutôt qu'une fonction, auquel cas un commit intermédiaire serait possible.  Cependant, cela ne règlerait pas le problème de base.

J'imagine que la raison pour laquelle le traitement pour un mois de données est long est parce ce que vous faites un traitement impératif ligne par ligne, et non pas un traitement "ensembliste".  Réécrivez votre algorithme pour fonctionner avec un ensemble de données et non une seule ligne et vous devriez grandement réduire le temps d'exécution.

Je suis aussi assez curieux de la façon dont vous gérez les homonymes et/ou les changement de numéros / adresse, mais c'est un autre problème.

---

Julien.
https://rjuju.github.io/

mailgifson

Membre

Re : Comment optimiser/réaliser ce traitement ?

C'est impossible de vous aider ainsi. Il faudrait déterminer ce qui est lent dans la fonction (en supposant qu'il n'y en a qu'une). Pour cela, soit vous utilisez un profiler, soit vous ajoutez des instructions style RAISE LOG pour savoir où il en est.

Je vais essayer de trouver les parties posant problème avec des RAISE LOG.
J’avais essayé avec RAISE NOTICE mais je n’avais rien qui s’affichait.

Il serait peut-être possible d'utiliser une procédure plutôt qu'une fonction, auquel cas un commit intermédiaire serait possible. Cependant, cela ne règlerait pas le problème de base.

Je ne savais pas que les procédures permettaient les commit.

J'imagine que la raison pour laquelle le traitement pour un mois de données est long est parce ce que vous faites un traitement impératif ligne par ligne, et non pas un traitement "ensembliste".  Réécrivez votre algorithme pour fonctionner avec un ensemble de données et non une seule ligne et vous devriez grandement réduire le temps d'exécution.

Je ne vois pas comment je pourrais faire ce traitement autrement que ligne à ligne, puisque j’ai besoin par chaque ligne de calculer des poids sur les lignes pouvant être équivalente.

Comme je dois comparer la table des échanges avec les fiches contacts et que cette dernière évolue en fonction de l’analyse des échanges, ça me parait compliqué de faire autrement.

Je suis aussi assez curieux de la façon dont vous gérez les homonymes et/ou les changement de numéros / adresse, mais c'est un autre problème.

J’ai en fait suffisamment d’informations pour distinguer les homonymes et les changements de téléphone. Par contre je ne détecte pas les changements d’adresse, cela construit une nouvelle fiche quand le cas de figure se produit.

rjuju

Administrateur

Re : Comment optimiser/réaliser ce traitement ?

Je ne vois pas comment je pourrais faire ce traitement autrement que ligne à ligne, puisque j’ai besoin par chaque ligne de calculer des poids sur les lignes pouvant être équivalente.

Comme je dois comparer la table des échanges avec les fiches contacts et que cette dernière évolue en fonction de l’analyse des échanges, ça me parait compliqué de faire autrement.

Peut-être est-il possible de traiter en une fois tous les contacts clairement identifiables, et de continuer à boucler après chaque série de mise à jour ?

---

Julien.
https://rjuju.github.io/