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

MS SQL Server Discussion :

transfuge de Access à SQL SERVER 2008


Sujet :

MS SQL Server

  1. #1
    Membre régulier
    Inscrit en
    Novembre 2005
    Messages
    337
    Détails du profil
    Informations forums :
    Inscription : Novembre 2005
    Messages : 337
    Points : 120
    Points
    120
    Par défaut transfuge de Access à SQL SERVER 2008
    bonjour.
    je suis nouveau en sql server 2008 que je trouve plus qu’intéressant. seulement voilà, en access, on peut indexer chaque champ d'une table "avec doublons" ou "sans doublons".
    je voudrais savoir où sql server donne la main pour indexer un champ d'une table.

    par ailleurs, la construction de certaines relations sous sql server me ramène le message d'erreur suivant:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    table «*CONTRAT*» enregistrée
    table «*CUBAGE*»
    - Impossible de créer la relation «*FK_CUBAGE_CONTRAT*». 
    Introducing FOREIGN KEY constraint 'FK_CUBAGE_CONTRAT' on table 'CUBAGE' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
    Could not create constraint. See previous errors.
    je voulais relier les tables "contrat" et "cubage" par le champ "contrat" qui est une clé primaire dans la table "contrat"
    je voudrais savoir ce que signifie ce message et qu'est ce que je dois faire pour y remédier. je voudrais noter qu'en ACCESS, cette relation marche.

    merci d'avance

  2. #2
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    Bonjour,

    seulement voilà, en access, on peut indexer chaque champ d'une table "avec doublons" ou "sans doublons".
    C'est possible avec SQL Server :

    Si la colonne doit contenir des valeurs uniques :

    - Vous devez créer un index cluster
    - Cela peut se faire avec le concepteur de table ...
    - ... ou en ajoutant une contrainte de clé primaire : ALTER TABLE maTable ADD CONSTRAINT PK_maTable PRIMARY KEY(colonnes)
    - ... ou en ajoutant une contrainte d'unicité et en spécifiant que l'index sous-jacent à celle-ci doit être cluster : ALTER TABLE maTable ADD CONSTRAINT UQ_maTable_colonnes UNIQUE (colonnes) CLUSTERED
    - Notez qu'il ne peut y avoir qu'un seul index non-cluster par table

    Si la colonne contient des valeurs qui peuvent être en double, vous pouvez créer un index non-cluster :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    CREATE INDEX IX_maTable_mesColonnes
    ON maTable (mesColonnes)
    je voulais relier les tables "contrat" et "cubage" par le champ "contrat" qui est une clé primaire dans la table "contrat"
    je voudrais savoir ce que signifie ce message et qu'est ce que je dois faire pour y remédier. je voudrais noter qu'en ACCESS, cette relation marche.
    Sans le code et la structure des tables, difficile de vous aider ...

    @++

  3. #3
    SLE
    SLE est déconnecté
    Membre éclairé Avatar de SLE
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Janvier 2004
    Messages
    604
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 50
    Localisation : Belgique

    Informations professionnelles :
    Activité : Consultant informatique

    Informations forums :
    Inscription : Janvier 2004
    Messages : 604
    Points : 799
    Points
    799
    Par défaut
    Citation Envoyé par elsuket Voir le message
    Notez qu'il ne peut y avoir qu'un seul index non-cluster par table
    C'est le contraire, non ? Un seul index CLUSTERED et plusieurs NON CLUSTERED

  4. #4
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 945
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 945
    Points : 30 716
    Points
    30 716
    Billets dans le blog
    16
    Par défaut
    Bonjour,


    Citation Envoyé par elsuket Voir le message
    Si la colonne doit contenir des valeurs uniques :
    - Vous devez créer un index cluster

    Si la colonne contient des valeurs qui peuvent être en double, vous pouvez créer un index non-cluster :
    Un index de type NON UNIQUE (valeurs en double acceptées) peut être CLUSTER, en conséquence de quoi le (ou les) index de type UNIQUE branchés sur une table peuvent ne pas être CLUSTER, quels qu'ils soient.

    Cela dit, qu'il soit UNIQUE ou pas, le choix de l'index CLUSTER est crucial, puisqu'on ne peut en avoir qu'un seul par table comme le rappelle SLE .
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  5. #5
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 945
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 945
    Points : 30 716
    Points
    30 716
    Billets dans le blog
    16
    Par défaut
    Venons-en à votre message d’erreur. Vu sa teneur, on peut penser que les relations entre CONTRAT et CUBAGE ressemblent à ceci (en conformité avec la théorie relationnelle) :


    Le texte en bleu a trait au métabolisme des données (les structures des bases de données ne sont pas limitées qu’à leur seul aspect anatomique...) :

    « CASCADE » signifie qu’à l’occasion de la suppression d’un contrat, des stimuli partent simultanément l’un par la gauche (chemin A) et l’autre par la droite (chemin B), avec pour effet la suppression simultanée des cubages (que sont-ce donc ces choses-là ?) en relation avec ce contrat (l’émission de stimuli vaut aussi pour la modification des clés primaires : les valeurs des clés étrangères de CUBAGE doivent être en permanence égales à celles des clés primaires de CONTRAT, même si changer la valeur d’une clé primaire est quelque chose qui mérite qu’on se fasse taper sur les doigts). A l’opposé, si l’option retenue est « NO CASCADE » (RESTRICT ou NO ACTION selon les SGBD), la suppression d’un contrat n’est possible que si ce contrat n’est pas référencé, car s’il l’est, la tentative échouera.

    Dans le contexte ACCESS, l’équivalent du diagramme ci-dessus est le suivant (ACCESS est vraiment très mauvais pour représenter les diagrammes...) :


    Le problème avec SQL Server est que, parce que les stimuli peuvent emprunter deux chemins pour aller d’un point à un autre, en l’espèce les chemins A et B, alors si l’on a l’option « CASCADE » pour un chemin, l’option pour l’autre chemin doit impérativement être « NO ACTION » (le « NO CASCADE » de SQL Server). Cette exigence n’est pas normale et elle est due au fait que l’algorithme utilisé par ce SGBD est manifestement trop fruste. Ma remarque valait du reste pour DB2 quand enfin on put mettre en œuvre l’intégrité référentielle avec ce SGBD (c’était en 1988). La partie cocasse de cette affaire est qu’avec DB2, si pour un chemin, on retenait l’option « CASCADE », alors pour l’autre chemin c’était obligatoirement « CASCADE » itou ! On avait même droit à la justification de ce choix (désolé, je n’ai pas la justification valant pour SQL Server, mais un MVP palliera sans doute mon incurie) :


    Il est évident qu’à la réflexion, du fait d’un algorithme trop fruste les arguments des parents de DB2 ne tiennent pas la route (pas plus que ceux des parents de SQL Server, ACCESS serait donc meilleur sur ce point ? ) Quoi qu’il en soit, le principe général de l’algorithme permettant de gommer les dangers évoqués par IBM est fourni par Chris Date, dans Database Explorations, Essays on The Third Manifesto and Related Topics, à la page 224.

    En ce qui vous concerne, vous serez malheureusement obligé de vous plier aux exigences de SQL Server : « CASCADE » d’un côté et « NO ACTION » de l’autre... Soit vous le faites directement dans la base de données ACCESS, soit vous interceptez la contrainte de clé étrangère coupable pour y modifier l’option. Si le choix du chemin à passer en « NO ACTION » est problématique d'un point de vue sémantique, vous pourrez exposer ici la difficulté qui se présente.
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  6. #6
    SLE
    SLE est déconnecté
    Membre éclairé Avatar de SLE
    Homme Profil pro
    Consultant informatique
    Inscrit en
    Janvier 2004
    Messages
    604
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 50
    Localisation : Belgique

    Informations professionnelles :
    Activité : Consultant informatique

    Informations forums :
    Inscription : Janvier 2004
    Messages : 604
    Points : 799
    Points
    799
    Par défaut
    Waw ! ça c'est de la réponse !
    +1

  7. #7
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    Citation Envoyé par SLE
    C'est le contraire, non ? Un seul index CLUSTERED et plusieurs NON CLUSTERED
    Héhé si, il était vraiment trop tard quand j'ai écrit ce message ... Merci de l'avoir relevé

    Citation Envoyé par fmsrel
    Un index de type NON UNIQUE (valeurs en double acceptées) peut être CLUSTER, en conséquence de quoi le (ou les) index de type UNIQUE branchés sur une table peuvent ne pas être CLUSTER, quels qu'ils soient.

    Cela dit, qu'il soit UNIQUE ou pas, le choix de l'index CLUSTER est crucial, puisqu'on ne peut en avoir qu'un seul par table comme le rappelle SLE .
    Tout à fait, merci également d'avoir relevé ces deux erreurs.
    Toutes mes excuses à djelloharmel.

    Faut que je prenne des vacances moi

    @++

  8. #8
    Membre expert Avatar de iberserk
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    Novembre 2004
    Messages
    1 795
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Gironde (Aquitaine)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2004
    Messages : 1 795
    Points : 3 173
    Points
    3 173
    Par défaut
    Cela dit, qu'il soit UNIQUE ou pas, le choix de l'index CLUSTER est crucial, puisqu'on ne peut en avoir qu'un seul par table comme le rappelle SLE
    ... et qu'il est référencé par tous les index non cluster...
    Prendre conscience, c'est transformer le voile qui recouvre la lumière en miroir.
    MCTS Database Development
    MCTS Database Administration

  9. #9
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 945
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 945
    Points : 30 716
    Points
    30 716
    Billets dans le blog
    16
    Par défaut
    Bonsoir,


    Citation Envoyé par iberserk Voir le message
    ... et qu'il est référencé par tous les index non cluster...
    J’avais vaguement entendu parler de cela il y a peu, mais sans y prêter attention. A vue de nez, on peut dire que ce principe de référence systématique à l’index cluster est une calamité, puisqu’en plus de traverser les niveaux de l’index non cluster, il faut que le SGBD se paye aussi la traversée de ceux de l’index cluster avant d’accéder aux données utiles de la table ! (A moins que celles-ci soient hébergées par l’index non cluster). Avez-vous plus d’informations à ce sujet ? De mon côté, je vous invite à voir comment sont organisés les index avec DB2, lequel évite soigneusement ces accès supplémentaires bien mal venus.

    Si votre curiosité est piquée, je fournis ici la description d'une organisation "économe" en me basant donc sur DB2 (cf. paragraphe « Des index »), mais je devrais modifier cette partie de mon message pour préciser que si avec DB2 tous les index attaquent directement les pages de données, d’autres SGBD mettent en oeuvre des méthodes différentes...

    Pour une description plus approfondie, avec des dessins, voyez aussi ici et . En me relisant, je me rends compte qu’à l’époque je n’avais pas examiné l’organisation des index selon les autres SGBD et, suite à votre remarque, je ressors cette réponse que j’avais faite à CinePhil qui avait formulé une question que je trouvais étrange et qui maintenant prend tout son sens :
    « Pourquoi voudriez-vous que DB2 tienne compte de l’index de la clé primaire (à supposer que celle-ci ait été définie...) quand vous créez un index sur le nom de la vache ? En revanche, si cela vous arrange, vous pouvez vous-même obtenir l’équivalent ».
    Je signale en passant que dans cette réponse, la formule « obtenir l’équivalent » ne remet évidemment pas en cause le principe DB2 de l’accès direct systématique aux pages de données à partir des feuilles de l’index, il s’agit seulement d’inclure dans la clé de cet index la (ou les) colonne(s) de la clé primaire.

    Merci iberserk pour l’information.
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  10. #10
    Expert éminent sénior
    Avatar de mikedavem
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2005
    Messages
    5 450
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Distribution

    Informations forums :
    Inscription : Août 2005
    Messages : 5 450
    Points : 12 891
    Points
    12 891
    Par défaut
    J’avais vaguement entendu parler de cela il y a peu, mais sans y prêter attention. A vue de nez, on peut dire que ce principe de référence systématique à l’index cluster est une calamité, puisqu’en plus de traverser les niveaux de l’index non cluster, il faut que le SGBD se paye aussi la traversée de ceux de l’index cluster avant d’accéder aux données utiles de la table !
    Tout dépend Si l'index non cluster suffit à répondre aux besoins de la requête alors il n'y aura pas besoin d'aller voir l'index cluster. On parle d'index couvrant dans ce cas.

    Le fait que chaque index non cluster d'une table référence un index cluster est plutôt une bonne chose dans la plupart des cas (en tout cas sur SQL Server) En effet si l'on prend un index non cluster sur une table HEAP (donc le niveau feuille de l'index possède un ROW ID vers la donnée utile ...) il faudra procéder à un scan de la table via sa page IAM (Index Allocation Map) qui est souvent bien plus coûteux que la traversée d'un éventuel cluster (le coût de la traversée représentant globalement le nombre de niveaux existant).

    ++

  11. #11
    Membre expert Avatar de iberserk
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    Novembre 2004
    Messages
    1 795
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Gironde (Aquitaine)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2004
    Messages : 1 795
    Points : 3 173
    Points
    3 173
    Par défaut
    A vue de nez, on peut dire que ce principe de référence systématique à l’index cluster est une calamité, puisqu’en plus de traverser les niveaux de l’index non cluster, il faut que le SGBD se paye aussi la traversée de ceux de l’index cluster avant d’accéder aux données utiles de la table
    Votre nez peut parfois vous tromper :-)

    Je vais bien entendu lire votre post sur DB2...

    Notez que les index NON CLUSTER possèdent simplement un pointeur vers l'index CLUSTER, qui est lui même ordonné et possède les données de la ligne au niveau de sa page de données, puisque l'index cluster EST la table.
    Prendre conscience, c'est transformer le voile qui recouvre la lumière en miroir.
    MCTS Database Development
    MCTS Database Administration

  12. #12
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 945
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 945
    Points : 30 716
    Points
    30 716
    Billets dans le blog
    16
    Par défaut
    Bonsoir,


    Citation Envoyé par mikedavem Voir le message
    Si l'index non cluster suffit à répondre aux besoins de la requête alors il n'y aura pas besoin d'aller voir l'index cluster. On parle d'index couvrant dans ce cas.
    D’accord, cela correspond à ce que j’écrivais dans mon message précédent à propos des données à récupérer :
    Citation Envoyé par fsmrel Voir le message
    A moins que celles-ci se trouvent hébergées par l’index non cluster



    Citation Envoyé par mikedavem Voir le message
    Le fait que chaque index non cluster d'une table référence un index cluster est plutôt une bonne chose dans la plupart des cas (en tout cas sur SQL Server) En effet si l'on prend un index non cluster sur une table HEAP (donc le niveau feuille de l'index possède un ROW ID vers la donnée utile ...) il faudra procéder à un scan de la table via sa page IAM (Index Allocation Map) qui est souvent bien plus coûteux que la traversée d'un éventuel cluster (le coût de la traversée représentant globalement le nombre de niveaux existant).
    Pour reprendre l’exemple évoqué par CinePhil, il est un fait que le balayage des pages IAM n’est pas d’un très bon rendement (euphémisme !) quand par exemple on cherche à récupérer les données concernant un bovin parmi 67 000 000... D’où la nécessité de définir au plus vite l’index cluster. Par comparaison, avec DB2 le problème ne se pose pas, puisque tous les index, qu’ils soient de type cluster ou non, sont des arbres. Pour la petite histoire, si on omet de préciser qu’il est cluster, le 1er index que l’on crée pour une table le devient de facto.


    Citation Envoyé par iberserk Voir le message
    Notez que les index NON CLUSTER possèdent simplement un pointeur vers l'index CLUSTER, qui est lui même ordonné et possède les données de la ligne au niveau de sa page de données, puisque l'index cluster EST la table.
    D’accord. Pour mieux comprendre l’organisation des index selon SQL Server, j’ai lu ici, et .


    Citation Envoyé par iberserk Voir le message
    Citation Envoyé par fsmrel Voir le message
    A vue de nez, on peut dire que ce principe de référence systématique à l’index cluster est une calamité, puisqu’en plus de traverser les niveaux de l’index non cluster, il faut que le SGBD se paye aussi la traversée de ceux de l’index cluster avant d’accéder aux données utiles de la table
    Votre nez peut parfois vous tromper :-)
    Gommons la fin de la phrase : « avant d’accéder aux données utiles de la table ». Il reste que mon nez n’est peut-être pas si mauvais :-). Reprenons l’exemple des 67 000 000 de bovins de CinePhil. Hypothèse : la clé primaire de la table des bovins est composée d’un attribut auto-incrémenté (appelons-le BovinId) et l’index cluster (appelons-le X1) est construit à partir de cet attribut. Définissons un index non cluster (et non couvrant) dont la clé est représentée par le nom des bovins (appelons BovinNom l’attribut correspondant et X2 l’index lui-même).

    Soit H1 la hauteur (le nombre de niveaux) de l’index cluster X1, H2 la hauteur de l’index non cluster X2 et comptons les accès pour récupérer l’ensemble des données de la vachette nommée Zaza.

    Avec DB2 on comptera en tout H2 + 1 accès (ce dernier accès portant sur le table space hébergeant la table). DB2 annonce H2 = 4 pour 67 000 000 de bovins (pour une clé de 32 octets, que ses valeurs soient compressées ou non dans l’index, que l’on prévoie ou non du free space, etc.) En tout, on compte donc 5 accès.

    Si je ne me suis pas planté dans ma lecture de la documentation, avec SQL Server, on comptera H2 + H1 accès (dont pour H1, 1 accès à la racine de X1 et 1 accès à la feuille contenant les données de Zaza).

    Pour 67 000 000 de bovins, je suppose que H1 est > 1, au pif :-) de l’ordre de 4 : 1 pour la racine + 2 pour les nœuds intermédiaires + 1 pour la feuille contenant les données.

    Pour résumer, avec DB2 on compte exactement 5 accès pour récupérer les données de Zaza parmi 67 000 000 de bovins (voire 6 accès si le table space hébergeant la table était désorganisé, mais ne mélangeons pas tout...) Avec SQL Server, qu’en est-il ? 8 accès ? Plus ? Moins ? Le partitionnement de X2 permettrait-il d’en réduire la hauteur ? Si oui, de combien ? En tout cas, je ne trouve pas inintéressant de comparer les organisations des index selon les SGBD...
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  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 716
    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 716
    Points : 52 380
    Points
    52 380
    Billets dans le blog
    4
    Par défaut
    François ton calcul est proche de la réalité. Mais tu oublie une chose : le problème de la fragmentation et de la défrag.

    En effet dans le cadre des index cluster sur clef immuable (auto incrément par exemple) il n'y a aucune mise à jour des index secondaire en cas de défragmentation de la table....

    Le problème est de bien choisir la composition de l'index cluster... Alors quand on voit des clef primaire de type VARCHAR(255) ou encore un GUID, ça donne froid dans le dos....

    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
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 945
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 945
    Points : 30 716
    Points
    30 716
    Billets dans le blog
    16
    Par défaut
    Bonjour à tous,


    Citation Envoyé par SQLpro Voir le message
    ton calcul est proche de la réalité
    C'est-à-dire 8 accès quand tout est nickel (pas de désorganisation) ?


    Citation Envoyé par SQLpro Voir le message
    tu oublie une chose : le problème de la fragmentation
    Certes non, je n'oublie pas ! C’est un problème auquel j’étais confronté déjà en 1970 (ça nous rajeunit !) et qui m’a accompagné tout au long de ma vie de DBA, c’tait l’bon temps comme dit l’autre... Je sous-entendais ce problème quand j’ai écrit : « voire 6 accès si le table space hébergeant la table était désorganisé ». En l’occurrence, s’il n’y a pas désorganisation, le nombre de lectures de pages par DB2 pour accéder aux données de la vachette Zaza est égal à 5, à savoir 4 lectures à l'occasion de la traversée de l’index X2, plus une lecture pour accéder à la page de données. Quand je dis que l’on peut avoir 6 accès, je veux dire que suite à UPDATE, la ligne L1 affectée à la vachette Zaza dans la table peut grossir et ne plus pouvoir tenir dans la page P1 hébergeant L1, auquel cas L1 migre dans une autre page, disons P2 (le plus possible au voisinage de P1 sur le disque pour des raisons de performance). Mais, quel que soit l’index, « primaire » ou « secondaire », celui-ci n’est pas affecté, il adresse toujours L1 dans P1 et c’est dans P1 que l’on trouve un renvoi (par pointeur) à l’adresse actuelle de L1, quelles que soient ses pérégrinations au fil des updates.

    J’explique la chose ici, où l’on voit CinePhil grossir tandis que SQLpro reste stable (je suis resté prudemment silencieux sur les excès pondéraux de fsmrel).


    Citation Envoyé par SQLpro Voir le message
    dans le cadre des index cluster sur clef immuable (auto incrément par exemple) il n'y a aucune mise à jour des index secondaire en cas de défragmentation de la table
    Je l'entends ainsi, mais reprenons l’exemple de la vachette Zaza, en relation cette fois-ci avec l’index primaire (de hauteur H). Quelle avanie subit cet index quand Zaza enfle suite à update et que la ligne L1 qui héberge la vachette ne tient plus dans la feuille P1 contenant L1 ? L1 va-t-elle migrer dans une page P2 (qui à son tour pourrait bien sûr devenir trop petite) ? Qu’en est-il de la stabilité du nœud situé au niveau H-1 (celui qui adresse les feuilles) ?
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  15. #15
    Expert éminent
    Avatar de StringBuilder
    Homme Profil pro
    Chef de projets
    Inscrit en
    Février 2010
    Messages
    4 144
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Rhône (Rhône Alpes)

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

    Informations forums :
    Inscription : Février 2010
    Messages : 4 144
    Points : 7 388
    Points
    7 388
    Billets dans le blog
    1
    Par défaut
    Y'a un truc que je pige pas bien.

    Dans le second poste, elsuket dit de créer :

    Si les valeurs de la colonne sont unique
    => Un index cluter

    Si les valeurs de la colonne sont non unique
    => Un index non-cluster

    Je ne vois pas très bien ce que vient faire la notion de "cluster" dans notre affaire.

    Par défaut, la clé primaire est associée à un index cluster (et unique).

    En revanche, autant il est fort possible (j'ai pas vérifier) qu'un index cluster soit forcément unique, autant je ne vois pas pourquoi il faudrait impérativement faire un index cluster quand on a une unicité.

    Pour moi, dans les deux cas :
    - On crée un INDEX "tout court"
    - Pour le cas de la colonne unique, on ajoute une contrainte UNIQUE

    Ou alors :
    - On crée un UNIQUE INDEX sur la colonne unique
    - Et un INDEX "tout court" sur la non-unique

    Une table pouvant avoir plusieurs colonnes uniques (clé primaire, et numéro de facture par exemple) et qu'il est impossible d'avoir plusieurs index cluster dans une même table, je ne pense pas qu'il faille mettre dans la tête des gens que "UNIQUE" = "CLUSTER", ça n'a rien à voir selon moi.
    On ne jouit bien que de ce qu’on partage.

  16. #16
    Membre expert
    Avatar de alassanediakite
    Homme Profil pro
    Développeur informatique
    Inscrit en
    Août 2006
    Messages
    1 599
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : Mali

    Informations professionnelles :
    Activité : Développeur informatique

    Informations forums :
    Inscription : Août 2006
    Messages : 1 599
    Points : 3 590
    Points
    3 590
    Billets dans le blog
    8
    Par défaut
    Salut
    C'est toujours intéressante de lire les experts quand ils livrent leurs secrets.
    Je voudrais juste ajouter les liens IN FRENCH de François (avec votre accord svp): ici, et
    Ne vous arrêtez surtout pas ici.
    Le monde est trop bien programmé pour être l’œuvre du hasard…
    Mon produit pour la gestion d'école: www.logicoles.com

  17. #17
    Membre expert Avatar de iberserk
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    Novembre 2004
    Messages
    1 795
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Gironde (Aquitaine)

    Informations professionnelles :
    Activité : Architecte de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2004
    Messages : 1 795
    Points : 3 173
    Points
    3 173
    Par défaut
    En revanche, autant il est fort possible (j'ai pas vérifier) qu'un index cluster soit forcément unique
    Non justement un CLUSTER n'a rien à voir avec l'unicité.


    Une table pouvant avoir plusieurs colonnes uniques (clé primaire, et numéro de facture par exemple) et qu'il est impossible d'avoir plusieurs index cluster dans une même table, je ne pense pas qu'il faille mettre dans la tête des gens que "UNIQUE" = "CLUSTER", ça n'a rien à voir selon moi.
    Vous avez raison la formulation est maladroite et laisse a penser que l'unique possibilité d'assurer l'unicité passe par la pose d'un index CLUSTER... ce n'est assuremment pas ce que voulait dire Elsuket...
    Je pense qu'il prenait l'exemple de la PK demandé par le posteur initial...
    Prendre conscience, c'est transformer le voile qui recouvre la lumière en miroir.
    MCTS Database Development
    MCTS Database Administration

  18. #18
    Expert éminent sénior
    Avatar de mikedavem
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2005
    Messages
    5 450
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Distribution

    Informations forums :
    Inscription : Août 2005
    Messages : 5 450
    Points : 12 891
    Points
    12 891
    Par défaut
    Je l'entends ainsi, mais reprenons l’exemple de la vachette Zaza, en relation cette fois-ci avec l’index primaire (de hauteur H). Quelle avanie subit cet index quand Zaza enfle suite à update et que la ligne L1 qui héberge la vachette ne tient plus dans la feuille P1 contenant L1 ? L1 va-t-elle migrer dans une page P2 (qui à son tour pourrait bien sûr devenir trop petite) ? Qu’en est-il de la stabilité du nœud situé au niveau H-1 (celui qui adresse les feuilles) ?
    Si une ligne de données mise à jour ne tient plus dans sa page d'origine, elle sera effectivement déplacée dans une autre page. Maintenant si on ne touche pas à la clé cluster le row locator associé ne bougera pas. Cela permet de ne pas toucher aux index non cluster qui en dépendent comme l'a précisé SQLPro. Au final, si beaucoup de mises à jour sont effectués sur la table il est probable que l'index cluster associé se fragmente plus ou moins rapidement au niveau H-1.

    @StringBuilder > Effectivement le choix de l'index cluster doit être réfléchi bien que la plupart du temps celui-ci concerne la clé primaire et est unique. Mais il arrive que ce soit pas forcément le choix adéquate.

  19. #19
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 945
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 945
    Points : 30 716
    Points
    30 716
    Billets dans le blog
    16
    Par défaut
    Bonsoir,


    J’ai oublié de compléter par un petit dessin le coup de la différence (selon moi) du nombre d’I/O en fonction du SGBD.

    Si j’ai bien compris, et pour résumer : physiquement parlant, avec SQL Server, les données d’une table sont intégrées à l’index cluster, dont elles constituent le niveau feuilles. A cette occasion et par comparaison, je rappelle qu’avec DB2, la table est hébergée dans un table space à part. Reprenons le cas de la recherche d’une vachette. Si l’on effectue un accès selon la séquence cluster, tout le monde a bon, en quatre entrées/sorties (symbolisées par des flèches rouges) on récupère les données de Zaza parmi soixante-sept millions de bovins :


    So far, so good. Si l’on effectue maintenant une recherche qui ne met pas directement en jeu l’index cluster, la stratégie n’est plus la même, cf. figure B ci-dessous. Dans tous les cas, le SGBD utilise les services de l’index non cluster qui va bien (s’il existe). Dans un cas, les feuilles de cet index adressent la racine de l’index cluster et dans l’autre cas directement le table space hébergeant la table, d’où un nombre d’entrées/sorties (ou de cailloux pour le Petit Poucet) égal à 8 dans un cas et à 5 dans l’autre cas (avec en l’occurrence un gain en I/O time plus que substantiel, conséquence du court-circuit de l’index cluster).


    Est-ce bien cela ? Si ce n’est pas le cas, je referai les dessins.
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  20. #20
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Points : 12 371
    Points
    12 371
    Par défaut
    Bonjour,

    physiquement parlant, avec SQL Server, les données d’une table sont intégrées à l’index cluster, dont elles constituent le niveau feuilles
    C'est tout à fait exact.

    Dans un cas, les feuilles de cet index adressent la racine de l’index cluster et dans l’autre cas directement le table space hébergeant la table, d’où un nombre d’entrées/sorties (ou de cailloux pour le Petit Poucet) égal à 8 dans un cas et à 5 dans l’autre cas
    Oui, mais seulement si l'index non cluster contient toutes les colonnes qui participent à la requête.
    Si ce n'est pas le cas et que l'index non-cluster contient seulement les colonnes qui participent à la jointure et/ou aux filtres, un key lookup prend effet : on va chercher les lignes qualifiées par l'index non-cluster dans l'index cluster pour en extraire les valeurs des colonnes qui "manquent".
    Suivant le nombre de lignes qualifiées, le key-lookup peut coûter plus ou moins cher.
    Si donc l'index non-cluster est sur une table contenant un nombre conséquent de lignes (ce que je trouve être le cas avec 67 millions), et qu'il n'existe qu'une ou peu de vaches dont le petit nom est Zaza, la requête peut souffrir le key lookup, et on peut conserver un index dont le nombre de colonnes est faible, ce qui est le but ultime.
    Si ce n'est pas le cas, c'est le moteur de stockage qui souffrira le martyre .
    Mais depuis SQL Server 2005, on peut y répondre en ajoutant des colonnes dites incluses à l'index non-cluster : elles se trouvent seulement au niveau feuille de celui-ci, et évitent le key-lookup par couverture de la requête.

    Ton schéma est donc correct

    @++

Discussions similaires

  1. [AC-2007] Conversion date heure Access au format BigInt SQL Server 2008
    Par PapouDomi dans le forum Access
    Réponses: 2
    Dernier message: 24/06/2015, 13h26
  2. Migration Access vers SQL-Server 2008
    Par SALIA LOUA OLIVIER dans le forum MS SQL Server
    Réponses: 1
    Dernier message: 20/03/2012, 15h42
  3. Transfert Access SQL Server 2008
    Par phil5841 dans le forum MS SQL Server
    Réponses: 1
    Dernier message: 01/06/2011, 19h57
  4. sql server 2008 express import de table MS ACCESS
    Par mapmip dans le forum MS SQL Server
    Réponses: 1
    Dernier message: 26/02/2010, 14h30
  5. Réponses: 0
    Dernier message: 27/10/2009, 13h24

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