IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Optimisations SGBD Discussion :

Recherche dans db ? diff entre 2 et x champs


Sujet :

Optimisations SGBD

  1. #1
    Nouveau membre du Club
    Homme Profil pro
    opérateur
    Inscrit en
    Avril 2023
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : Belgique

    Informations professionnelles :
    Activité : opérateur

    Informations forums :
    Inscription : Avril 2023
    Messages : 30
    Points : 34
    Points
    34
    Par défaut Recherche dans db ? diff entre 2 et x champs
    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

  2. #2
    Modérateur
    Avatar de al1_24
    Homme Profil pro
    Retraité
    Inscrit en
    Mai 2002
    Messages
    9 085
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 63
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Retraité
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Mai 2002
    Messages : 9 085
    Points : 30 863
    Points
    30 863
    Par défaut
    Bonjour,

    Une troisième voie existe : créer un index sur ces colonnes de recherche.
    Modérateur Langage SQL
    Règles du forum Langage SQL à lire par tous, N'hésitez pas à consulter les cours SQL
    N'oubliez pas le bouton et pensez aux balises
    [code]
    Si une réponse vous a aidé à résoudre votre problème, n'oubliez pas de voter pour elle en cliquant sur
    Aide-toi et le forum t'aidera : Un problème exposé sans mentionner les tentatives de résolution infructueuses peut laisser supposer que le posteur attend qu'on fasse son travail à sa place... et ne donne pas envie d'y répondre.

  3. #3
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 826
    Points : 52 900
    Points
    52 900
    Billets dans le blog
    5
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  4. #4
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Sr. Specialist Solutions Architect @Databricks
    Inscrit en
    Septembre 2008
    Messages
    8 453
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Sr. Specialist Solutions Architect @Databricks
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 453
    Points : 18 383
    Points
    18 383
    Par défaut
    Citation Envoyé par PrincessLoiseau Voir le message
    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.

  5. #5
    Nouveau membre du Club
    Homme Profil pro
    opérateur
    Inscrit en
    Avril 2023
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : Belgique

    Informations professionnelles :
    Activité : opérateur

    Informations forums :
    Inscription : Avril 2023
    Messages : 30
    Points : 34
    Points
    34
    Par défaut
    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 ?

  6. #6
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Sr. Specialist Solutions Architect @Databricks
    Inscrit en
    Septembre 2008
    Messages
    8 453
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Sr. Specialist Solutions Architect @Databricks
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 453
    Points : 18 383
    Points
    18 383
    Par défaut
    Ç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é.

  7. #7
    Nouveau membre du Club
    Homme Profil pro
    opérateur
    Inscrit en
    Avril 2023
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : Belgique

    Informations professionnelles :
    Activité : opérateur

    Informations forums :
    Inscription : Avril 2023
    Messages : 30
    Points : 34
    Points
    34
    Par défaut
    Citation Envoyé par Waldar Voir le message
    ..., 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

  8. #8
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Sr. Specialist Solutions Architect @Databricks
    Inscrit en
    Septembre 2008
    Messages
    8 453
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Sr. Specialist Solutions Architect @Databricks
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 453
    Points : 18 383
    Points
    18 383
    Par défaut
    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.

  9. #9
    Nouveau membre du Club
    Homme Profil pro
    opérateur
    Inscrit en
    Avril 2023
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : Belgique

    Informations professionnelles :
    Activité : opérateur

    Informations forums :
    Inscription : Avril 2023
    Messages : 30
    Points : 34
    Points
    34
    Par défaut
    Citation Envoyé par Waldar Voir le message
    (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

  10. #10
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Sr. Specialist Solutions Architect @Databricks
    Inscrit en
    Septembre 2008
    Messages
    8 453
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Sr. Specialist Solutions Architect @Databricks
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 453
    Points : 18 383
    Points
    18 383
    Par défaut
    Ç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.

  11. #11
    Nouveau membre du Club
    Homme Profil pro
    opérateur
    Inscrit en
    Avril 2023
    Messages
    30
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : Belgique

    Informations professionnelles :
    Activité : opérateur

    Informations forums :
    Inscription : Avril 2023
    Messages : 30
    Points : 34
    Points
    34
    Par défaut
    Le champ est effectivement l'intersection entre une ligne et une colonne. Mauvais use de ma part.

    Citation Envoyé par Waldar Voir le message
    on dit plutôt colonne pour les bases de données)
    Un grand merci pour vos échanges

  12. #12
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 826
    Points : 52 900
    Points
    52 900
    Billets dans le blog
    5
    Par défaut
    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é...

    Nom : Figure 10-011 - Extensions mixtes et uniformes.jpg
Affichages : 154243
Taille : 117,4 Ko
    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....)

    Nom : Figure 10-008 structure page.jpg
Affichages : 3039
Taille : 158,9 Ko
    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)...

    Nom : Figure 10-013 - Structure ligne.jpg
Affichages : 3032
Taille : 86,8 Ko
    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.

    Nom : Figure 10-014 cache et checkpoint.jpg
Affichages : 3039
Taille : 182,0 Ko
    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...
    Nom : Couverture livre SQL server Eyrolles.jpg
Affichages : 3015
Taille : 105,0 Ko

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  13. #13
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 826
    Points : 52 900
    Points
    52 900
    Billets dans le blog
    5
    Par défaut
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  14. #14
    Modérateur
    Avatar de escartefigue
    Homme Profil pro
    bourreau
    Inscrit en
    Mars 2010
    Messages
    10 240
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Loir et Cher (Centre)

    Informations professionnelles :
    Activité : bourreau
    Secteur : Finance

    Informations forums :
    Inscription : Mars 2010
    Messages : 10 240
    Points : 39 273
    Points
    39 273
    Billets dans le blog
    9
    Par défaut
    Précision sur un point de détail :

    Citation Envoyé par SQLpro Voir le message
    [*]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.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 3
    Dernier message: 06/02/2009, 18h39
  2. Rechercher le début d'entrées dans une table
    Par adrien.gendre dans le forum VBA Access
    Réponses: 3
    Dernier message: 17/07/2007, 10h25
  3. Réponses: 5
    Dernier message: 13/07/2007, 14h46
  4. Recherche dans table de BDD par rapport à deux champs
    Par kiops dans le forum Bases de données
    Réponses: 4
    Dernier message: 09/03/2007, 12h21
  5. Réponses: 5
    Dernier message: 27/01/2007, 21h19

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo