Optimisation de votre SGBDR et de vos requêtes...

[SQLpro]

Bonjour,

De la ré écriture des requêtes au paramétrage du serveur en passant sur
l'infrastructure système et l'influence des jeux de caractères sur la
rapidité d'exécution de vos requêtes, vous saurez tout ce qu'il faut
faire pour booster les performances de votre application et de votre
SGBDR favori !

A lire :
http://sqlpro.developpez.com/OptimSQL/SQL_optim.html

Frédéric BROUARD, expert SQL / bases de données
Livre 'SQL' la référence - Campus Press éditeur
* http://sqlpro.developpez.com/bookSQL.html *
site web 'SQLpro' http://sqlpro.developpez.com/

[neo.51]

encore un exellent article qui va étayer ton site qui est déjà la référence pour le SQL

[KPitN]

Vraiment vraiment super,

C'est tout a fait ce qu'il me faut, je vais suivre les conseils au maximum, car ma BDD risque d'etre assez grande.

une seule remarque:
est ce que ceci est vraiment important



deja que ma BDD est enorme je risque de l'emcombrer encore plus !


Mais En tout ca chapeau , c vraiment du bon boulot.

[SQLpro]

Cela parait idiot en effet de faire une table de référence pour un sexe qui peut être représenté par un booléen.

Mais comment savoir à quel sexe appartient TRUE ???

Le seul moyen est quelque part d'avoir l'information de correspondance.

Autrement dit une table de référence indiauant que TRUE = FEMME et FALSE = HOMME par exemple.

C'est peut être trivial, mais si tu donne ta base de données à quelqu'un qui ne connait pas cette correspondance, quel moyen a t-il pour recoller l'information ???

A +

[McFoggy]

Souvent les informations sur le sexe ne sont pas booléènne ms ternaire :
Homme, Femme, Indéterminé. Mais de là à créer une table pr ce genre d'informations...

McFoggy

[SQLpro]

C'est en ne normalisant pas que tu risque de l'encombrer énormément.

Si tu laisse le modèle 1 et que tu as 30 000 personnes dans ta table, cela fait : 68 * 2 octets * 30 000 = 4 Mega Octets dans ta table.

Si tu normalise au maximum, cette même table aura :
6 * 2 octets * 30 000 = 0.36 Mega Octets dans ta table

As ton avis, quelle sera la requête la plus rapide... Celle sur la table 1 ou la table 2 ???

Autrement dit la seconde solution possédant 11 fois moins de données ira 11 fois plus vite.
Même si tu rajoute les temps d'accès aux tables de références (correctement indexées) le gain de temps sera considérable !!!

A +

[garfield_fr]

Pourquoi mettre SEX_ID comme INTEGER ???
une definition du genre :

SEXE char check value in ('M','F',null)
(syntaxe a verifie mais vous comprendrez)

cette definition est largement suffisante car:
1-Il ne risque pas d'y avoir de nouveau sexe
2-1 octet, donc encore moins gourmand en place qu'un integer
3-permet l'indetermination avec la "valeur" null

[SQLpro]

1-Il ne risque pas d'y avoir de nouveau sexe
2-1 octet, donc encore moins gourmand en place qu'un integer
3-permet l'indetermination avec la "valeur" null

Quelques éléments pour te répondre :

1

- C'est vrai !

2

- Moins gourmand en place de stockage mais plus que tu ne le croit : CHAR = 2 octets en ASCI et 4 en unicode. De plus nécessite l'appel d'une collation pour l'ordre des caractères. Enfin les processeurs sont taillés pour traiter des nombres et non des caractères. Ces deux éléments font qu'un litéral même moins grand qu'un entier est entre 2 et 4 fois moins rapide dans les traitements.

3

- NULL signifie que la donnée n'a pas été renseignée... Mais si je veut dire :
SEXE inconnu (la personne ne l'a pas dit et il est pas évident d'affirmer qu'il s'agit d'un homme ou d'une femme)
SEXE non déterminé (malformation de naissance ?)
SEXE non applicable (escargot par exemple ?)
SEXE impossible (personne morale - société, association par exemple ?)

