Performance jointure sql

Version imprimable

Bonjour j'ai une requete sql généré en partie par SSRS( pas d'utilisation d'alias désolé)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT        REPLACE(LTRIM(REPLACE(INVENTORY.MATNR, 0, ' ')), ' ', 0) AS MATNR, INVENTORY.WERKS, INVENTORY.LGORT, INVENTORY.CHARG, INVENTORY.SOBKZ, REPLACE(LTRIM(REPLACE(INVENTORY.KUNNR, 0, 
                         ' ')), ' ', 0) AS KUNNR, REPLACE(LTRIM(REPLACE(INVENTORY.LIFNR, 0, ' ')), ' ', 0) AS LIFNR, INVENTORY.VBELN, INVENTORY.POSNR, INVENTORY.LABST, INVENTORY.UMLME, INVENTORY.INSME, 
                         INVENTORY.EINME, INVENTORY.SPEME, INVENTORY.RETME, INVENTORY.DLINL, INVENTORY.LGBPE, INVENTORY.KZILL, INVENTORY.KZILQ, INVENTORY.KZILE, INVENTORY.KZILS, INVENTORY.DISKZ, 
                         INVENTORY.[MBEW-VPRSV], INVENTORY.[MBEW-BKLAS], INVENTORY.[MBEW-LAEPR], INVENTORY.[MBEW-BWTTY], INVENTORY.PRICE, INVENTORY.[MBEW-BWTAR], INVENTORY.Total_Value, 
                         INVENTORY.GUID, INVENTORY.Stock_Price, INVENTORY.Stock_Value, INVENTORY.Sum, MATERIAL.FRdes, MATERIAL.ENdes, MATERIAL.DEdes, MATERIAL.ITdes, MATERIAL.ESdes, MATERIAL.PTdes, 
                         MATERIAL.NLdes, MATERIAL.MTART, MATERIAL.MATKL, Material_Detail.EKGRP, Material_Detail.DISPO
FROM           INVENTORY  
                          LEFT OUTER JOIN
                         Material_Detail ON INVENTORY.MATNR = Material_Detail.MATNR AND INVENTORY.WERKS = Material_Detail.WERKS INNER JOIN
						 Material ON MATERIAL.MATNR = INVENTORY.MATNR 
WHERE      /*(INVENTORY.WERKS IN (@werks)) AND (ISNULL(INVENTORY.LGORT, 'NULL') IN (@lgort)) AND (REPLACE(LTRIM(REPLACE(INVENTORY.MATNR, 0, ' ')), ' ', 0) = @MATNR) AND (INVENTORY.DATE = @date1) AND 
                         (ISNULL(INVENTORY.SOBKZ, 'NULL') IN (@sobkz)) AND (ISNULL(INVENTORY.DLINL, '2000-01-01') < ISNULL(@date_inventaire, GETDATE())) OR
                         (INVENTORY.WERKS IN (@werks)) AND (ISNULL(INVENTORY.LGORT, 'NULL') IN (@lgort)) AND*/ (INVENTORY.DATE = '2016-06-27')/* AND (ISNULL(INVENTORY.SOBKZ, 'NULL') IN (@sobkz)) AND 
                         (ISNULL(INVENTORY.DLINL, '2000-01-01') < ISNULL(@date_inventaire, GETDATE())) AND (@MATNR IS NULL)*/

Je devrais avoir 29450 ligne en gros que j'arrive à obtenir mais en seulement 6 minutes 30, si ma première jointure est en inner join cela prends 12 secondes pour 29000 lignes (normal que des lignes disparaissent, pas de soucis la dessus). Pourquoi une telle différence de temps , c'est comme si il prenait le where en compte différement en inner join et en left outer join.
Quelqu'un pourrait-il m'aider ou au moins m'expliquer cette différence?

28/06/2016, 11h57
skuatamad

La jointure est étrange, est-il normal qu'il n'y ait pas de lien entre Material et Material_Detail ?
28/06/2016, 13h08
oupepasa

Je n'ai pas besoin de ce lien effectivement,
qu'y a t'il d'étrange j'ai une table principale et je vais chercher des informations dans 2 autre table?
28/06/2016, 15h35
skuatamad

Citation:

Envoyé par oupepasa

qu'y a t'il d'étrange

Sémantiquement parlant, il est étrange qu'il n'y ait pas de lien entre MATERIAL et MATERIAL_DETAIL. Le choix du nom des tables ne semble donc pas très pertinent.

Citation:

Envoyé par oupepasa

j'ai une table principale et je vais chercher des informations dans 2 autre table?

En fonction de la cardinalité des relations entre INVENTORY et MATERIAL et entre INVENTORY et MATERIAL_DETAIL, ça peut démultiplier les lignes :
http://www.developpez.net/forums/d12...e/#post6958167
28/06/2016, 15h40
StringBuilder
Bonjour,

