Amélioration requête

**ibram_im** · 20/02/2019, 19h26

Bonjour,

j'ai ma table stockdate (id, qte, stock_date, Mag_id, Exe_id, Pro_id) qui enregistre toutes les modifications qui sont effectuées dans ma table stock (id, qte, Mag_id, Exe_id, Pro_id). Voila ,maintenant je veut faire une requêt qui me renvoie le dernier enregistrement dans la tables stock_date par rapport à une date donnée.
Voici ma requête qui fonctionne bien.

Code :

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

select * from stockdate where stock_date = (SELECT MAX(stock_date) FROM stockdate WHERE stock_date <= '2019-02-20 23:59:00' and Exe_id =2019 and Pro_id=1 AND Mag_id=1) and Exe_id =2019 and Pro_id=1 AND Mag_id=1

avec le jeu suivant
Nom : Capture.PNG
Affichages : 136
Taille : 18,3 Ko

j'obtient bien le bon résultat
Nom : Capture1.PNG
Affichages : 144
Taille : 18,3 Ko

j'aimerai savoir s'il y'a mieux que ma solution. Merci

**escartefigue** · 20/02/2019, 20h27

Bonjour

Il y a plusieurs façons de procéder, vous pouvez vous inspirer de mon blog pour les différentes solutions possibles.
La meilleure dépend du contexte, à prototyper donc en analysant les plans d'exécution et durées.

Quoi qu'il en soit, vous pouvez déjà remplacer le SELECT * par la liste des colonnes utiles à votre traitement, ce sera toujours ça de gagné en perf et en stabilité du résultat.

De plus, une fois mise au propre, on constate que votre prédicat de filtrage comporte des redondances, vous pouvez donc simplifier, ça ne changera pas les perfs, mais ce sera plus facile à lire

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
select col1
     , col2
     , [...]
     , coln
from stockdate 
where stock_date = 
    (SELECT MAX(stock_date) 
     FROM  stockdate 
     WHERE stock_date <= '2019-02-20 23:59:00' 
       and Exe_id      =2019 
       and Pro_id=1 
       and Mag_id=1) 
  and Exe_id =2019 <-- inutile
  and Pro_id=1     <-- inutile
  and Mag_id=1     <-- inutile

Après bien sur il faut vérifier quels sont vos critères de filtrage qui sont "sargables" (index présent et éligible) : les colonnes stock_date, exe_id, pro_id et mag_id sont elles indexées ? sont elles filtrantes ?

**tatayo** · 21/02/2019, 08h56

Bonjour,
Pour moi il n'y a pas de redondance dans les critères de filtrages.

La sous-requête renvoie une date max pour les lignes qui respectent une série de critères, et une date butoir
La requête renvoie toutes les lignes de la table qui respectent les mêmes critères, et dont la date est celle renvoyée par la sous-requête

Si on enlève les critères "inutiles" de la requête principale, elle va renvoyer toutes les lignes dont la date est celle retournée par la sous-requête, quelles que soient les valeurs des colonnes exe_id, prog_id et mag_id.
Le résultat ne sera alors plus le même.

Tatayo.

**escartefigue** · 21/02/2019, 11h59

En effet c'est possible, j'avais supposé que pour un magasin, un produit et une date il ne pouvait y avoir qu'une seule occurrence mais après tout faute de modèle de données, ce n'est peut être pas le cas

**ibram_im** · 21/02/2019, 12h32

Merci à escartefigue et tatayo.
en effet comme a dit tatayo, si j'enlève les critères supposés innutiles, le résultat ne sera pas correcte, vu qu'il va me renvoyer un ensemble de ligne au lieu d'une seule ligne.

**escartefigue** · 21/02/2019, 14h47

Juste par curiosité, cette table ressemble à un inventaire pour un article dans un magasin à une date.
Comment se fait il qu'il y a plusieurs lignes ? il s'agit de plusieurs emplacements dans un même magasin ?

**Artemus24** · 26/02/2019, 18h05

Salut à tous

Envoyé par ibram_im

je veux faire une requête qui me renvoie le dernier enregistrement dans la tables stock_date par rapport à une date donnée.

Votre solution est correcte, à la condition que vous utilisez des index sur votre table "stockdate". Performante, peut-être pas.

A part les remarques d'Escartefigue, j'ajouterai :

1) que faites-vous en cas de doublon (plusieurs dates identiques) ?

2) pourquoi utiliser le type datetime ? Mettre la colonne en timestamp(6) aurait été préférable, à cause des doublons.

