Discussion :

[PrincessLoiseau]

Bonjour,

Une question sur l’existence ou non d'une différence de temps de réponse lors d'une recherche portant sur 1, ou 2 champs dans une table.

Y'a aurait il un gain de performance et une utilité à créer deux tables plutôt qu'une.

Par exemple;

Dans le cas ou une table utilise par exemple 25 champs tout type et que, le principal des recherches dans celle-ci portent sur 3 champs, par exemple id et 2 varchar.
Vaut-il mieux créer une table avec ces 3 champs et porter les recherches dans celle-ci, pour ensuite utiliser une autre table différenciée et l'utiliser selon la nécessité.

Merci et la bonne journée

[al1_24]

Bonjour,

Une troisième voie existe : créer un index sur ces colonnes de recherche.

[SQLpro]

https://sqlpro.developpez.com/cours/quoi-indexer/

https://sqlpro.developpez.com/optimisation/indexation/

A +

[Waldar]

Dans le cas ou une table utilise par exemple 25 champs tout type et que, le principal des recherches dans celle-ci portent sur 3 champs, par exemple id et 2 varchar.
Vaut-il mieux créer une table avec ces 3 champs et porter les recherches dans celle-ci, pour ensuite utiliser une autre table différenciée et l'utiliser selon la nécessité.

C'est ce qu'on appelle parfois le partitionnement vertical, ça a son intérêt dans certains cas avec de fortes volumétries.
Sinon vous pouvez utiliser un SGBD colonne, ainsi la question ne se pose plus (mais ces SGBD sont adaptés à de l'analytique et non du transactionnel).

Pour des recherches "simples" je rejoins les préconisations précédentes de mes collègues à savoir l'ajout d'index.

[PrincessLoiseau]

Et sur le plan technique,

Outre l'importance d'ordre des champs.

Vous appliqueriez ceci à partir de quel quantité de ligne attendues.

J'imagine que sous 100 ou 1000, cela n'a pas d'influence.

vous partiriez à partir de ?

[Waldar]

Ça dépend de votre SGBD et du hardware derrière, mais en-dessous de x millions de lignes ça n'a probablement qu'un intérêt limité.

[PrincessLoiseau]

..., mais en-dessous de x millions de lignes ça n'a probablement qu'un intérêt limité.

lol

Un bon ordre des champs et un index me semble déjà pas mal.

Le million de lignes

[Waldar]

J'ai quelques clients qui ont dépassé le billion de lignes (mille milliards ou encore un million de million) dans la même table.

[PrincessLoiseau]

(mille milliards ou encore un million de million) dans la même table.

Si celles-ci sont pleines, il y a le DDR qui ne doit pas rigoler.

Juste une petite dernière sur laquelle j'ai très superficiellement jeter d'un œil.

Concernant les clusters, durant l'enregistrement d'une ligne dans la BD, est-ce que les champs sont enregistrés à la suite, ou est-ce la SGBDR organise l'espace à gauche et à droite selon ses compétences ( ou celle du gestionnaire).
Par exemple : champ1 de 128 octets, champ2 de 256 octects.
Place t'il champ1 et champ2 à la suite sur même cluster, ou priorise t'il par exemple, cluster 1 réservé pour champ1 et de la sorte, le champ1 de la ligne2 sera derrière le champ1 de la ligne1.

Bon après je sors

[Waldar]

Ça dépend des SGBD, mais la majorité essaient de placer le maximum de lignes dans une même page (ou bloc) afin de réduire les IO.
C'est très important pour les applications OLTP qu'une même ligne soit dans une même page, ça permet de la traiter en un seul IO (voire deux si utilisation d'un index secondaire).

Il y a des mécanismes qui permettent de conserver de l'espace vide par page pour faciliter les mises à jour (FILLFACTOR / PCTFREE).

Votre exemple où les champ1 (on dit plutôt colonne pour les bases de données) sont organisés de manière contiguë sont un des principe des bases de données colonnes (Vertica, Sybase IQ, SAP Hana, Redshift, Snowflake, BigQuery...)
Cette technologie colonne est utilisée en analytique pour traiter des volumes de données massifs.

Il y a un peu de tout dans le monde des bases de données.

[PrincessLoiseau]

Le champ est effectivement l'intersection entre une ligne et une colonne. Mauvais use de ma part.

on dit plutôt colonne pour les bases de données)

Un grand merci pour vos échanges

[SQLpro]

Dans la littérature consacrée, les lignes sont enregistrées dans des pages (Oracle pour se distinguer parle de "bloc") dont la taille est estimée en tenant compte :

• du type du système d'exploitation
• du file système sous-jacent

Par exemple

• sous Windows c'est 8 Ko
• Sous Linux c'est souvent du 8 Ko aussi pour la plupart des FS
• Pour ZOS / DB2 c'est de 4 à 32 Ko en fonction de la teille des partitions de stockage...

Cette métrique est calculée par des abaques... Page trop petite = beaucoup d'IO (goulet d'étranglement de la fille d'attente pour accès concurrentiel au disque), trop grande = moins rapide à lire/écrire (encombrement de la mémoire, augmentation du temps d'accès).

Pour les SGBDR d'entreprise qui gèrent leur propre stockage comme Microsoft SQL Server ou Oracle (mais ni MySQL ni PostgreSQL), c'est le moteur de stockage qui assure directement les entrées/sorties (appelées IO en anglais pour Input/Output) dans ce cas la plupart du temps ces pages sont elle même mise dans des groupes de pages contiguës (voire figure 1). Par exemple pour MS SQL Server ce sont des blocs de 8 pages, soit 64 Ko, appelés "extensions" (extents en anglais). Ceci toujours pour des raisons d'efficacité...

