Discussion :

[Mizar31]

Bonsoir a tous,

Voici un petit problème tout bête qui malgré tout me fait cogiter depuis de nombreuses heures.
Pour illustrer mon problème, disons que j'ai une table T_Recette :

Recette / Ingredients
A / Beurre
A / Farine
A / Levure
B / Beurre
B / Chocolat
C / Huile
C / Parmesan

et je cherche a afficher uniquement les recettes ne comportant ni Beurre, ni Farine ; donc la recette C dans l'exemple.
J'ai donc testé cette requete :

Code :

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

SELECT Recette FROM T_Recette WHERE Recette NOT IN (SELECT DISTINCT Recette FROM T_Recette WHERE Ingredients IN ('Beurre','Farine'));

Mais cette recette ne cuit pas comme il faut et la requete 'tourne' pendant plus de 30mn.
Donc si vous avez une idée pour m'aider à optimiser cela sachant que j'ai quelques milliers de lignes dans cette table et
que je ne peux ni modifier les index ni créer de table temporaire.

En attendant vos suggestions... Bien cordialement

[Invité]

Code :

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

1
2
3
SELECT DISTINCT [recette] FROM [dbo].[recettes]
EXCEPT 
SELECT DISTINCT [recette] FROM [dbo].[recettes] WHERE ingredient IN ('Beurre','Farine')

Sinon ta requête est bonne avec un distinct en plus :

Code :

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

1
2
3
4
5
6
7
SELECT DISTINCT Recette 
FROM [dbo].[recettes] 
WHERE Recette NOT IN (
	SELECT DISTINCT Recette 
	FROM [dbo].[recettes] 
	WHERE Ingredient IN ('Beurre','Farine') 
	)

[pcaboche]

Mais cette recette ne cuit pas comme il faut et la requete 'tourne' pendant plus de 30mn.
Donc si vous avez une idée pour m'aider a optimiser cela sachant que j'ai qques milliers de lignes dans cette table et
que je ne peux ni modifier les index ni creer de table temporaire.

Je pense que votre BDD a un problème de modélisation, mais vous ne pouvez pas modifier celle-ci.

Pour optimiser un peu, on peut combiner l'opération de filtre (ni beurre, ni farine) et l'opération "DISTINCT" en une seule :

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT [recette]
FROM [dbo].[recettes]
GROUP BY [recette]
HAVING 
  AVG(
    CASE WHEN [Ingredient] IN ('Beurre', 'Farine')
    THEN 0
    ELSE 1
    END
  ) = 1

[Lyche]

Il y a plusieurs façon pour obtenir le résultat.

Un DISTINCT est généralement consommateur. En mettre 2 dans la même requête, si celle d'origine a déjà des problèmes, c'est pas forcément mieux. Le HAVING me semble être une bonne solution.

Sinon, je suis un partisan du EXISTS/NOT EXISTS

Code sql :

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

1
2
3
4
5
6
7
 
SELECT R1.Recette
  FROM T_Recette R1
 WHERE NOT EXISTS ( SELECT NULL
                      FROM T_Recette R2
                     WHERE R2.Ingredients IN ('Beurre','Farine')
                       AND R2.Recette = R1.Recette );

mais il n'est pas certains que les performances soient plus au rendez-vous qu'avec un NOT IN

Cordialement,

[StringBuilder]

Aucun intérêt de mettre un DISTINCT dans un NOT IN. Le SGBD va passer plus de temps à le mettre en place qu'à skipper des données déjà retirées du jeu de résultat...

[Lyche]

Pourtant c'est ce qu'il y a dans la requête d'origine et dans ta reprise

Mais oui, il est claire que c'est inutile de mettre un distinct dans une sous requête!

[StringBuilder]

Ma reprise ?

[Lyche]

-_- je suis fatigué je crois, je sais pas pourquoi j'ai vu ton pseudo à la place de celui de 7gyY9w1ZY6ySRgPeaefZ (c'est un copié collé ) qui lui a repris le DISTINCT dans la sous requête...

