Optimisation requête SQL

**devnet75** · 16/09/2015, 10h04

(Re) Bonjour,

je reviens vers vous pour savoir si il serait possible d'optimiser une de mes requêtes. Celle-ci fonctionne, retourne le résultat voulu, mais j'aimerai savoir si il serait possible de l'améliorer pour booster les perfs.

Je cherche à obtenir le nombre de clients inscrit chaque mois ainsi que le nombre de clients convertis (ayant passé une commande). Voici ma requête à l'heure actuelle :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 
        COUNT( client.id ) AS nbClient, 
	DATE_FORMAT( client.created, "%Y" ) AS year, 
	DATE_FORMAT( client.created, "%m" ) AS month, 
	SUM(
		CASE WHEN (SELECT COUNT( * ) FROM commande WHERE commande.idClient = client.id ) > 0
		THEN 1
		ELSE 0
		END
	) AS nbConvertis
FROM client
WHERE client.created >= '2015-01-01'
AND client.created < '2015-09-17'
GROUP BY DATE_FORMAT( client.created, "%Y-%m" )

Je pense que la sous-requête prend beaucoup de ressources (sachant que pour l'année 2015, j'ai environ 1000 clients inscrits). Pensez-vous qu'il est possible de l'optimiser ?

Merci par avance

**escartefigue** · 16/09/2015, 11h23

Bonjour,

En effet, votre sous requête ne devrait pas utiliser un select mais faire un test d'existence comme suit

Code :

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

1
2
 
WHERE EXISTS SELECT 0 FROM  commande WHERE commande.idClient = client.id

**SQLpro** · 16/09/2015, 11h49

Cette requête est totalement imbécile... je suppose que c'est du MySQmerde ? En effet COUNT(id_client) doit vous renvoyer toujours rien ?
De plus votre groupage ne correspond pas à votre SELECT....

A +

**devnet75** · 16/09/2015, 12h13

Bonjour,

merci à vous deux pour votre aide,

Envoyé par escartefigue

Bonjour,

En effet, votre sous requête ne devrait pas utiliser un select mais faire un test d'existence comme suit

Code :

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

1
2
 
WHERE EXISTS SELECT 0 FROM  commande WHERE commande.idClient = client.id

Je ne comprends pas comment intégrer cela à ma requête ? Cela fonctionne avec MySql ?

Envoyé par SQLpro

Cette requête est totalement imbécile... je suppose que c'est du MySQmerde ? En effet COUNT(id_client) doit vous renvoyer toujours rien ?
De plus votre groupage ne correspond pas à votre SELECT....

A +

Je ne comprend pas votre remarque :/ Oui je suis en MySql (pas le choix du SGBD malheureusement) mais la requête fonctionne parfaitement et les résultats sont justes (validés après une vérification manuelle).

**escartefigue** · 16/09/2015, 13h24

Essayez comme ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 
        COUNT( client.id ) AS nbClient, 
	DATE_FORMAT( client.created, "%Y" ) AS year, 
	DATE_FORMAT( client.created, "%m" ) AS month, 
	SUM(
		CASE WHEN EXISTS (SELECT 0 FROM commande WHERE commande.idClient = client.id )
 		THEN 1
		ELSE 0
		END
	) AS nbConvertis
FROM client
WHERE client.created >= '2015-01-01'
GROUP BY 1, 2, 3

**dehorter olivier** · 16/09/2015, 13h30

Bonjour

Et pourquoi ne pas utiliser une jointure entre les 2 tables ?

**devnet75** · 16/09/2015, 13h38

Envoyé par escartefigue

Essayez comme ceci :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 
        COUNT( client.id ) AS nbClient, 
	DATE_FORMAT( client.created, "%Y" ) AS year, 
	DATE_FORMAT( client.created, "%m" ) AS month, 
	SUM(
		CASE WHEN EXISTS (SELECT 0 FROM commande WHERE commande.idClient = client.id )
 		THEN 1
		ELSE 0
		END
	) AS nbConvertis
FROM client
WHERE client.created >= '2015-01-01'
GROUP BY 1, 2, 3

En effet, cela fonctionne mais j'ai pas l'impression de ressentir des améliorations de performances, toujours 7 secondes pour l'exécution de la requête :/

Envoyé par dehorter olivier

Bonjour

Et pourquoi ne pas utiliser une jointure entre les 2 tables ?

Sachant qu'un client peut avoir potentiellement plusieurs commandes et que je souhaites grouper par dates, je n'arrive pas à avoir comment organiser ma requête autour d'une jointure :/

**escartefigue** · 16/09/2015, 13h42

Envoyé par devnet75

En effet, cela fonctionne mais j'ai pas l'impression de ressentir des améliorations de performances, toujours 7 secondes pour l'exécution de la requête :/

Votre colonne date sur laquelle vous filtrez est elle indexée ? si oui quelle proportion de la population correspond au critère ?
Avez vous fait un explain de la requête ?

Concernant la jointure outer pour le test d'existence, elle sera au mieux égale en perf à un where exist, et souvent plus mauvaise (d'autant moins bonne qu'il y aura d'enregistrements en correspondance dans la table outer)
un where exists est donc le plus souvent préférable

**dehorter olivier** · 16/09/2015, 13h47

par un truc comme cela, à adapter bien entendu

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT 
        COUNT(DISTINCT client.id ) AS nbClient, 
	DATE_FORMAT( client.created, "%Y" ) AS year, 
	DATE_FORMAT( client.created, "%m" ) AS month, 
        COUNT(*)
FROM client
   INNER JOIN Commande ON Commande.idclient = client.idclient
WHERE client.created >= '2015-01-01'
GROUP BY 2, 3

**escartefigue** · 16/09/2015, 14h11

Envoyé par dehorter olivier

par un truc comme cela, à adapter bien entendu

Code :

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

1
2
3
4
5
6
7
8
9
10
 
SELECT 
        COUNT(DISTINCT client.id ) AS nbClient, 
	DATE_FORMAT( client.created, "%Y" ) AS year, 
	DATE_FORMAT( client.created, "%m" ) AS month, 
        COUNT(*)
FROM client
   INNER JOIN Commande ON Commande.idclient = client.idclient
WHERE client.created >= '2015-01-01'
GROUP BY 2, 3

Sauf que cette solution provoque autant de jointures avec la table des commandes qu'il y a de lignes de commandes pour le client
Puisqu'on n'a besoin d'aucune colonne de la table commandes, cette solution est à éviter, il faut préférer where exists

**dehorter olivier** · 16/09/2015, 14h31

Certes, mais sous MySql cela ne serait-il pas plus rapide ? je ne sais pas

Si tant est que les index existent !

escartefigue, je n'avais pas vu que tu étais "bourreau", ca fait peur

**escartefigue** · 16/09/2015, 14h46

Envoyé par dehorter olivier

Certes, mais sous MySql cela ne serait-il pas plus rapide ? je ne sais pas

non quelque soit le SGBD

Envoyé par dehorter olivier

Si tant est que les index existent !

Certes

j'ose espérer que l'index entre les commandes et les clients n'a pas été oublié

Envoyé par dehorter olivier

escartefigue, je n'avais pas vu que tu étais "bourreau", ca fait peur

Ce n'est pas mon métier, juste une vocation inassouvie

**SQLpro** · 16/09/2015, 15h07

Sans la description des tables et notamment des types de chaque colonnes et des index de la table, difficile de vous répondre. De plus MySQL ayant l'un des plus mauvais optimiseur, il est parfaitement crédible que le EXISTS n'apporte aucun gain !

A +

**devnet75** · 21/09/2015, 14h47

Bonjour tout le monde,

je reviens vers vous pour vous donner les retours de tous mes essais. Voyant que le passage au exists n'avait pas amélioré les performances et suite à la remarque de escartefigue sur les indexs je m'y suis penché d'un peu plus près.

Du coup, j'ai rajouté un index sur la colonne idClient de la table Commande et ça a tout débloqué ! La requête s'exécute maintenant en quelques dixièmes de seconde.

Merci à tous pour votre aide

**escartefigue** · 21/09/2015, 15h50

Maintenant que vous avez les index requis, faites la comparaison enter la jointure inner telle que vous l'aviez construite initialement, et la requête avec exists.
Il est très probable que vous constaterez un écart significatif en temps CPU, au bénéfice de la requête exists.

Optimisation requête SQL

Langage SQL

Discussions similaires

Partager

Partager