Optimisation d'une requête [Résolu]

[lola06]

Bonjour,
J'aurai besoin d'aide pour l'optimisation d'une requête.

Table A

Code :

1
2
3
4
5
6
7
8
9
IdA | IdB
---------
 1  | c2
 2  | c1
 3  | f3
 4  | c4
 5  | e4
 6  | c2
 7  | d4

Table B

Code :

1
2
3
4
5
6
7
8
9
IdB | a | b 
------------
c2  | 1 | 2
c1  | 1 | 4
f3  | 1 | 6
c4  | 2 | 3
e4  | 6 | 5
c2  | 1 | 3
d4  | 4 | 4

Comme résultat je souhaiterai donc :

Code :

1
2
3
4
IdA | IdB | a | b 
------------
 2  | c1  | 1 | 4
 4  | c4  | 2 | 3

Voici ma requête :

Code :

1
2
3
4
5
6
7
8
9
10
SELECT *
FROM TableA A
 JOIN TableB B
 ON A.IdB = B.IdB
 AND IdB LIKE 'c%'
 
WHERE IdB NOT IN (SELECT IdB
     FROM TableB
     WHERE a = '1'
     AND b IN ('2','3','5'))

Or je manipule des millions d'enregistrements et je souhaiterai optimiser tout cela.
J'ai regardé le cours : http://sqlpro.developpez.com/cours/optimiser/#L9
Et la règle 10 me montre bien comment optimiser le "not in" mais j'avoue ne pas bien saisir le "LEFT OUTER JOIN".
Même après ce cours http://sqlpro.developpez.com/cours/s...ntures/#LIII-C j'ai quelques doutes quand à la construction de ma requête.
En effet je bloque avec la condition "AND IdB like 'c%'", comment dois-je agencer tout ça ?

Merci

[punkoff]

Bonjour,


Et plus simplement ?

Code :

1
2
3
4
5
 
SELECT *
FROM TableA A
 INNER JOIN JOIN TableB B ON A.IdB = B.IdB
WHERE b.IdB LIKE 'c%' AND b.a <> 1 AND b.b NOT IN ('2','3','5')

[asmduty]

Bonjour,

peux-tu essayer ceci :

Code :

SELECT * FROM TableA A JOIN TableB B ON (A.IdB = B.IdB AND IdB LIKE 'c%' AND a <> 1 AND b NOT IN ('2','3','5'))

EDIT : Devancé again ...

[lola06]

Merci pour vos réponses mais il me semble cette requête ne me renverrait rien pour mon jeu de test.
En effet elle excluerait tous les a = 1 et les b = 2,3,5.
Or je souhaite ici n'exclure que les couples (a,b) tels que (1,2), (1,3) et (1,5). D'où ma sous-requête.
Je suis actuellement en chomage technique donc je ne peux pas tester

[asmduty]

Exact !
Au temps pour moi

Code :