désolé -_-

[Mizar31]

Bonsoir à tous et merci pour vos suggestions.

J'avais deja testé l'utilisation de EXCEPT dans la requete mais malheureusement cela n'ameliore pas les perf de la requete.

Me reste donc a tester les idées de pcaboche et de Lyche... Je vous tiendrai au courant.

En tous cas, un grand merci a vous tous.

Bien cordialement

[pcaboche]

L'utilisation de EXCEPT dans la requete ne me donne malheureusement par de meilleures perf lors de l'execution de la requete.

Et combien de temps met la requête :

Code :

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

SELECT [recette] FROM [dbo].[recettes] WHERE [ingredient] IN ('Beurre', 'Farine')

?

Parce que si rien que cette partie là prend du temps (par manque d'index ou autre), il ne faut pas s'attendre à un miracle...

Même question avec un "DISTINCT" en plus.

[Mizar31]

Et combien de temps met la requête :

Code :

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

SELECT [recette] FROM [dbo].[recettes] WHERE [ingredient] IN ('Beurre', 'Farine')

?

Parce que si rien que cette partie là prend du temps (par manque d'index ou autre), il ne faut pas s'attendre à un miracle...

Même question avec un "DISTINCT" en plus.

Alors en effet, si je cherche de meilleures perf pour mon exclusion 'Farine-Beurre', c'est la requete pour selectionner ces ingredients me retourne un resultat de pres de 10 000 'recettes' en moins de 15 secondes. D'ou le potentiel pour optimiser la premiere requete qui met pratiquement 1/2H.

[StringBuilder]

Bonjour,

Avant de se poser la question des performances de la requête, je vous invite à vous poser la question de la modélisation de la base.

En effet, si je comprends bien :
- vous avez des doublons sur des valeurs littérales
- il n'y a pas de table de référence
- il n'y a pas de table associative

Bref, les règles élémentaires de la modélisation ne sont pas respectées.
Vous créez votre table telle qu'elle dans Access, et ce dernier va vous proposer de l'éclater en 3 tables, signe que même ce "SGBD" très mal vu dans le monde de la programmation est capable de modéliser mieux que vous cette base de données : c'est vous dire si on part de loin.

Je vous invite donc à adopter une modélisation de ce genre :

- Une table RECETTE (contient UNE ligne par recette)
- Une table INGREDIENT (contient UNE ligne par ingrédient)
- Une table associative RECETTE_INGREDIENT (contient autant de lignes qu'il y a d'ingrédient dans chaque recette), mais ne contient que des clé étrangères et non des valeurs littérales.

Code sql :

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
 
create table recette
(
    id int primary key not null,
    nom varchar(50) not null
);
go
 
create unique index uix_recette on recette (nom);
go
 
create table ingredient
(
    id int primary key not null,
    nom varchar(50) not null
);
go
 
create unique index uix_ingredient on ingredient (nom);
go
 
create table recette_ingredient
(
    recette_id int not null references recette(id),
    ingredient_id int not null references ingredient(id)
    primary key (recette_id, ingredient_id)
);
go
 
-- Pas convaincu de ce second index : à ne placer que si VRAIMENT la requête finale met plus d'une seconde à tourner
create unique index uix_recette_ingredient on recette_ingredient (ingredient_id, recette_id);
go

Ensuite, votre exemple se transforme en :

Code sql :

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
 
insert into recette (id, nom) values (1, 'A');
insert into recette (id, nom) values (2, 'B');
insert into recette (id, nom) values (3, 'C');
 
insert into ingredient (id, nom) values (1, 'Beurre');
insert into ingredient (id, nom) values (2, 'Farine');
insert into ingredient (id, nom) values (3, 'Levure');
insert into ingredient (id, nom) values (4, 'Chocolat');
insert into ingredient (id, nom) values (5, 'Huile');
insert into ingredient (id, nom) values (6, 'Parmesan');
 
insert into recette_ingredient (recette_id, ingredient_id) values (1, 1);
insert into recette_ingredient (recette_id, ingredient_id) values (1, 2);
insert into recette_ingredient (recette_id, ingredient_id) values (1, 3);
insert into recette_ingredient (recette_id, ingredient_id) values (2, 1);
insert into recette_ingredient (recette_id, ingredient_id) values (2, 4);
insert into recette_ingredient (recette_id, ingredient_id) values (3, 5);
insert into recette_ingredient (recette_id, ingredient_id) values (3, 6);

Et votre requête peut alors s'écrire :

Code sql :

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

1
2
3
4
5
6
7
8
9
 
select r.id, r.nom
from recette r
where r.id not in (
  select ri.recette_id
  from ingredient i
  inner join recette_ingredient ri on ri.ingredient_id = i.id
  where i.nom in ('Farine', 'Beurre')
);

Et vous verrez que la requête va tout d'un coup être... immédiate (je parie sur moins d'une seconde, à volumétrie égale).

[StringBuilder]

Et je confirme niveau performances :

50000 recettes
100 ingrédients

Une moyenne de 10 ingrédients par recette

Code sql :

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
 
declare @i int;
declare @j int;
 
set @i = 1;
while @i <= 100
begin
    insert into ingredient (id, nom) values (@i, 'Ingredient ' + cast(@i as varchar));
	set @i = @i + 1;
end;
 
set @i = 1;
while @i <= 50000
begin
    insert into recette (id, nom) values (@i, 'Recette ' + cast(@i as varchar));
 
	set @j = 1;
	while @j <= 10
	begin
		insert into recette_ingredient (recette_id, ingredient_id) values (@i, cast(100 * rand() as int) + 1);
		set @j = @j + 1;
	end;
	set @i = @i + 1;
end;

La requête ci-dessous :

Code sql :

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

1
2
3
4
5
6
7
8
9
 
select r.id, r.nom
from recette r
where r.id not in (
  select ri.recette_id
  from ingredient i
  inner join recette_ingredient ri on ri.ingredient_id = i.id
  where i.nom in ('Ingredient 20', 'Ingredient 30', 'Ingredient 1', 'Ingredient 2', 'Ingredient 3', 'Ingredient 4', 'Ingredient 5', 'Ingredient 6', 'Ingredient 7', 'Ingredient 8', 'Ingredient 9', 'Ingredient 10', 'Ingredient 11', 'Ingredient 21', 'Ingredient 22', 'Ingredient 23', 'Ingredient 24', 'Ingredient 25', 'Ingredient 26', 'Ingredient 27', 'Ingredient 28', 'Ingredient 29')
);

Ramène 4068 lignes en moins d'une seconde.
Sur une machine qui a 4 ans, et tout à fait modeste.

[pcaboche]

Avant de se poser la question des performances de la requête, je vous invite à vous poser la question de la modélisation de la base.

Déjà dit, cependant...

... je ne peux ni modifier les index ni créer de table temporaire.

S'il n'a même pas le droit de créer de table temporaire, c'est même pas la peine de chercher à vouloir re-modéliser la base...

(ça sent le développement qui a été patché à tout-va...)

[StringBuilder]

L'impossibilité de faire quelque chose dans une base de données peut venir de plusieurs éléments :
- outils éditeur : vu l'aspect de la chose, j'ose espérer que ce n'est pas le cas (sinon y'a pas que l'éditeur qui a un problème interface-siège-écran, le client aussi, pour avoir choisi un machin pareil)
- chef de projet qui se prend pour un cador de la modélisation et qui impose sa merde aux développeurs : c'est bien des fois de lui mettre le nez dans son caca et lui faire comprendre que s'il était si intelligent, personne ne ferais de remarque
- base hébergée avec accès restreints : l'hébergeur sera le premier content d'avoir une base qui ne met pas à genoux son serveur mutualisé à la moindre requête... il suffit de s'adresser à la bonne personne pour pouvoir modifier la structure de la base

Enfin, rien n'empêche de remplacer la table existante par une vue de même structure qui pointe sur des données proprement stockées dans des tables proprement modélisées. SQL Server gère les déclencheurs sur les vues, ce qui est parfait pour rendre totalement transparent le remplacement de la table par une vue pour le programme consommateur.

[pcaboche]

- chef de projet qui se prend pour un cador de la modélisation et qui impose sa merde aux développeurs : c'est bien des fois de lui mettre le nez dans son caca et lui faire comprendre que s'il était si intelligent, personne ne ferais de remarque

Chef ou pas, c'est le genre de truc qui peuvent te coûter ta place ("ouiiiiinnn... il a sous-entendu que j'étais un gros nul et que mon design c'est de la m****. Il est trop méchaaaaaaant... " )

Curieusement, dans beaucoup de boites (surtout dans les boites françaises ou francophones), on a tendance à croire que l'intelligence est proportionnelle au salaire (). Donc à moins de gagner plus que le boss (consultant super bien payé ? ) le plus souvent, "tu fais ce qu'on te dit, c'est moi le patron" .

[StringBuilder]

Bah des fois, faut savoir dire à son patron que c'est un gros nul, et que s'il a des employés, c'est avant tout pour faire le boulot qu'il peut pas faire lui-même...

Quand tu restes bloqué devant un problème pendant 6 mois sans oser rien dire, tu passes juste pour un con quand un consultant te fait passer pour un abruti pour ne pas t'être rendu compte (et pas fait remonté) un problème aussi gros.
Il vaut mieux des fois prendre ses couilles à deux mains et dire ce qu'on a à dire au chef, quitte à se faire remettre à sa place, plutôt que de rester le troufion de base qui dit rien et donne l'impression de ne pas réfléchir.
Au moins, le jour où un consultant se pointe et dit que la modélisation est merdique, le chef pourra pas te remettre ça sur le dos, mais tu pourras au contraire l'enfoncer un peu plus "c'était bien la peine de payer un audit à 20 K€ pour se faire dire ce que je m'égosille à dire depuis 6 mois !".

Enfin bref, pour revenir au problème initial, pas de solution miracle.

Pas d'index, lecture sur des valeurs littérales doublonnées = performances forcément catastrophiques.

Au pire, un index sur les ingrédient devrais améliorer légèrement les choses (au prix d'une place disque perdue importante, vu que c'est des valeurs littérales qu'on indexe, et qu'elles sont doublonnées), et au mieux il faut tout revoir (avec possibilité de faire, comme je l'ai suggéré, une vue avec triggers pour limiter l'impact au niveau logiciel).

On pourra changer les NOT IN en NOT EXISTS, LEFT OUTER JOIN avec filtre sur NULL, ou autre HAVING COUNT(*) = 0, ça ne changera rien au problème : l'ensemble de la table doit être lu et chargé en mémoire, ce qui est catastrophique, et pas le rôle d'un S§GBD.

[pcaboche]

Il vaut mieux des fois prendre ses couilles à deux mains et dire ce qu'on a à dire au chef, quitte à se faire remettre à sa place, plutôt que de rester le troufion de base qui dit rien et donne l'impression de ne pas réfléchir.
Au moins, le jour où un consultant se pointe et dit que la modélisation est merdique, le chef pourra pas te remettre ça sur le dos, mais tu pourras au contraire l'enfoncer un peu plus "c'était bien la peine de payer un audit à 20 K€ pour se faire dire ce que je m'égosille à dire depuis 6 mois !".

J'ai déjà vu des gens brillants se faire virer parce qu'ils ont eu l'outrecuidance de poitner du doigt certaines conneries du management. Ce qui restent, ce sont ceux qui ont des obligations (une femme, des enfants, une maison à payer) et qui sont plus enclin à courber l'échine (ce que je peux comprendre).

Le mieux, c'est d'être dans la position de l'auditeur à 20 K€ : il ne reste pas assez longtemps pour devoir supporter les cons et corriger les problèmes, il est grassement payé, iul peut se permettre de dire "vous êtes tous des m****" et tout le monde l'applaudit pour cela. Faudrait que je me trouve un boulot comme ça...

Enfin bref, pour revenir au problème initial, pas de solution miracle.

Pas d'index, lecture sur des valeurs littérales doublonnées = performances forcément catastrophiques.

...

À mon avis, il nous fournit sa table "recette" pour nous exposer son problème sans toutefois révéler le nom et le contenu réel de la table.

Parce que bon, 15 secondes pour retourner 10'000 enregistrements, sa requête est sans doute plus complexe que cela...
Dans ce cas, par manque d'information, on ne peut rien conclure quant à la modélisation.

[StringBuilder]

A ce moment, faut qu'il donne toutes les infos, car il n'est pas impossible qu'en voulant trop "simplifier" le problème pour nous l'exposer, il oublie le principal (comme souvent, quand on trouve pas, c'est qu'on cherche pas au bon endroit, et il est donc impératif d'avoir une vision d'ensemble).

Pour en revenir aux boîtes de 3 lettres, honnêtement, je pense que dans l'informatique il y a assez de boulot pour ne pas rester dans ce genre de sociétés puantes.

J'ai travaillé, en tant que prestataire (donc le consultant qui facture 20 K€ le document intitulé "vous êtes tous débiles"), dans ce genre de boîtes.
Honnêtement, ils aiment pas non plus quand ça vient d'un prestataire J'ai plus d'une mission à mon actif qui s'est écourtée suite à ce genre de remarque (même noyée dans une noisette de vaseline, ça passe pas toujours).

Mais mon patron, qui est loin d'être stupide, préfère travailler avec des gens intelligents qui disent quand il y a un problème plutôt que de travailler avec des lèches 3 lettres qui barbotent dans la 5 lettres et s'y complaisent sans rien faire avancer.

Il faut toutefois garder à l'esprit que la critique pour la critique, c'est juste pire que de faire de la 5 lettres et ne pas le reconnaître : quand on râle après une solution, il faut avant tout en avoir une meilleure à proposer. C'est ce qui fait la différence entre celui qui se fait virer, et celui qui remplace le chef... souvent.

[Mizar31]

Bonjour a tous,
Je ne pensais provoquer tant de ressentiments en postant ma question...
Cela dit, il est vrai que le probleme de ma requete sans 'Farine ni beurre' n'etait qu'une illustration de mon veritable probleme.
Mais l'exposé de ma veritable requete pointant sur ma table de faits et 8 autres tables de references (modele en etoile) sur le forum me semblait inaproprié. Pour le reste, je ne peux qu'etre d'accord avec vous sur le fait que la modelisation de l'ensemble semble aujourd'hui manquer d'amenagements pour ameliorer les performances d'un systeme transactionnel voué à alimenter des cubes OLAP.
Pour la petite histoire, cela a ete mis en place par une equipe de consultants se prenant tous pour des cadors et qui ont renvoyé dans leurs buts les equipes fonctionnelles qui osaient faire des preconisations avec le soutien du DI de l'epoque qui se vantait de jouer au golf avec le DG.
Sans oublier au passage de presenter la note, bien entendu. Mais aujourd'hui la donne a changé, la "crise" est passée par là et la re-modelisation d'un systeme WorldWild (nous ne sommes pas dans la petite PME francaise travaillant sous ACCESS) n'est plus d'actualité quitte a se ramasser dans quelques années par manque de réactivité. Bref, nous ne vivons pas dans un monde idéal, cela se saurait...

Pour en revenir a mon probleme d'optimisation, en effet, aucune des solutions proposées n'a eu de réels impacts sur les temps de reponses.
Dommage, j'avais quelques espoirs sur la solution de Pcaboche avec le HAVING AVG().

En attendant, d'envoyer un mail a mon patron pour lui dire que c'est un trouduc et son systeme une grosse m.... je vais marquer ce post comme etant resolu.

Je vous remercie tous pour vos remarques et vos suggestions.

Bien cordialement.