Project

General

Profile

Anomalie #13162

Recherche des doublons non affichables (temps d'affichage trop long)

Added by Ludovic ARAUJO over 2 years ago. Updated 11 months ago.

Status:
Développé / Analysé (S)
Priority:
1-Majeur
Target version:
Start date:
02/07/2020
Due date:
Tags Courrier:

Description

Optimisation du module de recherche de doublons
ex : limité la recherche à 30 éléments

History

#2 Updated by Ludovic ARAUJO over 2 years ago

  • Priority changed from 2-Sérieux to 1-Majeur

#3 Updated by Support Maarch over 2 years ago

  • Status changed from A qualifier to A traiter

#4 Updated by Emmanuel DILLARD over 2 years ago

  • Tracker changed from Anomalie to Ergonomie
  • Subject changed from Recherche des doublons non affichables(temps d'affichage trop long)(inutilisables par le client) to Recherche des doublons non affichables (temps d'affichage trop long)
  • Status changed from A traiter to Etude planifiée

#5 Updated by Emmanuel DILLARD over 2 years ago

  • Status changed from Etude planifiée to 17
  • Assignee set to Ludovic ARAUJO

Indiquer les temps /volumétrie de la base.

L'optimisation à été réalisée dans la 19.04 STD.

#6 Updated by Ludovic ARAUJO about 2 years ago

Volume de données : Plus de 8k de doublons.
Temps : plus de 13 minutes.

Arrêt de l’exécution par php.
Cause : Mémoire insuffisante.

php doit stocker plus de 4Go sur le serveur pour le faire passer au client par la suite.

Après modification et amélioration, je vous propose de remplacer les requêtes suivantes :

SELECT distinct contacts_v2.contact_id,
                      lower(translate(contacts_v2.lastname || ' ' || contacts_v2.firstname,
                                      'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-',
                                      'aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr '))         as lastname_firstname,
                      society,
                      society_short,
                      is_corporate_person,
                      contacts_v2.lastname,
                      contacts_v2.firstname,
                      contacts_v2.title,
                      (select count(*)
                       from contact_addresses as ca2
                       where ca2.contact_id = contacts_v2.contact_id)                                               as nb_addresses,
                      (select id from contact_addresses where contact_addresses.contact_id = contacts_v2.contact_id LIMIT 1),
                      (select address_num from contact_addresses where contact_addresses.contact_id = contacts_v2.contact_id LIMIT 1),
                      (select address_street from contact_addresses where contact_addresses.contact_id = contacts_v2.contact_id LIMIT 1),
                      (select address_postal_code from contact_addresses where contact_addresses.contact_id = contacts_v2.contact_id LIMIT 1),
                      (select address_town from contact_addresses where contact_addresses.contact_id = contacts_v2.contact_id LIMIT 1),
                      (select email from contact_addresses where contact_addresses.contact_id = contacts_v2.contact_id LIMIT 1)
      from contacts_v2
      WHERE lower(translate(contacts_v2.lastname || ' ' || contacts_v2.firstname,
                            'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-',
                            'aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr ')) in (
                SELECT lower(translate(contacts_v2.lastname || ' ' || contacts_v2.firstname,
                                       'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-',
                                       'aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr ')) as lastname_firstname
                FROM contacts_v2
                GROUP BY lastname_firstname
                HAVING Count(lower(translate(contacts_v2.lastname || ' ' || contacts_v2.firstname,
                                             'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-',
                                             'aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr '))) >
                       1
                   and lower(translate(contacts_v2.lastname || ' ' || contacts_v2.firstname,
                                       'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-',
                                       'aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr ')) <>
                       ' ')
      order by lower(translate(contacts_v2.lastname || ' ' || contacts_v2.firstname,
                               'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-',
                               'aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr ')),
               contacts_v2.contact_id

par

WITH c as
(SELECT distinct contacts_v2.contact_id,
                  (lower(translate(contacts_v2.lastname || ' ' || contacts_v2.firstname,
                                  'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-',
                                  'aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr '))) as lastname_firstname,
                  society,
                  society_short,
                  is_corporate_person,
                  contacts_v2.lastname,
                  contacts_v2.firstname,
                  contacts_v2.title,
                  (select count(*) from contact_addresses as ca2 where ca2.contact_id = contacts_v2.contact_id) as nb_addresses,
                  ca.id,
                  ca.address_num,
                  ca.address_street,
                  ca.address_postal_code,
                  ca.address_town,
                  ca.email
  FROM contacts_v2
  left join contact_addresses ca on ca.contact_id = contacts_v2.contact_id)
  SELECT distinct c.contact_id,
                  c.lastname_firstname,
                  c.society,
                  c.society_short,
                  c.is_corporate_person,
                  c.lastname,
                  c.firstname,
                  c.title,
                  c.nb_addresses,
                  c.id,
                  c.address_num,
                  c.address_street,
                  c.address_postal_code,
                  c.address_town,
                  c.email
  FROM c
  WHERE c.lastname_firstname in (
                            SELECT c.lastname_firstname as lf
                            FROM c
                            GROUP BY c.lastname_firstname
                            HAVING Count(c.lastname_firstname) > 1
                            and c.lastname_firstname <> ' '
                        )
  order by c.lastname_firstname, c.contact_id LIMIT 100;

Ainsi que :

SELECT contact_id,
       society,
       lower(translate(society, 'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-',
                       'aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr ')) as society_comp
from contacts_v2
WHERE is_corporate_person = 'Y'
  AND
      lower(translate(society, 'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-',
                      'aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr ')) in (
          SELECT lower(translate(society, 'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-',
                                 'aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr '))
          FROM contacts_v2
          where is_corporate_person = 'Y'
          GROUP BY lower(translate(society, 'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-',
                                   'aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr '))
          HAVING Count(lower(translate(society, 'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-',
                                       'aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr '))) > 1
             and lower(translate(society, 'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-',
                                 'aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr ')) <> ''
      )
order by lower(society), contact_id

par :

WITH s AS 
(SELECT contact_id,
       society,
       lower(translate(society, 'ÀÁÂÃÄÅÆÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖØÙÚÛÜÝÞßàáâãäåæçèéêëìíîïðñòóôõöøùúûýýþÿŔŕ-',
                       'aaaaaaaceeeeiiiidnoooooouuuuybsaaaaaaaceeeeiiiidnoooooouuuyybyRr ')) AS society_comp
FROM contacts_v2
WHERE is_corporate_person = 'Y')
SELECT contact_id,
       society,
       society_comp
FROM s WHERE society_comp IN (
          SELECT society_comp
          FROM s
          GROUP BY society_comp
          HAVING Count(society_comp) > 1
             AND society_comp <> ''
      )
ORDER BY lower(society), contact_id LIMIT 100;

Les effet sont les suivants :

Mémoire max allouée pour la requête réduite et limité.
Affichage du résultat en navigateur possible sans avoir 4Go ou plus de mémoire sur le poste.
Demande un rafraîchissement de la page après fusion de tous les doublons présents.

#7 Updated by Emmanuel DILLARD about 2 years ago

  • Tracker changed from Ergonomie to Anomalie
  • Status changed from 17 to Prêt à développer

#8 Updated by Emmanuel DILLARD about 2 years ago

  • Assignee changed from Ludovic ARAUJO to EDI PO

#9 Updated by Emmanuel DILLARD about 2 years ago

  • Project changed from Backlog to CURRENT SPRINT
  • Status changed from Prêt à développer to En cours de dev (S)

#11 Updated by Florian AZIZIAN about 2 years ago

  • Assignee deleted (EDI PO)

#12 Updated by Guillaume HEURTIER about 2 years ago

  • Assignee set to Guillaume HEURTIER

#13 Updated by Guillaume HEURTIER about 2 years ago

  • Status changed from En cours de dev (S) to Développé / Analysé (S)

#14 Updated by Emmanuel DILLARD about 2 years ago

  • Project changed from CURRENT SPRINT to Backlog

#15 Updated by Emmanuel DILLARD 11 months ago

  • Project changed from Backlog to Backlog Courrier
  • Target version changed from 19.04 (Support sécurité) to 19.04 (Sécurité)

Also available in: Atom PDF