[résolu] Besoin d'aide pour écrire une requête SQL compliquée

Besoin d'aide pour écrire une requête SQL compliquée [résolu] - SQL/NoSQL - Programmation

Marsh Posté le 08-11-2006 à 16:46:32    

Bonjour,
 
Je sollicite votre aide pour écrire une requête SQL (pour MySQL) qui me paraît compliquée.
Voilà la structure de table que je souhaite interroger :  
Configurations (ConfigurationID, ..., ConfigurationInternalVersion, ConfigurationDate, ConfigurationParentID);
ConfigurationID = clé primaire, entier
ConfigurationInternalVersion = n° de version de la configuration (interne à l'appli), entier allant de 1 à n
ConfigurationDate = date et heure à laquelle la configuration a été figée (NULL si elle ne l'est pas), datetime
ConfigurationParentID = ID de la configuration parente, NULL quand ConfigurationInternalVersion = 1
 
Ce que je veux récupérer, c'est pour chaque "famille" de configurations, la version interne la plus élevée ayant sa date non NULL.  


ex :  
|  ID | InternalVer |     Date    |  ParentID |
+----+-----------+-----------+----------+
|  1  |       1        |2006-11-01|    NULL    |
|  2  |       2        |2006-11-03|       1      |
|  3  |       1        |2006-11-02|    NULL    |
|  4  |       3        |   NULL      |       1      |  
+---+------------+-----------+----------+    
 
Je souhaite avoir en sortie :  
|  ID | InternalVer |     Date    |  ParentID |
+----+-----------+-----------+----------+
|  2  |       2        |2006-11-03|       1      |
|  3  |       1        |2006-11-02|    NULL    |
+---+------------+-----------+----------+


 
Merci par avance :)


Message édité par rufo le 07-12-2006 à 14:25:51
Reply

Marsh Posté le 08-11-2006 à 16:46:32   

Reply

Marsh Posté le 09-11-2006 à 09:51:47    

si vous avez besoin de plus de précision, y'a qu'à demander :)

Reply

Marsh Posté le 09-11-2006 à 12:01:09    

Bon, j'ai trouvé une requête qui fonctionne mais si vous trouvez une requête plus optimisée (notamment pour dégager les IN qui consomment pas de temps si je me souviens bien), je suis preneur :)
 
SELECT ConfigurationID FROM Configurations WHERE (ConfigurationParentID, ConfigurationInternalVersion) IN (SELECT c.ConfigurationParentID, MAX(c.ConfigurationInternalVersion) AS MaxInternalVersion FROM Configurations c WHERE  
c.ConfigurationDate IS NOT NULL GROUP BY c.ConfigurationParentID)
UNION DISTINCT SELECT ConfigurationID FROM Configurations WHERE ConfigurationParentID IS NULL AND ConfigurationDate IS NOT NULL AND ConfigurationID NOT IN (SELECT DISTINCT c.ConfigurationParentID FROM Configurations c WHERE c.ConfigurationParentID IS NOT NULL)

Reply

Marsh Posté le 09-11-2006 à 15:58:25    

mais arrêtez avec vos "IN" à tout bout de champ :sweat:
 
EXISTS vin Dieu !
 
et juste en passant "UNION" est obligatoirement "DISTINCT", donc pas besoin de le spécifier (sinon, on écrit UNION ALL si on veut les doublons).

Reply

Marsh Posté le 13-11-2006 à 11:03:30    

MagicBuzz a écrit :

mais arrêtez avec vos "IN" à tout bout de champ :sweat:
 
EXISTS vin Dieu !
 
et juste en passant "UNION" est obligatoirement "DISTINCT", donc pas besoin de le spécifier (sinon, on écrit UNION ALL si on veut les doublons).


 
pour le coup du union distinct, je m'en suis rendu compte ce WE en lisant un bonquin sur SQL. Par contre, j'ai dû mal à voir la tête de la requête avec des EXISTS à la place des IN.

Reply

Marsh Posté le 13-11-2006 à 14:58:35    

la syntaxe ne change pas d'un poil avec EXISTS.
 
Je vais pas réécrire pour la 1000 fois ce type de requête avec un EXISTS à la place des IN. Cherche juste "sodo gravier" ou "seau d'eau gravier" (ça dépend de mon humeur) sur le forum, tu trouveras forcément ces exemples que j'ai posté. Ca doit faire au moins 1 an que je passe mon temps à dire que le IN n'est pas fait pour ce genre de choses, j'en ai marre de prêcher dans le désert.


