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

  1. #21
    Membre éprouvé
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    septembre 2016
    Messages
    573
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2016
    Messages : 573
    Points : 1 059
    Points
    1 059

    Par défaut

    Citation Envoyé par SQLpro Voir le message
    sur chaque serveur la clef autoincrémentée commence à une valeur différentes avec un pas calculé sur le nombre potentiel de machine en réplication.
    par exemple si vous pensez n'avoir jamais plus de 100 machines, alors la machine 1 commence à 1 avec un pas de 100 (IDENTITY(1, 100)...), la machine 2, commence à 2 avec un pas de 100...
    A chacun ses expériences douloureuses.
    Comme indiqué avant cette technique souffre de l'énorme problème des actions "jouant" avec la valeur identity comme par exemple :
    - dbcc checkident https://docs.microsoft.com/fr-fr/sql...ql-server-2017
    - set identity insert https://docs.microsoft.com/fr-fr/sql...ql-server-2017

    Si c'est l'option retenue alors :
    1- il convient d'ajouter une contrainte check en jouant du modulo.
    2- de vérifier que le type choisi convient au besoin de numérotation (n'oublions pas qu'une valeur identity rollbackée est quand même consommée et qu'on peut purger tout ou partie d'une table sans changer la numérotation) dans le temps :
    a- si int : le max est 2 147 483 647 si on prend 100 serveurs le max sera de 21 474 836 pour chacun d'eux, individuellement.
    b- si bigint, la capacité devrait aller, mais on ajoute 8 octets à la table pour éviter de faire usage d'une colonne de 16 octets.

    Dans le cas a- en adoptant l'index cluster sur la PK et la colonne GUID en tant que clé candidate avec un index non cluster, l'activité propre au serveur devrait bien se passer et l'activité de réplication, qui compare l'intégralité des colonnes pour chaque ligne existante des 2 côtés, le surcoût semble correct.

    Dans le cas b- l'écart se resserre. A voir. Pas persuadé que le bilan global soit favorable à cette technique.

    J'avoue que je réagit plus par peur de reproduire l'expérience douloureuse que suite à un POC en faisant varier le nombre d'abonnés (par exemple de 2 à 92 par paliers de 10) et la volumétrie, le tout en charge.
    Ce serait un bon exercice ça !
    L'utilisation de HammerDB (et du TCPH) pour fixer la charge et déterminer la volumétrie pourrait être utile.

    Perso je n'ai ni le temps ni les moyens techniques de le faire.
    Mais je suis preneur du résultat.
    Surtout s'il ne va pas dans mon sens.
    Le savoir est une nourriture qui exige des efforts.

  2. #22
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 940
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 940
    Points : 44 311
    Points
    44 311

    Par défaut

    Citation Envoyé par Michel.Priori Voir le message
    ...
    Si c'est l'option retenue alors :
    1- il convient d'ajouter une contrainte check en jouant du modulo.
    C'est une option fortement recommandée....

    2- de vérifier que le type choisi convient au besoin de numérotation (n'oublions pas qu'une valeur identity rollbackée est quand même consommée et qu'on peut purger tout ou partie d'une table sans changer la numérotation) dans le temps :
    a- si int : le max est 2 147 483 647 si on prend 100 serveurs le max sera de 21 474 836 pour chacun d'eux, individuellement.
    b- si bigint, la capacité devrait aller, mais on ajoute 8 octets à la table pour éviter de faire usage d'une colonne de 16 octets.
    Aucune importance. Même phénomène en pire dans NEWSEQUENTIALID() car il est perdu en cas de ROLLBACK et comme il est partagé par toutes les table il n'est pas continu dans une même table !

    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...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  3. #23
    Membre éprouvé
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    septembre 2016
    Messages
    573
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2016
    Messages : 573
    Points : 1 059
    Points
    1 059

    Par défaut

    Citation Envoyé par SQLpro Voir le message
    Même phénomène, en pire dans NEWSEQUENTIALID(), car il est perdu en cas de ROLLBACK et comme il est partagé par toutes les table il n'est pas continu dans une même table !
    Heu... non.

    On parle bien de de l'épuisement des valeurs, épuisement comparé entre identity (avec un pas de 100) et NEWSEQUENTIALID().
    OK.
    L'aspect continu est perdu avec un pas de 100. Donc ce côté les 2 solutions sont équivalentes.

    Comparer l'épuisement d'un range sur 4 octets (INT, dont la moitié des valeurs sont en négatif) à un range de 16 octets (UNIQUEIDENTIFIER) représente une large différence opérationnelle.

    Le fait que la fonction NEWSEQUENTIALID() donne un nombre unique à travers le temps et les machines ET supérieur à la dernière valeur ne garanti en rien que la moyenne des écarts soit d'environ 100. mais de là à penser qu'elle est de 2^12... y a de la marge.

    Ce calcul est d'autant plus inutile car la fonction NEWSEQUENTIALID() repart à chaque redémarrage en fonction d'une valeur aléatoire.
    https://docs.microsoft.com/fr-fr/sql...ql-server-2017

    Et de toute façon si problème il devait y avoir, vu que réplication de fusion la met en place avec cette fonctionnalité on va devoir faire avec.
    La seule question est de savoir si on "double le coup" ou pas.
    Le savoir est une nourriture qui exige des efforts.

  4. #24
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 940
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 940
    Points : 44 311
    Points
    44 311

    Par défaut GUID vs IDENTITY dans SQL Server

    Citation Envoyé par Michel.Priori Voir le message
    Heu... non.

    On parle bien de de l'épuisement des valeurs.
    ... ???? d'ou vous sortez tout d'un coup cette notion d'épuisement des valeurs ???

    ... évidemment qu'entre de 4 octets et du 16 octets l'épuisement sera plus rapide; Mais pourquoi vous cantonner au 4 octets (INT) alors qu'il y a le BIGINT (8 octets) ?
    Déjà avec le BIGINT, à raison de 1 000 000 de lignes insérées par seconde, il vous faudra 584 554 années, 17 jours, 8 heures, 1 minute et 49 secondes pour épuiser le stocke des valeurs potentielles.
    Alors même avec 1000 instances en parallèle, cela vous laisse : 5 845 années soit un peu plus de 58 siècles.
    De plus l'identity étant propre à chaque table (contrairement au GUID qui est global), si vous avez 100 tables, vous aurez donc, épuisez toutes les valeurs de toutes les tables dans environ 58 millions d'années.

    Quatre inconvénients supplémentaires du fait du GUID par rapport à l'IDENTITY :
    1) deux fois plus d'octets => moins de performances car la lecture d'un GUID nécessite 2 passes dans le processeurs alors qu'un INT n'en fait qu'une seule. Et si c'est l'index cluster ce sont tous les index secondaires qui sont impactés....
    Ce sont aussi des tables notablement plus grosses, car il faut aussi compter les clefs étrangères qui seront aussi deux fois plus grosses... tout ceci n'est pas négligeable en terme de maintenance (sauvegarde - et la compression n'est pas efficace sur un GUID - maintenance des index et statistiques...).
    2) le hot spot créé par le calcul du GUID. En effet les performances sont nettement ralenties lorsqu'il y a une concurrence des INSERT dans différentes tables du fait de différents utilisateurs, car c'est le même bout de code (une API Windows) qui calcule les GUID. Les performances deviennent d'ailleurs erratique en cas de très forte concurrence...
    3) la lecture du GUID... Essayez donc au téléphone (dans la cadre d'un SAV applicatif par exemple) de demandez à un utilisateur de vous communiquer la valeur d'un GUID... neuf fois sur 10 il se trompera, alors qu'avec un simple chiffre il y a moins de risque d'erreur !
    4) pas de possibilité de récupérer facilement la valeur d'un GUID après insertion, alors que c'est si simple avec un IDENTITY (fonction SCOPE_IDENTITY()...)

    Autre inconvénient, mais je ne sais pas s'il est toujours de mise, pour des raisons de dispersion universelle des valeurs, le GUID est calculé par rapport à une lecture de la MAC adresse du PC. En multipliant le nombre de GUID généré par une même machine on peut reconstituer la MAC adresse de la machine. Ceci était très vrai au tout début des algorithmes de génération des GUID....

    Pour info, il y a 10 ans, nous avons eut à auditer une base de données gouvernementale, liée à la santé (je ne peut en dire plus, mais plusieurs millions de bénéficiaires...); Toutes les clefs était en GUID et malgré le NEWSEQUENTIALID() c'était une catastrophe.... Il a fallut changer en INT/BIGINT/SMALLINT avec IDENTITY selon le cas pour chaque table.


    A +

    refs :
    https://blogs.msdn.microsoft.com/sql...vs-int-debate/
    https://www.mssqltips.com/sqlservert...t-versus-guid/
    https://stackoverflow.com/questions/...cifically-rega
    https://sqlperformance.com/2015/01/s...its-disk-space
    https://sqlserverperformance.wordpre...in-sql-server/
    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...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  5. #25
    Expert confirmé Avatar de 7gyY9w1ZY6ySRgPeaefZ
    Homme Profil pro
    dba
    Inscrit en
    juillet 2007
    Messages
    4 577
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Canada

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : juillet 2007
    Messages : 4 577
    Points : 4 996
    Points
    4 996

    Par défaut

    Citation Envoyé par SQLpro Voir le message
    ... évidemment qu'entre de 4 octets et du 16 octets l'épuisement sera plus rapide; Mais pourquoi vous cantonner au 4 octets (INT) alors qu'il y a le BIGINT (8 octets) ?
    Déjà avec le BIGINT, à raison de 1 000 000 de lignes insérées par seconde, il vous faudra 584 554 années, 17 jours, 8 heures, 1 minute et 49 secondes pour épuiser le stocke des valeurs potentielles.
    Alors même avec 1000 instances en parallèle, cela vous laisse : 5 845 années soit un peu plus de 58 siècles.
    J'avais fait cette démonstration lorsque l'on m'a imposé un bigint auto-incrémenté en clef primaire pour une table « potentiellement » TRÈS active...
    1 an après la mise en prod, on atteignait pas 100 lignes...
    j'en ris encore !

  6. #26
    Membre éprouvé
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    septembre 2016
    Messages
    573
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 52
    Localisation : France, Isère (Rhône Alpes)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : Conseil

    Informations forums :
    Inscription : septembre 2016
    Messages : 573
    Points : 1 059
    Points
    1 059

    Par défaut

    Citation Envoyé par SQLpro Voir le message
    ... ???? d'ou vous sortez tout d'un coup cette notion d'épuisement des valeurs ???
    Des calculs exprimés dans le point n°2 que vous avez repris en commentaire.


    Citation Envoyé par SQLpro Voir le message
    ... évidemment qu'entre de 4 octets et du 16 octets l'épuisement sera plus rapide; Mais pourquoi vous cantonner au 4 octets (INT) alors qu'il y a le BIGINT (8 octets) ?
    Déjà avec le BIGINT, à raison de 1 000 000 de lignes insérées par seconde, il vous faudra 584 554 années, 17 jours, 8 heures, 1 minute et 49 secondes pour épuiser le stocke des valeurs potentielles.
    On est d'accord, avec un bigint on est large.
    C'était dis dans le fameux point 2.
    Merci pour les calculs

    Citation Envoyé par SQLpro Voir le message
    Alors même avec 1000 instances en parallèle, cela vous laisse : 5 845 années soit un peu plus de 58 siècles.
    De plus l'identity étant propre à chaque table (contrairement au GUID qui est global), si vous avez 100 tables, vous aurez donc, épuisez toutes les valeurs de toutes les tables dans environ 58 millions d'années.
    Là, par contre le calcul me semble foireux : le remplissage des tables n'est pas homogène, ni même le remplissage des bases.
    Le point bloquant sera atteint à la 1ere table arrivant à court de n° (et non pas de lignes, déjà expliqué la différence)

    Citation Envoyé par SQLpro Voir le message
    Quatre inconvénients supplémentaires du fait du GUID par rapport à l'IDENTITY :
    1) deux fois plus d'octets => moins de performances car la lecture d'un GUID nécessite 2 passes dans le processeurs alors qu'un INT n'en fait qu'une seule.
    Plusieurs choses :
    Occupation de l'espace par rapport au type : UNIQUEIDENTIFIER = 2 BIGINT = 4 INT
    Choix d'avoir une colonne UNIQUEIDENTIFIER : rappelons que dans le cas d'une réplication de fusion cette colonne est imposée, donc présente.

    C'est la colonne UNIQUEIDENTIFIER qui va être utilisé par le mécanisme de réplication.
    On réécrit tout en open source et on en informe bilou ?

    Citation Envoyé par SQLpro Voir le message
    Et si c'est l'index cluster ce sont tous les index secondaires qui sont impactés....
    Vrai.
    Mais on avait déjà parlé de bien gérer ça, non ?

    Citation Envoyé par SQLpro Voir le message
    Ce sont aussi des tables notablement plus grosses, car il faut aussi compter les clefs étrangères qui seront aussi deux fois plus grosses...
    Re vrai.


    Citation Envoyé par SQLpro Voir le message
    tout ceci n'est pas négligeable en terme de maintenance (sauvegarde - et la compression n'est pas efficace sur un GUID - maintenance des index et statistiques...).
    2) le hot spot créé par le calcul du GUID. En effet les performances sont nettement ralenties lorsqu'il y a une concurrence des INSERT dans différentes tables du fait de différents utilisateurs, car c'est le même bout de code (une API Windows) qui calcule les GUID. Les performances deviennent d'ailleurs erratique en cas de très forte concurrence...
    Ben si la présence de la colonne est imposée, ce sera idem, kifkif bourricot.

    Citation Envoyé par SQLpro Voir le message
    3) la lecture du GUID... Essayez donc au téléphone (dans la cadre d'un SAV applicatif par exemple) de demandez à un utilisateur de vous communiquer la valeur d'un GUID... neuf fois sur 10 il se trompera, alors qu'avec un simple chiffre il y a moins de risque d'erreur !
    Vaste débat de savoir quand et comment afficher les identifiants techniques aux utilisateurs.
    Je préfère avoir à déterminer par moi même l'identifiant de Mme Duchemu, même s'il y en existe plusieurs, que de parler du client 120356.
    Et si on doit passer par une liste d'identifiants alors là le bon vieux copier-coller fait l'affaire.
    L'avantage est que si on se trompe de table (si, si je l'ai vu ) les valeurs étant globalement unique, la requête ne renvoie rien, plutôt que des lignes erronées.

    Citation Envoyé par SQLpro Voir le message
    4) pas de possibilité de récupérer facilement la valeur d'un GUID après insertion, alors que c'est si simple avec un IDENTITY (fonction SCOPE_IDENTITY()...)
    Changement de pratique.
    On fait comment avec une séquence ?
    Au passage faudra le dire à Larry (Oracle) que c'est pas pratique son idée de séquence

    Citation Envoyé par SQLpro Voir le message
    Autre inconvénient, mais je ne sais pas s'il est toujours de mise, pour des raisons de dispersion universelle des valeurs, le GUID est calculé par rapport à une lecture de la MAC adresse du PC. En multipliant le nombre de GUID généré par une même machine on peut reconstituer la MAC adresse de la machine. Ceci était très vrai au tout début des algorithmes de génération des GUID....
    Pour attaquer un serveur grâce à sa MAC address il faut être dans le même sous réseau dans le cadre de l'unicast.
    Il existe des doublons de MAC address : 6 octets c'est plus qu'en IPv4 mais moins qu'en IPV6...
    Les machines virtuelles sont aussi des grandes pourvoyeuses de mac en double.

    Citation Envoyé par SQLpro Voir le message
    Pour info, il y a 10 ans, nous avons eut à auditer une base de données gouvernementale, liée à la santé (je ne peut en dire plus, mais plusieurs millions de bénéficiaires...); Toutes les clefs était en GUID et malgré le NEWSEQUENTIALID() c'était une catastrophe.... Il a fallut changer en INT/BIGINT/SMALLINT avec IDENTITY selon le cas pour chaque table.
    Ben ça je veux bien le croire.
    Surtout passer au SMALLINT ça du faire du bien.
    Il me manque de savoir si vous avez conservé les colonnes uniqueidentifier dans chaque table.
    Si c'est le cas, au moins pour la partie hors réplication, l'écart de performance pour les tables en BIGINT est valable.
    Je m'emballe peut être. Est-ce que l'écart de performance a été mesuré globalement ou possède t'on des métriques spécifiques sur les tables dont l'ID est passé en BIGINT ?

    Si ce n'est le cas, le travail nécessaire a été fait, et, je pense même, bien fait. Mais l'exemple n'est pas valide dans le sens où ce travail n'est pas transposable à une réplication de fusion.

    NOTE : j'espère que notre petit débat intéresse les autres internautes car sinon on pourrait débattre de ça autours d'une bière, non ?
    Le savoir est une nourriture qui exige des efforts.

  7. #27
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    18 940
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 18 940
    Points : 44 311
    Points
    44 311

    Par défaut

    Pour info, pour l'IDENTITY tu peut aussi utiliser un DECIMAL et donc monter à 38 chiffres (DECIMAL(38,0)...) soit une quantité d'entier de 10^38 - 1, donc, plus que l'UNIQUEIDENTIFFIER !!! (environ 3 fois plus....)

    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...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  8. #28
    Modérateur

    Homme Profil pro
    Consultant Teradata
    Inscrit en
    septembre 2008
    Messages
    7 844
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 41
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Consultant Teradata

    Informations forums :
    Inscription : septembre 2008
    Messages : 7 844
    Points : 15 562
    Points
    15 562

    Par défaut

    Citation Envoyé par Michel.Priori Voir le message
    Changement de pratique.
    On fait comment avec une séquence ?
    Au passage faudra le dire à Larry (Oracle) que c'est pas pratique son idée de séquence
    Une fois que la séquence a été incrémentée - et je suis d'accord cette restriction pourrait être levée :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    select masequence.currval from dual;

Discussions similaires

  1. Avenir des bases de données relationnelles ?
    Par LordBob dans le forum Décisions SGBD
    Réponses: 53
    Dernier message: 30/10/2005, 23h27
  2. Réponses: 9
    Dernier message: 25/07/2005, 15h56
  3. Noms des bases de données
    Par abdou.sahraoui dans le forum Administration
    Réponses: 8
    Dernier message: 01/09/2004, 15h21
  4. structure des bases de données Palm
    Par nomdutilisateur dans le forum Bases de données
    Réponses: 2
    Dernier message: 17/01/2004, 17h47
  5. Réponses: 3
    Dernier message: 24/10/2003, 21h46

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