Comment vais je pouvoir rajouter ces différentes déclinaisons de mon information ?
En autorisant mon utilisateur à modifier la structure de la table ??? (ALTER de la contrainte que tu as mis en outre en ligne plutôt qu'en contrainte de table ???)

Non, le plus sage et le plus performant c'est le référencement.

La seule exception concerne les informations immuables externe à l'univers que je modélise. Exemple les noms des jours, les noms des mois sont des informations universelles (en dehors du supporte de la langue) et sont dans un ensemble fermé de cardinalité fixe.

A +

[P'tit Jean]

SQL Pro tu peux m'expliquer ce truc stp :

Code :

1
2
3
-- mauvais INSERT INTO T_CLIENT VALUES (198, 'M.', 'DUCORNET', 'Archibald', NULL)
-- bon INSERT INTO T_CLIENT (CLI_ID, TIT_CODE, CLI_NOM, CLI_PRENOM, CLI_ENSEIGNE) VALUES (198, 'M.', 'DUCORNET', 'Archibald', NULL)
-- excellent (insertion implicite des NULL) INSERT INTO T_CLIENT VALUES (CLI_ID, TIT_CODE, CLI_NOM, CLI_PRENOM) VALUES (198, 'M.', 'DUCORNET', 'Archibald')

C la partie excelente que je comprends pas, merci.

[SQLpro]

Voici :

Code :

1
2
3
4
 
-- mauvais 
   INSERT INTO T_CLIENT
          VALUES (198, 'M.', 'DUCORNET', 'Archibald', NULL)

Absence de la clause de liste des noms des colonnes => le SGBDR va avoir à faire des requêtes supplémentaires pour aller à la recherche de cas colonnes.

Code :

1
2
3
4
 
-- bon 
   INSERT INTO T_CLIENT (CLI_ID, TIT_CODE, CLI_NOM, CLI_PRENOM, CLI_ENSEIGNE) 
          VALUES (198, 'M.', 'DUCORNET', 'Archibald', NULL)

On passe la liste complete des colonnes, et pour celles non renseignée on spécifie des marquers NULL. Or NULL est par défaut en cas d'absence de spécification de la colonne. Donc, redondance et texte de requête plus long que :

Code :

1
2
3
-- excellent (insertion implicite des NULL) 
   INSERT INTO T_CLIENT (CLI_ID, TIT_CODE, CLI_NOM, CLI_PRENOM) 
          VALUES (198, 'M.', 'DUCORNET', 'Archibald')

A +

[orafrance]

Bonjour,

je m'étonne de la préco concernant l'espace disque à préserver (30% de l'espace totale)... Ca me parait extrémement couteux et j'arrive pas à trouver de justification à ce choix.

Dans le cas d'Oracle par exemple, les tablespaces ont une taille définit par le DBA et on peut donc s'assurer le controle parfait de l'espace disque (les archives logs et traces diverses étant bien entendu sur des disques séparés).

Alors si je conçois qu'on se laisse une réserve de 10-15% au cas où il faille agrandir des tablespaces, j'arrive pas à voir pourquoi le disque est plus lent lorsqu'il est rempli à 50 qu'à 80%. Les indexes permettent de retrouver l'adresse exact de l'info sur les disques donc le remplissage n'a que peut d'importance à ceci près qu la tête de lecture à plus de risque de parcourir de "longues" distance si les infos sont sur les extérieurs du cylindre entre autre

Merci de préciser le fond de ta pensée pour éclairer ma lanterne

[SQLpro]

En fait c'est une constante des OS et des ressources disque.
On s'aperçoit que les temps d'accès sont très constants tant que le disque est rempli à un taux N généralement situé vers 50 à 60%. Dès que le disque commence a dépasser ce stade, les temps d'accès augmentent de façon exponentiel.
N'oublie pas que les fichiers sont fragmentés, à moins d'avoir demandé un fichier de taille fixe pour ta base, à la création du disque.
Or moins il y a de la place plus la fragmentation est forte et distante.
Je me souviens avoir vu fonctionner une base Paradox sur un disque ou il ne restait plus que quelque milliers d'octets : 2 heures pour une requête de quelques lignes qui mettait ordinairement moins d'une seconde. Mais l'OS y est quand même arrivé !

on peut représenter le graphique de cette façon :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
temps d'accès
    ^
    |
    |
    |
    |
    |
    |                                     _
    |
    |
    | 
    |                                   _ 
    |   
    |                                 __
    |                             ____
    |                       ______
    |_______________________
    |
    | 
     ----------------------------------------> Remplissage %
    0      20      40      60      80      100

A +

[orafrance]

Effectivement, je n'avais pas pensé aux problémes de fragmentation des fichiers

Merci bien pour ces précisions

[Katyucha]

Envoyé par P'tit Jean

SQL Pro tu peux m'expliquer ce truc stp :

Code :

C la partie excelente que je comprends pas, merci.

En plus, si jamais, tu dois rajouter une colonne dans la table, tu dois modifier chaque insert existant dans ton code!

[SQLpro]

Moins le SGBDR a de travail à faire, mieux cela vaut, donc sans précision de la liste des colonnes cible, il faut bien qu'il aille les retrouver dans le dictionnaire des données. D'ou une requête supplémentaire.

Si vous préciser une liste de colonnes et que pour certaines la valeur est NULL puisque vous ne voulez pas la renseigner, alors vous passez au serveur une requête dont la longeur du texte est supérieur à une requête équivamente dans laquelle on ne mentionnerait que les colonnes utiles.
Certes cela porte sur quelques caractères...
Mais quand une table possède 10 millions de lignes, alors quelques caractères multiplié par 10 millions, cela fait beaucoup de méga octets inutile véhiculé sur le réseau.

A +

[winzou]

Qu'en est-il de la facon de faire comme suit :

Code :

1
2
3
INSERT INTO `table` SET
colonne  = 'valeur',
colonne2 = 'valeur2'

Cela revient-il exactement au même que la version 'excellent' de dessus ?

[orafrance]

cette syntaxe n'est pas correcte

mais sinon ce serait tout aussi bon, l'idée étant de ne pas laisser au SGBD le soin de rechercher les colonnes mais de lui indiquer... ceci étant, c'est rarement ça qui dégrade les performances

[winzou]

bweu, bien sur que si elle est correcte, sous MySQL4 en tout cas

edit : tout dépend de ce que tu appelle correct en fait

[Barbibulle]

Envoyé par winzou

bweu, bien sur que si elle est correcte, sous MySQL4 en tout cas

edit : tout dépend de ce que tu appelle correct en fait

Etonnant car normalement on fait un

Code :

INSERT INTO matable (col, col2) VALUES ('..','...');

ou

Code :

INSERT INTO matable VALUES ('..', '...');

et un

Code :

UPDATE matable SET col=..., col2=...

mais je connaissais pas le

Code :

INSERT INTO matable SET ...

[orafrance]

effectivement barbi c'est ce que je voulais dire