Indexer le nom de la table lors d'une requête

**Peugg** · 26/11/2020, 11h40

Bonjour tous le monde,

J'ai X tables dans mon serveur SQL:
- Table_1
- Table_2
.
.
- Table X

J'ai une procédure stockée avec un paramètre d'entré : @Index INT

Ce que j'aimerais pouvoir faire c'est sélectionner dans le "FROM" de ma requête la table en fonction de l'index.

du style:
SELECT * FROM Table_+@Index WHERE ...

Quelqu'un aurait une idée de la syntaxe à appliquer ?

Merci!

**escartefigue** · 26/11/2020, 14h05

Pour ce genre de besoin, du SQL dynamique est la solution sauf que...

il faudra aussi probablement dynamiser la clause WHERE, il est en effet très peu probable que les mêmes colonnes de filtrage existent dans toutes les tables
ce type d'approche vous contraint à faire un select *, ce qui n'est jamais bon : résultat instable et requête peu performante

Quel est le but de la manoeuvre ? Il y a sans doute d'autres approches plus simples et plus performantes

**al1_24** · 26/11/2020, 14h25

Si toutes ces tables ont la même structure, cela démontre un problème de modélisation. Les données devraient toutes être regroupées en une seule table avec une colonne supplémentaire pour l'indice.
En attendant, toujours en supposant que ces tables ont la structure, on peut créer une vue pour émuler la structure idéale :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create view mavue
as  select  1   as indice
        ,   tbl.colonne_1
        ,   ...
        ,   tbl.colonne_n
    from    table_1 as tbl
union all
    ...
union all
    select  n   as indice
        ,   tbl.colonne_1
        ,   ...
        ,   tbl.colonne_n
    from    table_n as tbl

Ainsi la requête précédente est beaucoup plus simple :

Code :

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

1
2
3
4
5
SELECT  colonne_1
        ,   ...
        ,   colonne_n 
FROM    mavue   
WHERE   indice = @Index

**Peugg** · 26/11/2020, 17h31

En faite le problème est que chaque table fait déjà plus d'un million de lignes.
Et en tous il y a 101 tables.

Je sais pas si le serveur va accepter de rassembles toutes les lignes et surtout cela risque de prendre beaucoup de temps non?

**Peugg** · 26/11/2020, 17h38

Envoyé par escartefigue

Pour ce genre de besoin, du SQL dynamique est la solution sauf que...

il faudra aussi probablement dynamiser la clause WHERE, il est en effet très peu probable que les mêmes colonnes de filtrage existent dans toutes les tables
ce type d'approche vous contraint à faire un select *, ce qui n'est jamais bon : résultat instable et requête peu performante

Quel est le but de la manoeuvre ? Il y a sans doute d'autres approches plus simples et plus performantes

C'est quoi le SQL dynamique? désolé je suis novice en SQL ^^
Le but est un peu complexe a expliquer mais mes différents tableaux représentent chacun le débit passant dans différent circuit d'air en fonction de position de réglage.

**escartefigue** · 26/11/2020, 17h41

En ce cas on tombe sur le travers suppose par al1__24 : plusieurs tables de même structure.
Soit une grosse erreur de modélisation.
À corriger si possible sinon cf. sa proposition.

**SQLpro** · 26/11/2020, 17h48

Envoyé par Peugg

En faite le problème est que chaque table fait déjà plus d'un million de lignes.
Et en tous il y a 101 tables.

Je sais pas si le serveur va accepter de rassembles toutes les lignes et surtout cela risque de prendre beaucoup de temps non?

Que croyez vous ???

Un vieux proverbe US dit "garbage in garbage out"
Traduisible par "si t'as de la merde en entrée, t'auras de la merde en sortie" !
Votre merde en entré c'est ces 101 tables
votre merde en sortie ce sera l'UNION de toutes ces tables.

MySQL étant de très loin le SGBD le plus pourri en terme de performances et capacité, je vous souhaite bien du plaisir !

Pour info dans SQL Server , traiter des tables de 100 millions de ligne c'est courant, simple et extrêmement rapide, grâce notamment aux index columnstore dont MySQL est dépourvu...
https://docs.microsoft.com/fr-fr/sql...l-server-ver15
https://www.mssqltips.com/sqlservert...ta-warehouses/

A +

**Artemus24** · 27/11/2020, 02h01

Salut à tous.

Je réponds à la question posée.
Voici un exemple de SQL DYNAMIC dans une procédure stockée :

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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `tab_1`
--------------
 
