[Optimisation] Liste des possibles non existants (Exclusion des existants)Résolu

Question

Bonjour,

Le titre est assez obscur, mais voici ce que j'entends :

La table : SELECT SalesReasonID, Name FROM Sales.SalesReason
SalesReasonID | Name	
1             | Price
2             | On Promotion
3             | Magazine Advertisement
4             | Television Advertisement
5             | Manufacturer
6             | Review
7             | Demo Event
8             | Sponsorship
9             | Quality
10            | Other
Et la table : SELECT SalesOrderID, SalesReasonID FROM Sales.SalesOrderHeaderSalesReason
SalesOrderID | SalesReasonID
43697        | 5
43697        | 9
43702        | 5
43702        | 9
... (27647 enregistrements)

Ce que je cherche à obtenir :
SalesOrderID | SalesReasonID | Name
43697        | 1             | Price
43697        | 2             | On Promotion
43697        | 3             | Magazine Advertisement
43697        | 4             | Television  Advertisement
43697        | 6             | Review
43697        | 7             | Demo Event
43697        | 8             | Sponsorship
43697        | 10            | Other
43702        | 1             | Price
43702        | 2             | On Promotion
43702        | 3             | Magazine Advertisement
43702        | 4             | Television  Advertisement
43702        | 6             | Review
43702        | 7             | Demo Event
43702        | 8             | Sponsorship
43702        | 10            | Other
...

C'est-à-dire que je veux la liste de ce qui manque.
Je veux exclure ce que j'ai déjà.

-----------------------------------------------

Pour le moment, j'ai les requêtes suivantes :
SELECT sohsr.SalesOrderID, sr.SalesReasonID, sr.Name
  FROM Sales.SalesOrderHeaderSalesReason sohsr
CROSS JOIN Sales.SalesReason sr
EXCEPT
SELECT sohsr.SalesOrderID, sr.SalesReasonID, sr.Name
  FROM Sales.SalesOrderHeaderSalesReason sohsr
INNER JOIN Sales.SalesReason sr On sohsr.SalesReasonID = sr.SalesReasonID
ORDER BY sohsr.SalesOrderID, sr.SalesReasonID
ou 
SELECT DISTINCT sohsr.SalesOrderID, sr.SalesReasonID, sr.Name
  FROM Sales.SalesOrderHeaderSalesReason sohsr
CROSS JOIN Sales.SalesReason sr
WHERE sr.SalesReasonID NOT IN (SELECT s2.SalesReasonID FROM Sales.SalesOrderHeaderSalesReason s2 WHERE s2.SalesOrderID = sohsr.SalesOrderID)
ORDER BY sohsr.SalesOrderID, sr.SalesReasonID

-----------------------------------------------

Je cherche donc la requête qui me permettra de récupérer ma liste des manquants le plus rapidement possible en prévision d'un traitement sur une base de données plus volumineuse et plus solllicitée qu'une instance locale de SQL SERVER 2008.
Je sais que certains éléments peuvent être couteux (IN, DISTINCT, CROSS), mais ces 2 requêtes sont équivalentes (4 secondes avec le ORDER BY).

Pouvez-vous m'indiquer s'il existe une méthode plus performante ?

Merci d'avance,

tri_yann · Accepted Answer

Bonjour, 

Voici la requête que j'utiliserai :

SELECT A.*
FROM
(SELECT A.SalesOrderId, B.SalesReasonID, B.Name
FROM
(SELECT Distinct A.SalesOrderId FROM Sales.SalesOrderHeaderSalesReason) A 
CROSS JOIN  Sales.SalesReason B
) C --Produit cartésien SalesOrderID / SalesReasonID 
LEFT JOIN Sales.SalesOrderHeaderSalesReason D
ON C.SalesOrderId D.SalesOrderId and C.SalesReasonID D.SalesReasonID 
WHERE D.SalesOrderId is null
ORDER BY A.SalesOrderId, A.SalesReasonID


Pour une meilleure performance :
le champs SalesReasonID de la table Sales.SalesReason doit être indexé voir primary key.
Les champs SalesOrderID & SalesReasonID de la table Sales.SalesOrderHeaderSalesReason doivent être index clustered (unique ?). Auquel cas, je pense que la clause Order by ne sert plus à rien. Même sur une grosse table Sales.SalesOrderHeaderSalesReason (100 000 enr. par ex), je pense qu'il est possible d'être à la seconde. 
Désolé, je n'ai pas l'occasion de tester mon code. Si erreur, reviens vers moi

Yann

jpvito · Answer

Bonjour,

Merci, cette requête est effectivement plus performante et j'apprécie particulièrement la petite note explicative.

Après test, la requête est plutôt celle-ci :SELECT C.*
  FROM (SELECT A.SalesOrderId, B.SalesReasonID, B.Name
  FROM (SELECT Distinct SalesOrderId FROM Sales.SalesOrderHeaderSalesReason) A
CROSS JOIN  Sales.SalesReason B
) C --Produit cartésien SalesOrderID / SalesReasonID 
LEFT JOIN Sales.SalesOrderHeaderSalesReason D ON C.SalesOrderId D.SalesOrderId and C.SalesReasonID D.SalesReasonID 
 WHERE D.SalesOrderId is null
 ORDER BY C.SalesOrderId, C.SalesReasonID

Merci.

Cordialement,

[Optimisation] Liste des possibles non existants (Exclusion des existants)

2 réponses

Votre réponse

Discussions similaires