Vitesse d'une requête (problème de jointures externes complexes)

**pepper** · 23/07/2007, 13h55

Bonjour à tous,

Je suis en train de concevoir une application VB6 qui sert à programmer de la musique sur une journée, en fonction de certains critères.
Pour chaque morceau programmé, l'appli fait une requête SQL sur une base access, en fonction de ce qui a été programmé précédemment.
L'accès aux données se fait par ADO.
L'ensemble fonctionne, mais chaque requête est extrêmement longue !
Quelqu'un saurait-il si c'est lié à la nature des requêtes ?

Voici mon code, pour plus de précision :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
SELECT Disco.Num, Disco.Numartiste, Disco.titre, Disco.NumCat, Disco.Derdiff, 
Disco.Duree, Artistes.NomArtiste, Genres.NomGenre 
FROM Disco, Artistes, Genres WHERE Disco.NumArtiste = Artistes.IDA 
AND Disco.NumGenre = Genres.IDG AND NumCat = 3 AND Disco.Num NOT IN 
(SELECT ID FROM Prog, Disco WHERE Prog.ID = Disco.Num AND 
Abs(Datediff('n', Prog.Diff, #06:00:00#)) < Disco.EcartTitre) 
AND Disco.NumArtiste NOT IN 
(SELECT Disco.NumArtiste FROM Prog, Disco WHERE Disco.Num = Prog.ID 
AND Disco.NumArtiste <> 105 
AND Abs(Datediff('n', Prog.Diff, #06:00:00#)) < Disco.EcartArtiste) 
AND Disco.NumGenre NOT IN 
(SELECT Disco.NumGenre FROM Prog, Disco, Genres 
WHERE Disco.NumGenre = Genres.IDG AND Disco.Num = Prog.ID 
AND Abs(Dateadd('n', Prog.Diff, #06:00:00#< Genres.Separation) 
AND Disco.Num NOT IN (SELECT ID FROM Prog 
WHERE Format(Prog.Diff, 'dd/mm/yyyy')= #24/06/2007# 
AND Abs(Datediff('n', Format(Prog.Diff, 'hh:nn:ss'), #06:00:00#)) < 180) 
ORDER BY DerDiff ;

Pour traduire en français, je sélectionne à chaque fois dans la discothèque, un morceau :
- de telle ou telle catégorie (ici '3')
- qui n'a pas été diffusé à moins d'un certain écart (en minutes) de l'heure dite (ici '06:00:00')
- dont le Genre est séparé d'un certain écart des morceaux du même genre
- qui n'a pas été diffusé la veille (ici 24/06/2007) à moins d'un certain écart (180) de la même heure
- ordonné par la dernière diffusion en date

**al1_24** · 23/07/2007, 14h21

Tu n'as pas essayé d'exécuter ta requête en dehors du programme ?

**pepper** · 23/07/2007, 14h32

J'ai bien essayé, oui ... ça me laisse croire effectivement que la requête y est pour beaucoup. Dans access, c'est également long (2 ou 3 secondes)

**Gaetch** · 23/07/2007, 14h47

lol ben oui tu m'étonnes avec 4 NOT IN c'est un peu normal ! Tu peux pas faire plutot avec des jointures ? parce que là pour chaque ligne, ça refait 4 requêtes c'est normal que ce soit long.

**Gaetch** · 23/07/2007, 14h59

Tiens j'ai un peu cherché dans les cours en ligne y'a quelqu'un qui parle de ça : http://sql.developpez.com/optimiser/. Ca dit bien d'éviter les sous-requêtes qund tu peux utiliser les jointures et sinon de préférer EXIST à IN.

**pepper** · 23/07/2007, 15h33

euh .... je dois mal m'y prendre.
Mais franchement, je ne vois pas comment faire une jointure dans ce cas précis. (Et ne pas mettre de sous requête)

Grosso modo, j'ai deux tables principales. Une table Disco qui contient toute ma discothèque (nom de l'artiste, titre du morceau, écart avec le même morceau, écart avec le même artiste, Genre, Catégorie, etc...), et une table Prog dans laquelle j'inscris à chaque requête un nouveau morceau programmé (N° ID, Date et Heure de la diffusion).

Donc à chaque requête, je fais la liste des ID qu'il ne peut pas choisir et je lui demandes de ne pas piocher dedans.
D'où le NOT IN.

Je pourrai donc remplacer le NOT IN par NOT EXIST, mais je ne suis pas sûr que ça accélère des masses !?

**Gaetch** · 23/07/2007, 15h51

Et ça veut dire quoi un morceau qu'il peut pas choisir ? (C'est quoi ton critère pour dire qu'un morceau peut être choisi ou pas)

**Gaetch** · 23/07/2007, 15h57

Je pense que ça peut aller plus vite en ajoutant temporairement une colonne dans la table ou tu choisis tes chansons, de la remplir par défaut avec une valeur et de changer la valeur qund le morceau doit plus être choisi. Ensuite pour tester, t'auras qu'a faire un test avec un WHERE et tu gagneras beaucoup de temps... je pense.

**pepper** · 23/07/2007, 16h06

Voici en gros à quoi ressemble mes tables :

Disco:
ID MORCEAU : 212
ID ARTISTE : 371 (ARTISTE : MICKEY MOUSE)
Titre : Vive le bonheur
Ecart Artiste : 180 (il ne peut pas y avoir moins de 180 min entre 2 morceaux de MICKEY MOUSE)
Ecart Titre : 360 (il ne peut pas y avoir moins de 360 min entre 2 diffusions de "Vive le bonheur")
Genre : 2 (Rock'n'roll)
etc...

Prog :
ID MORCEAU : 212
Diffusion : #24/06/2007 13:31#

Donc, si j'en suis à 15:00, par exemple, la liste des morceaux qui vont pouvoir être choisis va être dressée en regardant tous les morceaux dans la table Disco, et de comparer avec les diffusions dans la table Prog.

Exemple, pour Mickey Mouse, il va regarder :
1) Critère 1 : l'écart artiste. Or, il y a une diffusion à 13:31 (Moins de 180 minutes, pas bon.
2) critère 2 : l'écart titre. Idem, diffusion à 13:31 (Moins de 360, pas bon)
Ensuite, il passe au morceau suivant de la discothèque.
Tout ceci dans la même requête, d'où la difficulté de la rédaction...

**pepper** · 23/07/2007, 16h12

Si je te suis, tu me conseilles de calculer l'écart pour chaque morceau avec la dernière diffusion de l'artiste ou du morceau et de mettre 0 ou 1 dans une colonne "Diffusion Autorisée" grâce à une requête UPDATE ?
Par contre, il faut quand même que je fasses un select après, donc, ça me fait toujours deux requêtes ...

**Gaetch** · 23/07/2007, 16h30

Je pense que ça ira beaucoup plus vite quand même. Parce que toi tu fais un SELECT et puis sur chaque ligne tu refais encore 4 SELECT.

Fais un truc genre des que tu joue un morceau de l'artiste BIDULE, tu fais un UPDATE DISCO SET PROCH_DIFF = HEURE_SYSTEME (connais pas la fonction) + 180min.

Après pour avoir la liste des pistes diffusables, t'auras qu'a faire SELECT ID_MORCEAU FROM DISCO WHERE PROCH_DIFF < HEURE_SYSTEME.

Ha faudrait aussi initialiser PROCH_DIFF à HEURE_SYSTEME quand tu lance l'application, sinon quand tu feras SELECT ID_MORCEAU FROM DISCO WHERE PROCH_DIFF < HEURE_SYSTEME ça prendra pas les morceaux qui auront PROCH_DIFF à NULL (par défaut).

**pepper** · 23/07/2007, 17h24

Je te suis tout à fait, mais ça n'est pas aussi simple :il s'agit d'une programmation à l'avance et pour une date / heure donnée et non de temps réel.
En plus, il faut éventuellement pouvoir revenir sur le morceau programmé à 15h00 et l'échanger contre un autre dans une liste, même si on a déjà programmé jusqu'à 23:59.

Ta 1ère solution avec les jointures me semble plus viable, mais j'ai encore de gros soucis de syntaxe ... J'ai testé, c'est BEAUCOUP plus rapide (nickel, en fait), mais ... ça ne respecte pas les critères ... J'ai dû me planter quelque part...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SELECT DISTINCT Disco.Num, Disco.Numartiste, Disco.titre, Disco.NumCat, 
Disco.Derdiff, Disco.Duree, Artistes.NomArtiste, Genres.NomGenre 
FROM ((Disco INNER JOIN Genres ON Disco.NumGenre = Genres.IDG) INNER JOIN 
Artistes ON Disco.NumArtiste = Artistes.IDA)   LEFT OUTER JOIN 
Prog ON Disco.Num = Prog.ID 
AND (Disco.NumArtiste <>285 
AND Abs(Datediff('n', Prog.Diff, #23/07/2007 02:35:41#)) < Disco.EcartArtiste)  
AND Abs(Datediff('n', Prog.Diff, #23/07/2007 02:35:41#)) < Disco.EcartTitre
WHERE NumCat =1
AND Prog.ID IS NULL
ORDER BY DerDiff ;

**Gaetch** · 23/07/2007, 17h34

Mais attends, pourquoi tu fais pas ça au niveau client plutot qu'en passant par le SQL au fait ? Oui sinon les jointure ça speede bien moi aussi en ce moent je suis entrain de développer un truc et j'étais passé par de multiples SELECT, j'ai fait plusieurs jointures à la place et ça va beaucoup plus vite. Par contre j'utilise que des LEFT JOIN. Sinon c'est pas grave si c'est pas en temps réel tu remplace HEURE_SYSEME par ton heure de départ.

**Gaetch** · 23/07/2007, 17h52

Dans ta table PROGRAMMATION tu renseigne la date de diffusion du morceau, ben des que tu mets un morceau tu vas dans la table DISCO, colonne PROCH_DIFF faire un UPDATE sur tous les morceaux du même artiste, tu mets PROCH_DIFF à date de diffusion du morceau + ton ecartArtiste. Après tu fais un update que sur le même morceau tu mets sa PROCH_DIFF à date de diffusion du morceau + ton ecartTitre.

Après pour sélectionner la liste des titres diffusables tu fais :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT Num, Numartiste, Titre, NumCat, Derdiff, Duree, NomArtiste, NomGenre
FROM
    Disco
    LEFT JOIN Genres ON NumGenre = IDG
    LEFT JOIN Artistes ON NumArtiste = IDA
    LEFT JOIN Prog ON Num = ID
WHERE ProchDiff < Date de commencement du dernier morceau diffusé + Durée du dernier morceau + quelques secondes de marge

Pourquoi fqire un ORDER BY (sache que ça ralentit la requête) ?

**pepper** · 23/07/2007, 18h12

J'ai envisagé cette éventualité (même si je n'avais pas ton code, qui j'avoue est beaucoup plus simple que tout ce que j'aurais pu trouver ...), MAIS ...
L'idée de Proch_diff ne peut pas marcher.
Il faut absolument que je reparte à chaque fois de toutes les dates de diffusion, parce que l'écart est valable pour les morceaux que l'on va programmer APRES, mais aussi pour ceux que l'on va programmer AVANT.
Un exemple, dans ma table Prog, j'ai :

13:56 : X
14:15 : Y
15:28 : Z
17:32 : B
18:15 : X

Je dois pouvoir, au dernier moment, décider de changer Z.
Je clique sur l'élément Z dans ma grille (composant quelconque alimenté par la base de données), et là, une nouvelle fenêtre apparait, pour me proposer une liste de remplacement, grâce à ... toujours la même requête !
Mais dans cette liste, il ne pourra pas y avoir Y (trop peu d'écart), ou même X qui est prévu avant ET après.
La requête doit pouvoir toujours regarder avant et après.

Je crois que le code que j'ai proposé juste avant n'est pas loin du but, mais mes clauses JOIN sont pourries. (J'ai pas du tout comprendre)

Merci pour ton idée en tous cas !

Au fait, l'ORDER BY, c'est pour prendre le morceau le plus anciennement diffusé...

**Gaetch** · 24/07/2007, 16h00

Okay, c'est bien tendu ton truc quand même ! Ben là je vois pas trop de solution par contre si tu trouves, ça m'intéresse de savoir comment tu fais et si tu sais pas comment faire une requête ou si tu veux de l'aide je t'aiderai. Tu le fais pour toi perso ce programme ?

**pepper** · 24/07/2007, 16h19

Merci de ton aide, tes conseils sont précieux en tous cas.

Voici ce que j'ai trouvé pour l'instant. J'utilise dans cette solution un peu de
jointure et un peu de NOT EXISTS.
Au niveau rapidité, c'est tout à fait convenable, mais je ne suis pas sûr du respect des critères :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
SELECT D1.Num, D1.Numartiste, D1.titre, D1.NumCat, D1.Derdiff, 
D1.Duree, MIN(Abs(DateDiff('n',P1.Diff, #24/07/2007 04:35:41#))), 
Artistes.NomArtiste, G1.NomGenre
FROM ((Disco AS D1 INNER JOIN Genres AS G1 ON D1.NumGenre = G1.IDG) 
INNER JOIN Artistes ON D1.NumArtiste = Artistes.IDA) LEFT JOIN Prog AS P1 
ON (Abs(Datediff('n', P1.Diff,#24/07/2007 04:35:41#)) > D1.EcartTitre) 
AND (D1.Num= P1.ID) 
 
WHERE NOT EXISTS 
(SELECT * FROM Prog P3, Disco D3 WHERE D3.NumArtiste = P3.NumA
AND D3.NumArtiste <> 285
AND D1.Num = D3.Num 
AND Abs(Datediff('n', P3.Diff, #23/07/2007 04:35:41#)) < D3.EcartArtiste)
 
AND NOT EXISTS
(SELECT * FROM Prog P4 
WHERE P4.ID = D1.Num
AND Format(P4.Diff, 'dd/mm/yyyy')= #23/07/2007# 
AND Abs(Datediff('n', Format(P4.Diff, 'hh:nn:ss'), #06:00:00#)) < 180) 
 
 
AND  NumCat = 1 AND P1.ID IS NULL
GROUP BY D1.Num, D1.NumArtiste, D1.Titre, D1.NumCat, D1.Derdiff, D1.Duree, Artistes.NomArtiste, G1.NomGenre
ORDER BY 1/MIN(Abs(DateDiff('n',P1.Diff, #24/07/2007 04:35:41#)));

En fait, dans ma première requête (principale) je fais une jointure entre la table DISCO et la table PROG pour avoir la liste de tous les morceaux, en créant un champ avec l'écart en minute avec l'heure donnée (comme il peut y avoir plusieurs occurences du même morceau, je prends l'écart le plus petit, d'où le MIN).

De cette requête, je ne garde évidemment que les lignes ou l'écart est assez grand (> EcartTitre). Je fais le tri grâce à WHERE P1.ID IS NULL.

Ensuite, j'ai deux sous-requêtes, et c'est là que je ne suis pas sûr de leur efficacité. Je vérifie pour chacune d'elle que chaque morceau respecte bien le critère :
1) d'écart avec la diffusion du même artiste
2) d'écart avec la diffusion du même morceau la veille

**Gaetch** · 24/07/2007, 16h42

Je remet juste un peu en forme car j'ai un peu du mal à lire et à suivre !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
SELECT D1.Num, D1.Numartiste, D1.titre, D1.NumCat, D1.Derdiff, 
D1.Duree, MIN (Abs (DateDiff ('n', P1.Diff, '24/07/2007 04:35:41'))), 
Artistes.NomArtiste, G1.NomGenre
 
FROM
    Disco D1
    JOIN Genres G1 ON D1.NumGenre = G1.IDG 
    JOIN Artistes ON D1.NumArtiste = Artistes.IDA
    LEFT JOIN Prog AS P1 ON (Abs (Datediff ('n', P1.Diff, '24/07/2007 04:35:41')) > D1.EcartTitre)
 
WHERE D1.Num = P1.ID
AND NOT EXISTS 
    (
    SELECT *
    FROM Prog P3, Disco D3
    WHERE D3.NumArtiste = P3.NumA
    AND D3.NumArtiste <> 285
    AND D1.Num = D3.Num
    AND Abs (Datediff ('n', P3.Diff, '23/07/2007 04:35:41')) < D3.EcartArtiste
    )
 
AND NOT EXISTS
    (
    SELECT *
    FROM Prog P4
    WHERE P4.ID = D1.Num
    AND Format (P4.Diff, 'dd/mm/yyyy') = '24/06/2007'
    AND Abs (Datediff ('n', Format (P4.Diff, 'hh:nn:ss'), '06:00:00')) < 180
    )
 
AND NumCat = 1
AND P1.ID IS NULL
 
GROUP BY D1.Num, D1.NumArtiste, D1.Titre, D1.NumCat, D1.Derdiff, D1.Duree, Artistes.NomArtiste, G1.NomGenre
ORDER BY 1 / MIN (Abs (DateDiff ('n', P1.Diff, '24/07/2007 04:35:41')))

Et j'en profite pour te dire en passant que ta requête est de taille conscéquente ! Je réfléchis un peu !

**Gaetch** · 24/07/2007, 16h44

Tu veux pas filer le nom et la structure de tes tables avec les attributs ? Si t'avais un shéma ce serait nikel !

**pepper** · 24/07/2007, 17h16

Bien sûr !

Table Disco
Num (Numéroauto)
NumSon (Numérique)
Titre (Texte)
NumArtiste (Numérique, correspond à IDA de la table Artistes)
NumCat (Numérique, correspond à IDC de la table Catégories)
Duree (Numérique)
EcartTitre (Numérique)
EcartArtiste (Numérique)
NumGenre (Numérique, correspond à IDG de la table Genres)

Table Prog
Num (Numéroauto)
ID (Numérique, correspond au Num de la table Prog)
Diff (Date/Heure ---> Date et heure de Diffusion)
NumA (copie de l'ID artiste)
NumG (copie de l'ID Genre)
NumC (copie de l'ID Catégorie)

Table Artistes
IDA (Numéroauto)
NomArtiste (Texte)

Table Catégories
IDC (Numéroauto)
NomCat (Texte)

Table Genres
IDG (Numéroauto)
NomGenre (Texte)
Separation (Numérique : temps de séparation entre deux morceaux du même genre)

Vitesse d'une requête (problème de jointures externes complexes)

Langage SQL

Discussions similaires

Partager

Partager