Message édité par MagicBuzz le 13-11-2006 à 14:58:42
Reply

Marsh Posté le 14-11-2006 à 16:03:26    

Une question : est-ce-que le c.ConfigurationParentID de la première aprtie de ma requête est le même que le c.ConfigurationParentID qui se trouve dans la 2ième partie (la 2ième partie étant celle après le UNION). En d'autres termes, est-ce que l'alias "c" que j'utilise est local à ma sous-requête ou il est commun à toute la requête, y compris la sous-requête se trouvant après le UNION?

Reply

Marsh Posté le 14-11-2006 à 16:44:09    

il est différent de chaque côté du union.
 
par contre, pour plus de lisibilité, tu as raison, il vaudrais mieux trouver deux alias différents. style "c" et "cp" pour "configyationparent" par exemple

Reply

Marsh Posté le 14-11-2006 à 18:25:27    

J'ai un soucis, toujours sur la même requête. Quand j'execute cette requête, ça va, ça répond vite (ma BD ne fait que 182 ko)
 

SELECT COUNT(DISTINCT c.ConfigurationID) FROM Configurations c, Components cmp0, ComponentsAttributes cmpa0, Attributes attr0, Components cmp1, ComponentsAttributes cmpa1, Attributes attr1 WHERE c.ConfigurationID IN (SELECT sc1.ConfigurationID FROM Configurations sc1 WHERE (sc1.ConfigurationParentID, sc1.ConfigurationInternalVersion) IN (SELECT sco1.ConfigurationParentID, MAX(sco1.ConfigurationInternalVersion ) AS MaxInternalVersion FROM Configurations sco1 WHERE sco1.ConfigurationDate IS NOT NULL GROUP BY sco1.ConfigurationParentID)
UNION SELECT usc1.ConfigurationID FROM Configurations usc1 WHERE usc1.ConfigurationParentID IS NULL AND usc1.ConfigurationDate IS NOT NULL AND usc1.ConfigurationID NOT IN (SELECT DISTINCT sco2.ConfigurationParentID FROM Configurations sco2 WHERE sco2.ConfigurationParentID IS NOT NULL)) AND c.ConfigurationID = cmp0.ConfigurationID AND cmp0.ComponentID = cmpa0.ComponentID AND cmpa0.AttributeID = attr0.AttributeID AND attr0.AttributeName LIKE "%nom%" AND attr0.AttributeValue LIKE "%fd%" AND c.ConfigurationID = cmp1.ConfigurationID AND cmp1.ComponentID = cmpa1.ComponentID AND cmpa1.AttributeID = attr1.AttributeID AND attr1.AttributeName LIKE "%modele%" AND attr1.AttributeValue LIKE "%drive%"


 
Par contre, en exécutant cette requête, mysql part en live et se met à pomper 1 Go de RAM et j'ai mon résultat au bout de plusieurs minutes!!!!! Pourquoi?  :pt1cable:  
 

SELECT DISTINCT c.ConfigurationID, c.ConfigurationName, c.ConfigurationVersion, c.ConfigurationInternalVersion, c.ConfigurationDate, c.ConfigurationComment, c.ConfigurationParentID FROM Configurations c, Components cmp0, ComponentsAttributes cmpa0, Attributes attr0, Components cmp1, ComponentsAttributes cmpa1, Attributes attr1 WHERE c.ConfigurationID IN (SELECT sc1.ConfigurationID FROM Configurations sc1 WHERE (sc1.ConfigurationParentID, sc1.ConfigurationInternalVersion) IN (SELECT sco1.ConfigurationParentID, MAX(sco1.ConfigurationInternalVersion) AS MaxInternalVersion FROM Configurations sco1 WHERE sco1.ConfigurationDate IS NOT NULL GROUP BY sco1.ConfigurationParentID) UNION SELECT usc1.ConfigurationID FROM Configurations usc1 WHERE usc1.ConfigurationParentID IS NULL AND usc1.ConfigurationDate IS NOT NULL AND usc1.ConfigurationID NOT IN (SELECT DISTINCT sco2.ConfigurationParentID FROM Configurations sco2 WHERE sco2.ConfigurationParentID IS NOT NULL)) AND c.ConfigurationID = cmp0.ConfigurationID AND cmp0.ComponentID = cmpa0.ComponentID AND cmpa0.AttributeID = attr0.AttributeID AND attr0.AttributeName LIKE "%nom%" AND attr0.AttributeValue LIKE "%fd%" AND c.ConfigurationID = cmp1.ConfigurationID AND cmp1.ComponentID = cmpa1.ComponentID AND cmpa1.AttributeID = attr1.AttributeID AND attr1.AttributeName LIKE "%modele%" AND attr1.AttributeValue LIKE "%drive%" ORDER BY c.ConfigurationName LIMIT 0, 8


 
C'est la même requête, sauf que dans le 1er cas, je récupère ne nb de configurations totales correspondant à mes critères et dans le second, je récupère des infos sur qq confs correspondant à mes critères.
 