Que se passe-t-il si vous mettez la jointure externe après la jointure interne ? (j'ai pour habitude de toujours renvoyer à la fin les jointures externes, me disant que ça permet au SGBD de commencer par la partie "facile", même si je doute un peu que ça change quoi que ce soit...)

Les clés étrangères sont-elles matérialisées ?

Si possible, créer les relations suivantes :
Code:

1 2 3 alter table inventory add foreign key (matnr) references material (matnr); alter table Material_Detail add foreign key (matnr, werks) references inventory (matnr, werks);
PS : Accessoirement, j'ai l'impression que votre table de départ est "material" et non "inventory".
PS² : J'ai cependant un léger doute quant à la seconde contrainte... werks ne serait-il pas nullable dans la table inventory par hasard ?

Que donne donc la clause FROM suivante ?
Code:

1 2 3 4 FROM Material inner join INVENTORY on INVENTORY.MATNR = MATERIAL.MATNR LEFT OUTER JOIN Material_Detail ON Material_Detail.MATNR = INVENTORY.MATNR AND Material_Detail.WERKS = INVENTORY.WERKS
28/06/2016, 15h46
skuatamad

Citation:

Envoyé par StringBuilder

PS : Accessoirement, j'ai l'impression que votre table de départ est "material" et non "inventory".

Ah oui, c'est pas faux.
28/06/2016, 17h13
aieeeuuuuu

Bonjour,

Dans la requete que vous avez postée, tous les prédicats sauf un sont en commentaire...
Est-ce que vous parlez bien de la requete avec ces commentaires, ou en prenant en compte les prédicats commentés ? car certains (pour ne pas dire tous) sont très mal exprimés.

Si vous parlez de la requete avec uniquement le filtre sur la date, il serait intéressant de comparer les plan d’exécution, mais il doit manquer des statistiques (ou elles sont obsolètes) pour arriver à de telles différences.
29/06/2016, 11h42
oupepasa

Citation:

Envoyé par skuatamad

Sémantiquement parlant, il est étrange qu'il n'y ait pas de lien entre MATERIAL et MATERIAL_DETAIL. Le choix du nom des tables ne semble donc pas très pertinent.

il y a un lien , mais cela rajoute une jointure gauche pas sur que cela soit efficace

Citation:

Les clés étrangères sont-elles matérialisées ?

Non mais les clés primaires non plus, ma table est plus construite comme un datawarehouse, ce qui pourrait expliquer ces contre performances effectivement

Citation:

PS : Accessoirement, j'ai l'impression que votre table de départ est "material" et non "inventory".
PS² : J'ai cependant un léger doute quant à la seconde contrainte... werks ne serait-il pas nullable dans la table inventory par hasard ?

Que donne donc la clause FROM suivante ?

ps : a quoi voyez vous cela?
ps1 : werks ne peut pas etre null
La clause donne le meme résultat et à vrai dire c'était effectivement celle que ssrs m'a construite à la base

Citation:

Bonjour,

Dans la requete que vous avez postée, tous les prédicats sauf un sont en commentaire...
Est-ce que vous parlez bien de la requete avec ces commentaires, ou en prenant en compte les prédicats commentés ? car certains (pour ne pas dire tous) sont très mal exprimés.

Si vous parlez de la requete avec uniquement le filtre sur la date, il serait intéressant de comparer les plan d’exécution, mais il doit manquer des statistiques (ou elles sont obsolètes) pour arriver à de telles différences.

c'est bien la requete avec seulement ce filtre dont je parle

Je pense que ma solution (de facilité) sera d'ajouter ces champs au moments de la création de la table si je ne trouve rien de mieux
29/06/2016, 15h34
aieeeuuuuu

Citation:

Envoyé par oupepasa

Citation:

Les clés étrangères sont-elles matérialisées ?

Non mais les clés primaires non plus, ma table est plus construite comme un datawarehouse, ce qui pourrait expliquer ces contre performances effectivement

oui, il n'y a pas a chercher beaucoup plus loin

si les clef primaires ne sont pas déclarées, alors vous n'avez certainement pas d'index sur ces tables.
Par ailleurs, le moteur ne peut pas "savoir" que chaque ligne dans Material_Detail aura forcément une correspondance dans la table inventory; Il va donc s'appuyer sur des statistiques globales, ce qui peut tout à fait expliquer la différence de comportement entre les deux types de jointure.
29/06/2016, 15h39
aieeeuuuuu

Citation:

Envoyé par oupepasa

Je pense que ma solution (de facilité) sera d'ajouter ces champs au moments de la création de la table

A terme, cela risque d'empirer la situation globale et de vous mener a d'autres problèmes.

1/ Déclarez les clefs primaires et étrangères.
2/ indexez les clefs étrangères (ce sera fait automatiquement pour les clef primaires)
3/ créez un index sur inventory.DATE

tout devrait aller beaucoup mieux (il y aurait surement même moyen de faire mieux, mais ce sera déjà un bon début.)
29/06/2016, 17h08
oupepasa

le probleme c'est que je n'ai pas forcement une clé unique par table

en plus c'est une table qui est rechargé integralement tous les matins donc ca pose peu de probleme