SELECT * FROM TableA A JOIN TableB B ON (A.IdB = B.IdB AND IdB LIKE 'c%' AND (a <> 1 OR b NOT IN ('2','3','5'))

[lola06]

Merci beaucoup.
Comme une andouille j'avais pas pensé au "OR" dans le join !!
J'attend de pouvoir tester avant de mettre résolu...

[pacmann]

Salut !

Attention, les requêtes qu'on te propose ne donnent pas le même résultat que ta requête initiale :
- Avec idb NOT IN (...), ça exclut tout les idb si un seul remplit la condition de ta sous-requête.
- En remplaçant la sous-requête par des conditions directes sur tes lignes, tu n'exclut que les occurences de idb qui remplissent ces conditions.

Côté performances, il faut le plan d'exécution avant tout, ainsi que les volumétries un peu plus précises genre combien de ligne c'est sensé te retourner.

Quant aux cours d'optimisation de SQLPro, attention, ça dépend du contexte, du SGBD, ...
La démarche pragmatique (du moins sur Oracle) c'est : lire le plan d'exécution, comprendre pourquoi c'est lent, imaginer comment ça pourrait l'être moins.

[punkoff]

La solution avec le OR est pourtant identique à la demande initiale (on avait juste oublie les principes de De Morgan! )

[pacmann]

Si tu changes un peu le jeu d'essai pour y ajouter la dernière ligne :

Code :

1
2
3
4
5
6
7
8
9
10
11
 
IdB | a | b 
------------
c2  | 1 | 2
c1  | 1 | 4
f3  | 1 | 6
c4  | 2 | 3
e4  | 6 | 5
c2  | 1 | 3
d4  | 4 | 4
c2  | 1 | 6

Alors c2 | 1 | 6 ressortira dans le résultat avec la solution en OR, mais pas avec la solution NOT IN.
Dans un cas tu appliques le critère direct sur les lignes, dans l'autre c'est plutôt sur des "groupes"

[punkoff]

Oula, passé a côté de ca ...
Je pensais qu'elle faisait son test sur un id autre.

Autant pour moi.

[lola06]

Envoyé par pacmann

Attention, les requêtes qu'on te propose ne donnent pas le même résultat que ta requête initiale :
- Avec idb NOT IN (...), ça exclut tout les idb si un seul remplit la condition de ta sous-requête.
- En remplaçant la sous-requête par des conditions directes sur tes lignes, tu n'exclut que les occurences de idb qui remplissent ces conditions.

Effectivement ma première solution est alors mauvaise .

Envoyé par pacmann

Côté performances, il faut le plan d'exécution avant tout, ainsi que les volumétries un peu plus précises genre combien de ligne c'est sensé te retourner.

Malheureusement je ne peux pas tester mes requêtes aujourd'hui --> chomage technique.

J'avais déjà essayé d'exéctuter un EXPLAIN PLAN mais impossible de modifier le paramètrage qui permettrait son fonctionnement.

La requête que je vous présente est juste une partie d'une requête. Sinon la requête que je vous met brasse plusieurs millions de lignes et doit en renvoyer moins de 200 000 lignes. Je ne peux pas être plus précise parce qu'en fait cette requête et une autre totalement indépendant doivent renvoyer en tout environ 200 000 lignes, mais je ne connais pas les proportions.

Sinon merci beaucoup pour tes explications pour le NOT IN, j'avais mal compris son fonctionnement.

[asmduty]

Fail again, j'avais pas pensé à ça punkoff

[pacmann]

L'explain plan, c'est important

Tu le lances avec quel outil ? C'est quoi le paramétrage qui passe pas ?

Sinon, ça tourne en combien de temps actuellement ?
200K lignes à retourner, c'est beaucoup... mais peut être que passer par un index pourrait te faire du bien

Si j'ai bien compris, ta version NOT IN était fausse au départ ? Dans ce cas si ça se trouve, sans le NOT IN ça passera tout seul

[lola06]

J'ai réussi à lancer ma requête sur la prod pendant la pause repas et actuellement ma requête met dans les 45 minutes, j'optimise à mort mais franchement je vois pas d'amélioration notables.
D'un autre côté c'est normal, la requête complete utilise 6 tables de chacune plusieurs millions de ligne.
Après je n'ai pas la possibilité de modifier la base de données pour faire un index, je peux toujours en parler autour de moi.
Est-ce qu'il existe une méthode pour savoir si des index existent déjà ?

J'utilise TOAD et j'avais vu que l'explain plan pouvait bloquer et que pour cela il fallait changer le nom de la table cible dans le paramétrage TOAD (or ça je peux pas le faire). Je vais essayer de retrouver le lien.

Pour ma version not in, c'était faux effectivement, mais je n'avais pas pu la tester, je pensais que le not in renverrais les Id correspondants à mes conditions et que ça agirrai comme un filtre. J'avoue qu'avec des requêtes à rallonge on peut s'y perdre

[punkoff]

Mais c'est quoi le plan d'execution de votre requête ?

Pour les indexs dans toad vous pouvez les voir en faisant une description de vos tables.

Sinon vous avez aussi la vue user_indexes.


Dans un premier lieu vérifiez que vous avez des index sur chacuen de vos jointures, après il faudra peut-être couvrir les restrictions dans la clause "where".

Quel est l'utilité d'extraire 200k lignes ? vous allez faire quel type de traitement avec ?

[lola06]

Envoyé par punkoff

Mais c'est quoi le plan d'execution de votre requête ?

Pour l'instant je n'ai plus accès à mes bases de recette et hors de question d'aller sur la prod en horaires administratives. Dès que je peux je m'y remet.

Envoyé par punkoff

Quel est l'utilité d'extraire 200k lignes ? vous allez faire quel type de traitement avec ?

Ceci malheureusement n'est pas de mon ressort, je sais que je dois sortir les enregistrements vérifiants un certain nombre de critères pour lancer des traitements dessus par la suite.

[tatayo]

Bonjour,
Et avec une jointure externe, est-ce que ça va plus vite ?

Code SQL :

1
2
3
4
5
6
7
8
 
SELECT *
FROM TableA A
 JOIN TableB B
 ON A.IdB = B.IdB
 AND b.IdB LIKE 'c%'
 LEFT OUTER JOIN TableB AS C ON C.idb = b.idb AND C.a = '1' AND C.b IN ('2','3','5')
WHERE C.idb IS NULL

Tatayo.

[lola06]

Nouvelle formulation de la requête par les demandeurs :
Jeu de test :

Code :

1
2
3
4
5
6
7
8
9
IdA | IdB 
----------
 1  | c2  
 1  | c1  
 2  | f3  
 3  | c4  
 3  | e4  
 3  | c5  
 4  | d4

Code :

1
2
3
4
5
6
7
8
9
 IdB | a | b 
-------------
 c2  | 1 | 2
 c1  | 1 | 4
 f3  | 1 | 6
 c4  | 2 | 3
 e4  | 6 | 5
 c5  | 1 | 4
 d4  | 4 | 4

Résultat souhaité :

Code :

1
2
3
4
IdA | IdB | a | b 
------------------
 3  | c4  | 2 | 3
 3  | c5  | 1 | 4

Je dois retourner seulement les IdA qui respectent ces conditions pour tous leurs IdB.
(on voit bien la différence avec IdA = 1 qui ne les respectent pas, on ne renvoi pas la ligne suivante)

Voici ma nouvelle requête :

Code :

1
2
3
4
5
6
7
SELECT *
FROM TableA A
 JOIN (SELECT idB, a, b
   FROM TableB
   WHERE IdB LIKE 'c%'
   AND (a <> 1 OR b NOT IN (2,3,5))) B
 ON A.IdB = B.IdB

J'ai pas pu la tester (encore). Pensez-vous qu'il est possible de l'optimiser ?
Perso j'ai vraiment du mal, rien que la construction de la requête correcte est pas facile (difficulté de communication/compréhension des demandes).

[lola06]

Envoyé par tatayo

Bonjour,
Et avec une jointure externe, est-ce que ça va plus vite ?

Code SQL :
SELECT
FROM TableA A
JOIN TableB B
ON A.IdB = B.IdB
AND b.IdB LIKE 'c%'
LEFT OUTER JOIN TableB AS C ON C.idb = b.idb AND C.a = '1' AND C.b IN ('2','3','5'
WHERE C.idb IS null

Malheureusement pour le moment je ne peux toujours pas tester.
Par contre je veux bien que tu m'explique pourquoi tu met "IdB IS NULL".

[lola06]

Est-il possible d'écrire :

Code :

AND (a <> 1 OR b NOT IN (2,3,5))

sous la forme :

Code :

AND NOT (a = 1 AND b IN (2,3,5))

J'ai pas réussi à trouver et comme je peux rien tester