ps : vous connaissez un bon tuto sur EXPLAIN de MySQL? Merci :jap:


Message édité par rufo le 14-11-2006 à 18:27:10
Reply

Marsh Posté le 14-11-2006 à 19:16:46    

T'ain, tu bosserais pas sous MySQL, je jureraus que tu bosses pour Econocom :o
Le même modèle des données abominable :D
 
Est-ce qu'il y a moyen de nous la refaire au ralenti ta requête ?
 
C'est à dire, la structure des tables, et ce que tu veux récupérer (avec si possible un jeu de données de quelques lignes).
 
Parceque là comme ça, à froid, et sans indentation, je t'avoue que j'ai même pas lu jusqu'au bout ;)

Reply

Marsh Posté le 14-11-2006 à 19:16:46   

Reply

Marsh Posté le 15-11-2006 à 10:24:26    

Voici un topic sur le même sujet : http://forum.hardware.fr/forum2.ph [...] w=0&nojs=0
 
Il s'agit d'un gestionnaire de configurations (matériel et logiciel de machines). Voici mes tables :  
Configurations (ConfigurationID, ConfigurationName, ConfigurationDate, ConfigurationVersion, ConfigurationInternalVersion, ConfigurationParentID)
Components (ComponentID, ConfigurationID)
ComponentsAttributes (ComponentAttributeID, ComponentID, AttributeID)
Attributes (AttributeID, AttributeName, AttributeType, AttributeValue, AttributeCompareType)
 
Qq règles :  
1) une configuration ayant sa version interne à 1 (la première version d'une famille de configurations) à ConfigurationParentID à NULL
2) une configuration ayant sa version interne > 1 à un ConfigurationParentID <> NULL
3) toutes les configurations d'une même famille ont le même ConfigurationParentID (à l'exception de la configuration parente qui a son ConfigurationParentID à NULL, cf. règle 1)
4) une configuration a des composants, 1 composant n'appartient qu'à une conf
5) un composant à des attributs
6) un attribut peut être partagé par plusieurs composants
 
Avec ma requête bourrine, je veux récupérer la dernière configuration de chaque famille de confs (celle qui a sa version interne la plus élevée) qui a des composants dont l'un possède un attribut dont le nom = "%nom%" et a pour valeur = "%fd%" et l'autre possède un attribut dont le nom = "%modele%" et a pour valeur = "%drive%".

Reply

Marsh Posté le 15-11-2006 à 10:34:20    

Voici la requête indentée et le résultat du EXPLAIN :
 