3) je ne vois pas de clef primaire de type technique (auto incrémenté).
Je ne suis pas trop partisan d'utiliser un identifiant composé de plusieurs valeurs.
Le mieux est de décomposer en plusieurs colonnes, vos différentes valeurs, et de les regrouper en tant que clef primaire.

4) je vous propose la requête suivante :

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `test`
--------------
 
--------------
CREATE TABLE `test`
( `exe_id`      smallint unsigned NOT NULL,
  `type_id`     char(03)          NOT NULL,
  `val_id`      smallint unsigned NOT NULL,
  `prod_id`     tinyint  unsigned NOT NULL,
  `mag_id`      tinyint  unsigned NOT NULL,
  `qte`         integer  unsigned NOT NULL,
  `unite`       char(04)          NOT NULL,
  `stock_date`  datetime          NOT NULL,
  PRIMARY KEY (`exe_id`,`type_id`,`val_id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
INSERT INTO `test` (`exe_id`,`type_id`,`val_id`,`prod_id`,`mag_id`,`qte`,`unite`,`stock_date`) VALUES
  (2019, 'HAR', 16, 1, 1, 600, 'cndt', '2019-02-19 16:46:42'),
  (2019, 'HAR', 35, 1, 1, 595, 'cndt', '2019-02-19 17:41:14'),
  (2019, 'HAR', 38, 1, 1, 581, 'cndt', '2019-02-20 16:13:42'),
  (2019, 'HAR', 41, 1, 1, 531, 'cndt', '2019-02-20 16:15:51'),
  (2019, 'HAR', 44, 1, 1, 486, 'cndt', '2019-02-20 16:46:35')
--------------
 
--------------
select * from `test`
--------------
 
+--------+---------+--------+---------+--------+-----+-------+---------------------+
| exe_id | type_id | val_id | prod_id | mag_id | qte | unite | stock_date          |
+--------+---------+--------+---------+--------+-----+-------+---------------------+
|   2019 | HAR     |     16 |       1 |      1 | 600 | cndt  | 2019-02-19 16:46:42 |
|   2019 | HAR     |     35 |       1 |      1 | 595 | cndt  | 2019-02-19 17:41:14 |
|   2019 | HAR     |     38 |       1 |      1 | 581 | cndt  | 2019-02-20 16:13:42 |
|   2019 | HAR     |     41 |       1 |      1 | 531 | cndt  | 2019-02-20 16:15:51 |
|   2019 | HAR     |     44 |       1 |      1 | 486 | cndt  | 2019-02-20 16:46:35 |
+--------+---------+--------+---------+--------+-----+-------+---------------------+
--------------
select           t1.*
           from  `test` as t1
 
left outer join  `test` as t2
             on  t2.stock_date > t1.stock_date
            and  t2.stock_date < '2019-02-20 16:15:00'
            and  t2.exe_id     = 2019
            and  t2.prod_id    = 1
            and  t2.mag_id     = 1
 
          where  t1.stock_date < '2019-02-20 16:15:00'
            and  t1.exe_id     = 2019
            and  t1.prod_id    = 1
            and  t1.mag_id     = 1
            and  t2.exe_id is null
--------------
 
+--------+---------+--------+---------+--------+-----+-------+---------------------+
| exe_id | type_id | val_id | prod_id | mag_id | qte | unite | stock_date          |
+--------+---------+--------+---------+--------+-----+-------+---------------------+
|   2019 | HAR     |     38 |       1 |      1 | 581 | cndt  | 2019-02-20 16:13:42 |
+--------+---------+--------+---------+--------+-----+-------+---------------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Pour faire apparaître la date maximale de votre jeu d'essai, j'ai dû changer votre critère en mettant '2019-02-20 16:15:00'.
A vous de tester cette requête !

@+

**ibram_im** · 27/02/2019, 10h20

Merci pour vos commentaires et propositions.
je vais un peu expliquer le contexte du projet. L'entreprise est un groupe qui a des magasins dans plusieurs villes du pays. Ces magasins jouent aussi en même temps le rôle de point de vente c'est-à-dire que les clients viennent directement dans ces magasins pour effectuer des achats des produits. Chaque magasin(point de vente) a sa propre base de données locale , qui effectue à la fin de la journée une synchronisation avec la base centrale qui est sur un hébergeur web. C'est dans ce cadre que nous avons décider d'avoir une clé composée pour la plus part de nos tables pour éviter les doublons des clés dans la base centrale lors de la synchronisation.
Lors qu'on utilise les données suivantes:

Code :

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

1
2
3
4
5
6
7
8
9
+--------+---------+--------+---------+--------+-----+-------+---------------------+
| exe_id | type_id | val_id | prod_id | mag_id | qte | unite | stock_date          |
+--------+---------+--------+---------+--------+-----+-------+---------------------+
|   2019 | HAR     |     16 |       1 |      1 | 600 | cndt  | 2019-02-19 16:46:42 |
|   2019 | HAR     |     35 |       1 |      1 | 595 | cndt  | 2019-02-19 17:41:14 |
|   2019 | HAR     |     38 |       1 |      1 | 581 | cndt  | 2019-02-20 16:13:42 |
|   2019 | HAR     |     41 |       1 |      1 | 531 | cndt  | 2019-02-20 16:15:51 |
|   2019 | HAR     |     44 |       1 |      1 | 486 | cndt  | 2019-02-20 16:46:35 |
+--------+---------+--------+---------+--------+-----+-------+---------------------+

dans l'application, l'utilisateur va choisir une date (Y-m-d) et je vais transformer cette date en (Y-m-d H:m:s). donc si l'utilisateur choisie la date 2019-02-20 , dans ma requête j'aurai 2019-02-20 23:59:00 et le résultat doit être la ligne 2019-02-20- 16:43:35 parce que c'est la date MAX de la journée en question. De même si l'utilisateur avait choisi la date 209-02-27, le résultat sera toujours le même puisque la date Max la plus proche de 2019-02-27 est toujours 2019-02-20 16:46:35.
En conclusion, il n'y aura pas de doublons de date dans la table "stockdate" et utiliser le type "timestamp(6)" à la place de "datetime" n'aurai pas marché.

**SQLpro** · 28/02/2019, 20h42

Envoyé par ibram_im

... C'est dans ce cadre que nous avons décider d'avoir une clé composée pour la plus part de nos tables pour éviter les doublons des clés dans la base centrale lors de la synchronisation.

C'est d'une haute stupidité. Votre clef ressemble à celle-ci :
Nom : clef beynac2.jpg
Affichages : 115
Taille : 50,1 Ko

En voici la taille comparative...
Nom : clef beynac.jpg
Affichages : 120
Taille : 259,2 Ko

Maintenant c'était du temps de châteaux fort… (en particulier, celles-ci viennent de Beynac en Dordogne, pays cher à Artemus !)

Lorsque l'on a une telle chose, on remplace cette clef primaire par une clef dite alternative ou subrogée, c'est à dire une contrainte d'unicité et on créé une véritable clef primaire auto incrémentée propre à la table afin d'obtenir une clef comme celle-ci :
Nom : clef.jpg
Affichages : 123
Taille : 14,5 Ko

Nom : clef.jpg
Affichages : 123
Taille : 14,5 Ko

La manipulation de votre table avec une telle clef aura les mêmes conséquences de lourdeur et de complexité que celle du château fort. Imaginez simplement de l'avoir dans la poche !

A +

**Artemus24** · 28/02/2019, 17h00

Salut ibram_im.

Envoyé par ibram_im

C'est dans ce cadre que nous avons décider d'avoir une clé composée pour la plus part de nos tables pour éviter les doublons des clés dans la base centrale lors de la synchronisation.

Je n'ai pas dit que l'usage de votre clef était mauvaise, mais de ne pas créer dans une seule colonne, la concaténation de plusieurs informations.
Autrement dit, je rejete l'idée d'avoir sous la forme d'une seule colonne, une clef composée, mais déclater votre clef composée en plusieurs colonnes, comme dans mon exemple.
Au final, cela ne change rien, sinon à simplifier la structure de vos tables.

Envoyé par ibram_im

dans l'application, l'utilisateur va choisir une date (Y-m-d) et je vais transformer cette date en (Y-m-d H:m:s).

Pourquoi avoir concaténé la date avec l'heure si l'utilisateur doit sélectionner que la partie date ?
Dans ce cas, faites une colonne pour la date seule et une autre colonne pour l'heure seule.

Envoyé par ibram_im

En conclusion, il n'y aura pas de doublons de date dans la table "stockdate" et utiliser le type "timestamp(6)" à la place de "datetime" n'aurai pas marché.

Le type timestamp(6) ("2019-02-28 12:05:17.123456") vous donne une précision plus grande que le type datetime ("2019-02-28 12:05:17").
Si en cas de doublon sur la partie date + time, vous pouvez faire la différence sur la partie millième de seconde.

Cela ne change rien au traitement, mais vous donne un plus grande précision.
Ne pas oublier de faire l'usage des index pour améliorer la performance de vos recherches.

@+