--------------
CREATE TABLE `tab_1`
(  `id`    integer unsigned NOT NULL auto_increment Primary key,
   `mess`  varchar(255)     NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
--------------
 
--------------
INSERT INTO `tab_1` (`mess`) VALUES
  ('Un'),('Deux'),('Trois'),('Quatre'),('Cinq')
--------------
 
--------------
select * from `tab_1`
--------------
 
+----+--------+
| id | mess   |
+----+--------+
|  1 | Un     |
|  2 | Deux   |
|  3 | Trois  |
|  4 | Quatre |
|  5 | Cinq   |
+----+--------+
--------------
DROP TABLE IF EXISTS `tab_2`
--------------
 
--------------
CREATE TABLE `tab_2`
(  `id`    integer unsigned NOT NULL auto_increment Primary key,
   `mess`  varchar(255)     NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
--------------
 
--------------
INSERT INTO `tab_2` (`mess`) VALUES
  ('One'),('Two'),('Three'),('Four'),('Five')
--------------
 
--------------
select * from `tab_2`
--------------
 
+----+-------+
| id | mess  |
+----+-------+
|  1 | One   |
|  2 | Two   |
|  3 | Three |
|  4 | Four  |
|  5 | Five  |
+----+-------+
--------------
DROP PROCEDURE IF EXISTS `search`
--------------
 
--------------
CREATE PROCEDURE `search`
( in in_index  integer,
  in in_mess   varchar(255)
)
DETERMINISTIC
NO SQL
BEGIN
  SET @req = concat("select * from tab_", in_index, " where mess = '", in_mess, "';");
 
  PREPARE            stmt FROM @req;
  EXECUTE            stmt;
  DEALLOCATE PREPARE stmt;
END
--------------
 
--------------
call `search` (1, 'trois')
--------------
 
+----+-------+
| id | mess  |
+----+-------+
|  3 | Trois |
+----+-------+
--------------
call `search` (2, 'five')
--------------
 
+----+------+
| id | mess |
+----+------+
|  5 | Five |
+----+------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

A part votre demande, je dirai que vous avez une problème de modélisation de votre base de données.
Il existe une solution qui consiste à partitionner vos tables.
A lire : https://dev.mysql.com/doc/refman/8.0...titioning.html

@+

**Peugg** · 01/12/2020, 09h55

Envoyé par SQLpro

Que croyez vous ???

Un vieux proverbe US dit "garbage in garbage out"
Traduisible par "si t'as de la merde en entrée, t'auras de la merde en sortie" !
Votre merde en entré c'est ces 101 tables
votre merde en sortie ce sera l'UNION de toutes ces tables.

MySQL étant de très loin le SGBD le plus pourri en terme de performances et capacité, je vous souhaite bien du plaisir !

Pour info dans SQL Server , traiter des tables de 100 millions de ligne c'est courant, simple et extrêmement rapide, grâce notamment aux index columnstore dont MySQL est dépourvu...
https://docs.microsoft.com/fr-fr/sql...l-server-ver15
https://www.mssqltips.com/sqlservert...ta-warehouses/

A +

Justement j'aimerai éviter de devoir réunir les tables sachant que je peux pointer sur une seule (je choisis la table en fonction d'un paramètre de réglage)

**Peugg** · 01/12/2020, 09h58

Envoyé par Artemus24

Salut à tous.

Je réponds à la question posée.
Voici un exemple de SQL DYNAMIC dans une procédure stockée :

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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS `tab_1`
--------------
 
--------------
CREATE TABLE `tab_1`
(  `id`    integer unsigned NOT NULL auto_increment Primary key,
   `mess`  varchar(255)     NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
--------------
 
--------------
INSERT INTO `tab_1` (`mess`) VALUES
  ('Un'),('Deux'),('Trois'),('Quatre'),('Cinq')
--------------
 
--------------
select * from `tab_1`
--------------
 
+----+--------+
| id | mess   |
+----+--------+
|  1 | Un     |
|  2 | Deux   |
|  3 | Trois  |
|  4 | Quatre |
|  5 | Cinq   |
+----+--------+
--------------
DROP TABLE IF EXISTS `tab_2`
--------------
 
--------------
CREATE TABLE `tab_2`
(  `id`    integer unsigned NOT NULL auto_increment Primary key,
   `mess`  varchar(255)     NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
--------------
 
--------------
INSERT INTO `tab_2` (`mess`) VALUES
  ('One'),('Two'),('Three'),('Four'),('Five')
--------------
 
--------------
select * from `tab_2`
--------------
 
+----+-------+
| id | mess  |
+----+-------+
|  1 | One   |
|  2 | Two   |
|  3 | Three |
|  4 | Four  |
|  5 | Five  |
+----+-------+
--------------
DROP PROCEDURE IF EXISTS `search`
--------------
 
--------------
CREATE PROCEDURE `search`
( in in_index  integer,
  in in_mess   varchar(255)
)
DETERMINISTIC
NO SQL
BEGIN
  SET @req = concat("select * from tab_", in_index, " where mess = '", in_mess, "';");
 
  PREPARE            stmt FROM @req;
  EXECUTE            stmt;
  DEALLOCATE PREPARE stmt;
END
--------------
 
--------------
call `search` (1, 'trois')
--------------
 
+----+-------+
| id | mess  |
+----+-------+
|  3 | Trois |
+----+-------+
--------------
call `search` (2, 'five')
--------------
 
+----+------+
| id | mess |
+----+------+
|  5 | Five |
+----+------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

A part votre demande, je dirai que vous avez une problème de modélisation de votre base de données.
Il existe une solution qui consiste à partitionner vos tables.
A lire : https://dev.mysql.com/doc/refman/8.0...titioning.html

@+

Super merci ! c'est ce que je cherchais

**escartefigue** · 01/12/2020, 09h58

Si ce n'est pas trop tard, c'est pourtant la bonne solution : une seule table.
Avec 101 tables, il faut gérer les modifications dans 101 structures, multiplier les index, les vues, complexifier la gestion des privilèges...
Aucun intérêt.

**Peugg** · 01/12/2020, 10h18

Bonjour!

J'ai du mal a voir ce que ça change en terme de performance d'avoir plusieurs tables d'1M de lignes et en cibler une, ou d'avoir une grande table de 101M de lignes comme vous le préconisez avec toutes les lignes où l'on ciblerai une certaine plage de 1M de ligne pour chercher la solution.

Si j'ai le temps j'essaierai les deux.

Merci à vous pour votre aide

**Peugg** · 01/12/2020, 10h22

Envoyé par escartefigue

Si ce n'est pas trop tard, c'est pourtant la bonne solution : une seule table.
Avec 101 tables, il faut gérer les modifications dans 101 structures, multiplier les index, les vues, complexifier la gestion des privilèges...
Aucun intérêt.

Mais une fois la table crée et implémentée dans le serveur, elle ne bougera plus jamais.

elle sera utilisé ensuite pour une "simple" recherche pour récupérer des consignes de réglage à envoyer sur une machine par rapport à d'autre consigne

**escartefigue** · 01/12/2020, 11h14

Envoyé par Peugg

Bonjour!

J'ai du mal a voir ce que ça change en terme de performance d'avoir plusieurs tables d'1M de lignes et en cibler une, ou d'avoir une grande table de 101M de lignes comme vous le préconisez avec toutes les lignes où l'on ciblerai une certaine plage de 1M de ligne pour chercher la solution.

Si j'ai le temps j'essaierai les deux.

Merci à vous pour votre aide

Ca change plein de choses.

Déjà, la plupart des tables possèdent des clefs étrangères référençant des identifiants qui sont dans d'autres tables.
A l'inverse d'autres tables peuvent aussi posséder des FK qui référencent la PK de ces 101 tables !
Avoir 101 tables interdit ce fonctionnement et donc compromet l'intégrité référentielle. Ce qui est très grave !
La première qualité d'une base de données est de garantir l'intégrité de son contenu, pour ce faire, l'intégrité référentielle est obligatoire

De plus, qui peut dire qu'une table n'évoluera jamais, perosnne. Une base de données vit en fonction des demandes des gestionnaires, des contraintes réglementaires ou autres contingences extérieures à votre organisation et que vous ne maîtrisez pas.

Enfin, quand le volume va encore augmenter dans les tables, vous allez créer une 102eme puis 103eme puis...

**Peugg** · 01/12/2020, 11h55

La table est une "Photographie" de débit passant par différents circuits par rapport au réglage d'ouverture de ces circuits
Les 101 tables correspondes au 0-100% d'ouverture de l'extraction d'air, donc une table par %
c'est un peu une utilisation détourné de SQL dans le sens ou on ne gère pas la base de donnée. C'est la seule méthode qui nous garantie des temps d'exécution court (~ 1s sur SQL / 7-8s sur un exécutable fortran)

Donc il n'y aura pas de 102 ou 103 tables, ni de modification de ces tables.

A la base je m'étais dis qu'en séparent les données ça serait plus facile au serveur de me renvoyer les résultats s'il cherche dans une plus petite table, après je connais très peu cette techno ^^

**escartefigue** · 01/12/2020, 13h38

Rechercher une ligne particulière dans une table de 100 millions de lignes sur un critère discriminant et indexé ne prend que quelques nanosecondes
Un modèle évolutif est toujours préférable à un modèle figé même si aujourd'hui vous ne voyez pas de perspective d'évolution, vous ne pouvez pas garantir que ce ne sera jamais nécessaire.
Argument supplémentaire : pour consolider des résultats sur différents pourcentages, cette architecture nécessite des requêtes UNION (ALL) et du coup vous balayerez bien l'ensemble du volume tous pourcentages confondus.

**Peugg** · 07/12/2020, 11h42

Bonjour,

Je n'ai pas encore eu le temps d'intégrer la base de donnée mais on a déjà généré toute les solutions d'après notre modèle. Je pense effectivement que pour la suite je vais donc fusionner tous les tableaux en un.

Merci pour votre aide

A bientôt

**escartefigue** · 08/12/2020, 07h32

Si c'est dans un contexte d'entreprise, je suis très surpris que personne n'ai poussé des hauts cris en voyant arriver 101 tables de même structure...

En principe, l'architecture de la BDD doit être validée par les services compétents (DBA, architecte...) bien avant que les applications soient développées, justement pour éviter ce genre d'impair.

**Peugg** · 08/12/2020, 08h06

C'est un peu particulier comme fonctionnement, ce n'est pas un outils qui va être utilisé sur un réseau, mais uniquement en local dans un IPC et sera utilisé pour le paramétrage d'une machine. (trouver les réglages de la machine par rapport a des consignes simplifiées)

Indexer le nom de la table lors d'une requête

Requêtes MySQL

Discussions similaires

Partager

Partager