Code :
  1. SELECT DISTINCT c.ConfigurationID, c.ConfigurationName, c.ConfigurationVersion, c.ConfigurationInternalVersion, c.ConfigurationDate, c.ConfigurationComment, c.ConfigurationParentID
  2. FROM Configurations c, Components cmp0, ComponentsAttributes cmpa0, Attributes attr0, Components cmp1, ComponentsAttributes cmpa1, Attributes attr1
  3. WHERE c.ConfigurationID
  4. IN (
  5.       SELECT sc1.ConfigurationID
  6.       FROM Configurations sc1
  7.       WHERE (
  8.            sc1.ConfigurationParentID, sc1.ConfigurationInternalVersion
  9.       )
  10.       IN (
  11.             SELECT sco1.ConfigurationParentID, MAX( sco1.ConfigurationInternalVersion ) AS MaxInternalVersion
  12.             FROM Configurations sco1
  13.             WHERE sco1.ConfigurationDate IS NOT NULL
  14.             GROUP BY sco1.ConfigurationParentID
  15.       )
  16.       UNION SELECT usc1.ConfigurationID
  17.       FROM Configurations usc1
  18.       WHERE usc1.ConfigurationParentID IS NULL
  19.       AND usc1.ConfigurationDate IS NOT NULL
  20.       AND usc1.ConfigurationID NOT
  21.       IN (
  22.             SELECT DISTINCT sco2.ConfigurationParentID
  23.             FROM Configurations sco2
  24.             WHERE sco2.ConfigurationParentID IS NOT NULL
  25.       )
  26. )
  27. AND c.ConfigurationID = cmp0.ConfigurationID
  28. AND cmp0.ComponentID = cmpa0.ComponentID
  29. AND cmpa0.AttributeID = attr0.AttributeID
  30. AND attr0.AttributeName LIKE "%nom%"
  31. AND attr0.AttributeValue LIKE "%fd%"
  32. AND c.ConfigurationID = cmp1.ConfigurationID
  33. AND cmp1.ComponentID = cmpa1.ComponentID
  34. AND cmpa1.AttributeID = attr1.AttributeID
  35. AND attr1.AttributeName LIKE "%modele%"
  36. AND attr1.AttributeValue LIKE "%drive%"
  37. ORDER BY c.ConfigurationName
  38. LIMIT 0 , 8


 
 


 id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
1  PRIMARY  cmpa0  ALL  ComponentID  NULL  NULL  NULL  809  Using temporary; Using filesort
1  PRIMARY  cmpa1  ALL  ComponentID  NULL  NULL  NULL  809    
1  PRIMARY  cmp1  eq_ref  PRIMARY  PRIMARY  3  testgconfimport.cmpa1.ComponentID  1  Using where
1  PRIMARY  attr0  eq_ref  PRIMARY  PRIMARY  4  testgconfimport.cmpa0.AttributeID  1  Using where
1  PRIMARY  attr1  eq_ref  PRIMARY  PRIMARY  4  testgconfimport.cmpa1.AttributeID  1  Using where
1  PRIMARY  cmp0  eq_ref  PRIMARY  PRIMARY  3  testgconfimport.cmpa0.ComponentID  1  Using where
1  PRIMARY  c  eq_ref  PRIMARY  PRIMARY  3  testgconfimport.cmp0.ConfigurationID  1  Using where
2  DEPENDENT SUBQUERY  sc1  eq_ref  PRIMARY  PRIMARY  3  func  1  Using where
3  DEPENDENT SUBQUERY  sco1  ALL  NULL  NULL  NULL  NULL  14  Using where; Using temporary; Using filesort
4  DEPENDENT UNION  usc1  eq_ref  PRIMARY  PRIMARY  3  func  1  Using where
5  DEPENDENT SUBQUERY  sco2  index  NULL  ConfigurationName  106  NULL  14  Using where; Using index; Using temporary
NULL  UNION RESULT  <union2,4>  ALL  NULL  NULL  NULL  NULL  NULL    


Message édité par rufo le 15-11-2006 à 10:45:27
Reply

Marsh Posté le 15-11-2006 à 11:10:45    

Ok.
 
Je vais tenter de réécrire ta requête étape par étape.
Au départ je voulais faire des EXISTS pour remplacer tes IN qui n'ont aucun rapport avec la problématique, mais je m'apperçois que des jointures simples font parfaitement l'affaire aussi pour la première du moins.
 
Question : qu'est-ce que "ComponentAttributeID" ?
 
Etape 1 :
Toutes les configurations qui un attribut "%nom%" ayant pour valeur "%fd"% ainsi qu'un attribut "%modele%" ayant pour valeur "%drive%"
 


select cf.configurationparentid, cf.configurationid
from configurations cf inner join components co on co.configurationid = cf.configurationid
inner join componentsattributes coa1 on coa1.componentid = co.componentid
inner join attributes a1 on a1.attributeid = coa1.attributeid
inner join componentsattributes coa2 on coa2.componentid = co.componentid
inner join attributes a2 on a2.attributeid = coa2.attributeid
where a1.attributename like '%nom%' and a1.attributevalue like '%fd%'
and a2.attributename like '%modele%' and a2.attributevalue like '%drive%'


 
Etape 2 :
Je veux l'ID de la dernière version de chaque configuration
 


select cfs2.configurationid
from configuration cfs2
where cfs2.configurationinternalversion =
(
  select max(cfs1.configurationinternalversion)
  from configurations cfs1
  where cfs1.configurationparentid = cfs2.configurationparentid
)
union all
select cfs2.configurationid
from configuration cfs2
where cfs2.configurationparentid is null
and not exists
(
  select null
  from configurations cfs1
  where cfs1.configurationparentid = cfs2.configurationid
)


 
Etape 3 :
Le mixe de tout ce joyeux bordel, et résultat final
 


select cf.configurationparentid, cf.configurationid
from
(
  select cfs2.configurationid
  from configuration cfs2
  where cfs2.configurationinternalversion =
  (
    select max(cfs1.configurationinternalversion)
    from configurations cfs1
    where cfs1.configurationparentid = cfs2.configurationparentid
  )
  union all
  select cfs2.configurationid
  from configuration cfs2
  where cfs2.configurationparentid is null
  and not exists
  (
    select null
    from configurations cfs1
    where cfs1.configurationparentid = cfs2.configurationid
  )
) cf
inner join components co on co.configurationid = cf.configurationid
inner join componentsattributes coa1 on coa1.componentid = co.componentid
inner join attributes a1 on a1.attributeid = coa1.attributeid
inner join componentsattributes coa2 on coa2.componentid = co.componentid
inner join attributes a2 on a2.attributeid = coa2.attributeid
where a1.attributename like '%nom%' and a1.attributevalue like '%fd%'
and a2.attributename like '%modele%' and a2.attributevalue like '%drive%'


 
Notes :
- Afin d'éviter le UNION inutile, il suffit de faire un configurationparentid = configurationid pour le premier niveau (nécessite la mise à jour des données)
- Pourquoi des like ?
- Une vue regroupant l'ensemble des attributs d'une configuration permettrait à la fois d'alléger la requête, mais aussi d'accélérer les traîtements

Message cité 1 fois
Message édité par MagicBuzz le 15-11-2006 à 11:12:43
Reply

Marsh Posté le 15-11-2006 à 11:15:01    

Par contre, le seul souci, c'est que je me rends compte que ma requête n'est pas vraiment plus simple que la tienne. Mise à part pour obtenir la liste des configs les plus récentes, on a la même... J'espère que ça va quand même aider à accélérer tout ça.

Reply

Marsh Posté le 15-11-2006 à 11:21:12    