Figure 1 - les blocs de 8 pages contigües de SQL Server sont appelées EXTENSIONS (extents en anglais) et peuvent être uniforme (les 8 pages appartiennent à un seul objet : table ou index) ou mixtes (chaque page peut appartenir à un objet différent).

En effet, les SGBDR manipulent les données uniquement en mémoire... Toute lecture (SELECT...), comme toute écriture INSERT, UPDATE, DELETE, MERGE, TRUNCATE, CREATE, ALTER, DROP... est effectuée en mémoire, mais les écritures seront reportées de la mémoire vers les fichiers de données, de temps en temps, de manière asynchrone, par le biais d'une commande interne nommée CHECKPOINT. Cette commande parcoure la mémoire à la recherche des pages "sales" (dirty pages) c'est à dire des pages désynchronisées par rapport aux fichiers de données. Toute écriture physique nécessite la réécriture de l'intégralité de la page, car dans chaque page il y a des métadonnées connexes (par exemple dans MS SQL Server chaque page contient, en sus des lignes et des identifiants de l'objet auquel appartient la page, le nombre d'octets libre, le nombre de ligne vivante et une somme de contrôle pour vérifier l'intégrité du stockage).

Autrement dit chaque écriture mémoire vers disque (IO) est une page.

Au niveau lecture disque vers mémoire, c'est par blocs de page que cela se passe, ce qui permet de faire des lectures anticipées (Par exemple pour MS SQL Server mettre en mémoire 8 pages, même si une seule a été demandée du fait de la lecture par extension).

À l'intérieur de la page (voire figure 2), les lignes sont mises à la queue-leu-leu, sans ordre particulier si c'est une table, avec un placement spécifique pour un index (BTree, hash, bitmap....)

Figure 2 - Structure interne d'une page de données

• L'entête de page contient les métadonnées (par exemple pour SQL Server id de la base, id du fichier, id de la page, id de la table, id de l'index, nombre de lignes vivantes, nombre d'octets libres... en tout 96 octets),
• le bas de la page contient un tableau des offsets de ligne qui indique à quel offset en octet commence telle ou telle ligne (le tableau se lit à l'envers...)
• Entre l'entête et le tableau des offsets de ligne figurent les lignes qui sont généralement de longueur variable (VARCHAR, VARBINARY...).

Cette disposition permet de restreindre l'espace libre par le fait que les lignes croissent vers le bas tandis que que le tableau croit vers le haut.... À la fin il ne reste quasiment plus de place !

Enfin en ce qui concerne les données qui sont dans les lignes, ces dernières sont mise à la queue-leu-leu, soit par leur position ordinale dans la définition de la table (voir dans SELECT * FROM INFORMATION_SCHEMA.COLUMNS) mais dans certains SGBDR haut de gamme comme MS SQL Server une disposition particulière permet d'en accélérer les accès (voire figure 3)...

Figure 3 - Disposition particulière des informations dans SQL Server afin d'optimiser les accès aux données

En effet dans le cas de SQL Server une colonne de taille fixe verra sa données toujours stocké à un offset identique par rapport au début de la ligne et une colonne de taille variable, nécessitera une seule lecture supplémentaire. Dans PostGreSQL, les informations étant stockées les unes après les autres, à l'aide d'un code séparateur, il faut lire successivement toutes les valeurs précédentes pour accéder à la bonne. Par exemple si l'information est cherché dans la 10e colonne, alors il faut lire la ligne octets par octets et s'arrêter après le 9e octets séparateur pour lire le début de l'information recherchée, et s'arrêter au séparateur suivant... ce qui est plus long...
Autre optimisation SQL Server pour les données de taille variables, si l'on cherche un littéral dont la longueur est de 23 octets par exemple, inutile d'aller lire les zones de plus de moins de 23 octets... Donc, une seule lecture...

Enfin, pour les mises à jour, c'est dernières sont écrites d'abord dans le journal de transaction qui assure la persistance pour pouvoir :

• revenir à l'état initial des données en cas d'annulation de la transaction (toute écriture est une transaction journalisée)
• permettre de récupérer les dernières mise à jours non encore répercutées au niveau des fichiers de données en cas de crash du système.

L'ensemble des commandes est décrit en figure 4... depuis la mise à jour d'une information par l'utilisateur jusqu'au CHECKPOINT.

Figure 4 - Gestion des données entre cache (RAM) et disques lors des transactions d'écriture

Tout ceci est extrait de mon livre sur MS SQL Server...

A +

[SQLpro]

Pour info j'ai un client qui a atteint le milliard de ligne sous SQL Server dans une table de suivi téléphonique... il y a déjà 10 ans...

Il y avait déjà des volumes considérables dans de nombreuses bases Microsoft SQL Server, comme Pann Starrs ou centipède avec des bases de plus de 100 To...

A +

[escartefigue]

Précision sur un point de détail :

[*]Pour ZOS / DB2 c'est de 4 à 32 Ko en fonction de la taille des partitions de stockage...

Dans DB2 for Z/OS, la taille de la page est liée au bufferpool. On peut en effet choisir des bufferpools de différentes tailles (4, 8, 16 ou 32k).
Le bufferpool peut être associé à la table ou au tablespace (dans l'ordre CREATE ou ALTER correspondant).
À des fins d'optimisation, les pages sont lues dans le bufferpool (et donc en RAM) et non pas directement sur disque.