C'est bon, j'ai trouvé une requête qui va bien. Elle est largement encore optimisable mais au moins, elle va vite (pour l'instant)
 

Code :
  1. SELECT c.ConfigurationID, c.ConfigurationName, c.ConfigurationDate, c.ConfigurationVersion, c.ConfigurationInternalVersion, c.ConfigurationComment, c.ConfigurationParentID
  2. FROM Configurations c, (
  3.          SELECT DISTINCT fc.ConfigurationID
  4.          FROM Configurations fc, Components cmp0, ComponentsAttributes cmpa0, Attributes attr0, Components cmp1, ComponentsAttributes cmpa1,               
  5.          Attributes attr1
  6.          WHERE fc.ConfigurationID
  7.          IN (
  8.                SELECT sc1.ConfigurationID
  9.                FROM Configurations sc1
  10.                WHERE (
  11.                            sc1.ConfigurationParentID, sc1.ConfigurationInternalVersion
  12.                )
  13.                IN (
  14.                      SELECT sco1.ConfigurationParentID, MAX( sco1.ConfigurationInternalVersion ) AS MaxInternalVersion
  15.                      FROM Configurations sco1
  16.                      WHERE sco1.ConfigurationDate IS NOT NULL
  17.                      GROUP BY sco1.ConfigurationParentID
  18.               )
  19.               UNION SELECT usc1.ConfigurationID
  20.               FROM Configurations usc1
  21.               WHERE usc1.ConfigurationParentID IS NULL
  22.              AND usc1.ConfigurationDate IS NOT NULL
  23.              AND usc1.ConfigurationID NOT
  24.              IN (
  25.                    SELECT DISTINCT sco2.ConfigurationParentID
  26.                    FROM Configurations sco2
  27.                    WHERE sco2.ConfigurationParentID IS NOT NULL
  28.              )
  29.      )
  30.      AND fc.ConfigurationID = cmp0.ConfigurationID
  31.      AND cmp0.ComponentID = cmpa0.ComponentID
  32.      AND cmpa0.AttributeID = attr0.AttributeID
  33.      AND attr0.AttributeName LIKE "%nom%"
  34.      AND attr0.AttributeValue LIKE "%fd%"
  35.      AND fc.ConfigurationID = cmp1.ConfigurationID
  36.      AND cmp1.ComponentID = cmpa1.ComponentID
  37.      AND cmpa1.AttributeID = attr1.AttributeID
  38.      AND attr1.AttributeName LIKE "%modele%"
  39.      AND attr1.AttributeValue LIKE "%drive%"
  40. ) AS ConfsTmp
  41. WHERE c.ConfigurationID = ConfsTmp.ConfigurationID
  42. ORDER BY c.ConfigurationName
  43. LIMIT 0 , 8

Reply

Marsh Posté le 15-11-2006 à 11:27:32    

:heink:
 
je pige pas tout... y'as juste rajouté un niveau de sous-jointure pour récupérer les infos de la config à partir de la liste des id retrouvés par la sous-requête géante ?
 
:heink:
 
et mysql va plus vite avec ça ?
 
:heink:
 
quel sgbd de merde :sweat:

Reply

Marsh Posté le 15-11-2006 à 11:27:48    

En fait, j'ai l'impression que l'interpréteur de requêtes de MySQL effectuait d'abord les LIKE et créait une table temporaire contenant comme tous les champs du SELECT du début de la requête ainsi que tous les champs de la table attributes et ce pour chaque attribut correspondant aux critères des LIKE! :/

Reply

Marsh Posté le 15-11-2006 à 11:28:19    

ps : c'est vrai que j'avais pas remarqué un truc... c'est quoi ce DISTINCT de misère ?

Reply

Marsh Posté le 15-11-2006 à 11:28:57    

MagicBuzz a écrit :

:heink:
 
je pige pas tout... y'as juste rajouté un niveau de sous-jointure pour récupérer les infos de la config à partir de la liste des id retrouvés par la sous-requête géante ?
 
:heink:
 
et mysql va plus vite avec ça ?
 
:heink:
 
quel sgbd de merde :sweat:


 
oui, ça va plus vite (sans commune mesure : < 1s contre plusieurs minutes et 1Go de RAM bouffé)

Reply

Marsh Posté le 15-11-2006 à 11:29:03    

nan, cherchepas plus loin, c'est donc distinct sur 25 champs qui fout la merde... ça kill n'importe quel sgbd dès 4 lignes...

Reply

Marsh Posté le 15-11-2006 à 11:30:25    

ceci dit, t'es sur que t'as des doublons si tu vire le distinct ?
parceque je ne vois pas où tu pourrais en avoir...

Reply

Marsh Posté le 15-11-2006 à 11:33:58    

J'avais aps vu la requête que tu avais écrite. Je vais tester pour voir si elle va plus vite et surtout, si elle retourne bien le bon résultat (ça peut aider :D)... En tout cas, merci d'avoir consacré du temps à mon pb :jap:
 
ps : on m'a dit que les JOIN étaient préférables à des IN ou des EXISTS. Si ta requête peut me faire gagner encore du temps, ça sera utile quand il y aura une montée en charge de la BD...

Reply

Marsh Posté le 15-11-2006 à 11:37:07    

MagicBuzz a écrit :

ceci dit, t'es sur que t'as des doublons si tu vire le distinct ?
parceque je ne vois pas où tu pourrais en avoir...


 
Je peux virer les DISTINCT de mes sous-requêtes, mais il faut alors que j'en mette un dans le premier SELECT, sinon, j'ai des doublons.

Reply

Marsh Posté le 15-11-2006 à 11:39:44    

MagicBuzz a écrit :

Ok.
 
...
Notes :
- Afin d'éviter le UNION inutile, il suffit de faire un configurationparentid = configurationid pour le premier niveau (nécessite la mise à jour des données)
- Pourquoi des like ?
- Une vue regroupant l'ensemble des attributs d'une configuration permettrait à la fois d'alléger la requête, mais aussi d'accélérer les traîtements


 
l'utilisateur ne va pas connaître le nom exacte de chaque attribut d'une conf ni sa valeur (y'en a qui font plus de 100 caractères!)


Message édité par rufo le 15-11-2006 à 11:41:05
Reply

Marsh Posté le 15-11-2006 à 11:51:31    

Bon, après qq corrections de syntaxe de ta requête, elle donne bien de même résultat et à première vue, de manière plus rapide :)
Par contre, il faut que je fasse la même bidouille que pour la dernière requête que j'ai écrite (mettre ta requête en tant que sous-requête du SELECT c.ConfigurationName...) car ta requête ne renvoie que l'ID de la conf et celui de son parent, mais pas son nom, sa version, sa date, etc...

Reply

Marsh Posté le 15-11-2006 à 11:55:16    

d'accord pour le coup du distinct. en fait, tu peux avoir plusieurs attributs contenant "drive" par exemple c'est ça ?
 
dans ce cas, en effet, le mieux à faire, c'est ton histoire de sous-requête avec un distinct sur le id uniquement.
 
sinon, à la limite, tu peux tenter de faire des EXISTS à la place des deux jointures sur les attributs. en effet, à la base, un EXISTS c'est ni plus ni moins qu'un test de validité d'une jointure (d'où ma grande préférence par rapport à un IN, qui exécute réellement la sous-requête et crée un lot de résultats).
=> ça évitera le DISTINCT, qui de toute façon n'est pas mieux que des EXISTS. A tester en tout cas.


Message édité par MagicBuzz le 15-11-2006 à 11:56:12
Reply

Marsh Posté le 15-11-2006 à 12:00:31    

en fait, ça donnerait :
 


select cf.ConfigurationID, cf.ConfigurationName, cf.ConfigurationDate, cf.ConfigurationVersion, cf.ConfigurationInternalVersion, cf.ConfigurationComment, cf.ConfigurationParentID
from  
(  
  select cfs2.configurationid  
  from configuration cfs2  
  where cfs2.configurationinternalversion =  
  (  
    select max(cfs1.configurationinternalversion)  
    from configurations cfs1  
    where cfs1.configurationparentid = cfs2.configurationparentid  
  )  
  union all  
  select cfs2.configurationid  
  from configuration cfs2  
  where cfs2.configurationparentid is null  
  and not exists  
  (  
    select null  
    from configurations cfs1  
    where cfs1.configurationparentid = cfs2.configurationid  
  )  
) cf
inner join components co on co.configurationid = cf.configurationid
where exists
(
  select null
  from componentsattributes inner join attributes a1 on a1.attributeid = coa1.attributeid
  where coa1.componentid = co.componentid
  and a1.attributename like '%nom%' and a1.attributevalue like '%fd%'
)
and exists
(
  select null
  from componentsattributes inner join attributes a2 on a2.attributeid = coa2.attributeid
  where coa2.componentid = co.componentid
  and a2.attributename like '%modele%' and a2.attributevalue like '%drive%'
)


 

Reply

Marsh Posté le 15-11-2006 à 12:01:27    

au final, ça donne :  
 

Code :
  1. SELECT c.ConfigurationID, c.ConfigurationName, c.ConfigurationDate, c.ConfigurationVersion, c.ConfigurationInternalVersion, c.ConfigurationComment, c.ConfigurationParentID
  2. FROM Configurations c,
  3.     (SELECT cf.configurationid
  4.      FROM
  5.              (SELECT cfs2.configurationid
  6.               FROM configurations cfs2
  7.               WHERE cfs2.configurationinternalversion =
  8.                    (select max(cfs1.configurationinternalversion)
  9.                     from configurations cfs1
  10.                     where cfs1.configurationparentid = cfs2.configurationparentid
  11.               )
  12.               union all select cfs2.configurationid
  13.               from configurations cfs2
  14.               where cfs2.configurationparentid is null
  15.               and not exists (
  16.                                     select null
  17.                                     from configurations cfs1
  18.                                     where cfs1.configurationparentid = cfs2.configurationid
  19.              )
  20.     ) AS cf
  21.     inner join components co on co.configurationid = cf.configurationid
  22.     inner join componentsattributes coa1 on coa1.componentid = co.componentid
  23.     inner join attributes a1 on a1.attributeid = coa1.attributeid
  24.     inner join componentsattributes coa2 on coa2.componentid = co.componentid
  25.     inner join attributes a2 on a2.attributeid = coa2.attributeid
  26.     where a1.attributename like '%nom%'
  27.     and a1.attributevalue like '%fd%'
  28.     and a2.attributename like '%modele%'
  29.     and a2.attributevalue like '%drive%'
  30. ) AS ConfsTmp
  31. WHERE c.ConfigurationID = ConfsTmp.ConfigurationID
  32. ORDER BY c.ConfigurationName
  33. LIMIT 0, 8


 
ouf, on y est! :)


Message édité par rufo le 15-11-2006 à 12:04:50
Reply

Marsh Posté le 15-11-2006 à 12:02:45    

nan, ça y est pas, teste ma dernière requête ;)

Reply

Marsh Posté le 15-11-2006 à 15:06:34    

MagicBuzz a écrit :

nan, ça y est pas, teste ma dernière requête ;)


 toujours le même pb. Dans ta table "cf", il n'y a que le champ ConfigurationID. Tu oublies de faire une jointure sur la table Configurations pour pouvoir récupérer les autres infos sur les confs. Au final, on retombe à peu de chose près sur ton avant-dernière requête que j'ai modifiée.
 
Je viens de tester, côté temps, c'est kif kif...

Reply

Marsh Posté le 15-11-2006 à 15:13:30    

arf, oui, c'est vrai, j'ai oublié que mon cf est un sous-select en fait :D
ceci dit, tu peux rajouter les champs dans les cfs2 (la sous-requête) afin d'éviter de joindre pour rien avec cf à la fin.
 
mais bon, ça changera pas grand chose aux perfs

Reply

Marsh Posté le 15-11-2006 à 16:04:32    

Après des tests plus poussés, il faut mettre un DISTINCT dans le 1er SELECT sinon, y'a des doublons

Reply

Marsh Posté le 15-11-2006 à 16:08:19    

par contre, j'ai finalement utilisé ta dernière version de requête car celle-ci est construite dynamiquement par mon appli et entre autre, on active ou pas la récupération de la dernière version de chaque conf. Or les LIKE, qui sont aussi d'autres paramètres activables ou pas, sont inclus dans la sous-requête qui récupère la dernière version de conf ce qui est génant si l'utilisateur veut activer les LIKE et pas la récupération de la dernière version de conf. Ta dernière requête de prête mieux à ma construction dynamique ;)... et après test, ça marche nickel. Merci beaucoup pour ton aide.

Reply

Marsh Posté le 15-11-2006 à 16:09:28    

de rien :)

Reply

Marsh Posté le 15-11-2006 à 16:21:26    

une dernière question : c'est vrai que pour MySQL, mettre un index sur un champ où on utilise LIKE, ça ne sert à rien car LIKE n'utilise pss les index?

Reply

Marsh Posté le 15-11-2006 à 16:55:47    

vérifie, mais j'abonde plutôt dans ce sens.
disons que l'index pourra être utilisé, mais n'apportera vraiment pas grand chose.
d'où le fait qu'il est très fortement déconseillé d'utiliser un like (et c'est pour ça que je t'ai demandé au début si c'était vraiment nécessaire ;))

Reply

Marsh Posté le 15-11-2006 à 17:49:59    

oui, la recherche d'infos partielles dans des chaînes de caractères est nécessaire. Maintenant, à voir si l'utilisation de MATCH serait mieux?

Reply

Marsh Posté le 15-11-2006 à 17:52:36    

qq chose me dit que j'aurais sans doute à nouveau besoin de tes lumières pour l'optimisation de 3 requêtes permettant de comparer de configurations entre elles : trouvers les attributs en +, en - et les attributs de même nom mais de valeur différente. J'ai déjà écrit les requêtes mais j'ai peur qu'avec la monté en charge de la BD, ça ne devienne très lent...:/

Reply

Marsh Posté le 16-11-2006 à 09:34:35    

pour ces trucs là, je pense que le mieux, c'est de faire une requête "toute bête" qui ramène l'intégralité des attributs d'une config.
la lancer pour chacune des deux configs.
puis comparer dans ton appli, ou à la limite dans une procédure stockée.
mais en requête, à mon avis tu vas être hyper limité (trop de cas possibles, donc complexité accrue de ta requête) d'autant plus que pour l'affichage, tu risques de vouloir aussi les attributs communs.

Reply

Marsh Posté le 16-11-2006 à 09:35:59    

sinon, le coup du MATCH pour retrouver des mots exacts, je suis pas certain que ce soit une super idée. MATCH (et dérivés) ça sert à faire des moteurs de recherche tapant dans de gros volumes d'infos. dans ton cas, ça me semble peu justifié, et surtout, MATCH peut faire de l'approximation d'orthographe, ce que tu ne veux pas forcément.

Reply

Marsh Posté le    

Reply

Sujets relatifs:

Leave a Replay

Make sure you enter the(*)required information where indicate.HTML code is not allowed