Discussion :

[hbfocus]

Bonjour,

J’ai 12 tables sur ma base sur lesquelles je voudrai utiliser le type DECIMAL(16.10) comme clés primaires, ce type me permet d’utiliser la fonction OADate de .Net ce qui m’évite bien sûr les doublons mais même temps de me débarrasser de la colonne de la date et heure de création de la ligne.

Je voudrai avoir votre avis sur le cout en performances par rapport à un INT ou BIGINT surtout que c’est des tables de 150 000 lignes et 12 colonnes en moyenne chacune.

Merci.

[fsmrel]

Bonjour hbfocus,


Les valeurs prises par une clé primaire doivent être figées, sans signification (pour éviter de gros ennuis, un attribut entrant dans la composition d’une primaire doit être artificiel, voyez la règle d’or de Tabourier dans le billet auquel je vous renvoie). En revanche une clé alternative (astreinte à garantir l’unicité, au même titre qu’une clé primaire) est bâtie sur les attributs « naturels », c'est-à-dire ayant une signification pour l’utilisateur.

Autrement dit, pour votre table définissez un nouvel attribut de type INT qui servira pour la clé primaire, et utilisez le type DECIMAL(16.10) pour l’attribut qui servira pour la clé alternative.

Ce système fonctionne très bien avec des tables de cent millions de lignes : la performance de votre base de données ne sera pas dégradée...

[escartefigue]

Stable et sans signification mais aussi, de préférence, concise.

[SQLpro]

Stable et sans signification mais aussi, de préférence, concise.

Ce qui est loin d'être le cas d'un DECIMAL (16, 10) qui utilise 9 octets et donc nécessite 3 passes dans un processeur 32 bits pour être lu alors qu'un INT n'en nécessite qu'une seule... Donc 3 fois moins rapide pour les lectures et par conséquent 6 fois moins rapide pour toute jointure !!!

A +

[escartefigue]

Nous sommes d'accord, d'où mon rappel sur cette règle, non obligatoire, mais à appliquer dès que possible

[hbfocus]

Bonjour,

Merci pour votre aide, je me suis employé à essayer de comprendre certaines locutions car au fait je suis autodidacte. Pendant l’âge de pierre j’ai développé des petites applis sous clipper et le bon vieux dBaseIII+ avec quelques centaines de mouvements par jour, les clients avaient l’air satisfaits vu que je n’avais presque jamais eu à intervenir par la suite même pas pour la maintenance de la base (ce qui est ma satisfaction à moi). Depuis quelques années je me suis mis à .Net et Access ou SQL Server selon le cas.

A chaque fois et avant de déployer mon appli je génère un jeux d’essai assez volumineux pour faire des tests en situation extrême.
Ma base actuelle devra contenir au max. un million de lignes au bout de 20 ans d’utilisation.

Voici le résultat d’un petit test de chargement des mouvement courants 5000 lignes en moyennes, avec des clé primaire varchar(8 à 36) que je vais bien sûr modifier. Le résultat donne 1.34 secondes que je trouve très lourd, j’espère pouvoir le diviser par 3 grâce à vos conseils.

Merci encore une fois.

[hbfocus]

Actes - 502 - 0.1092002
ActesParties - 1004 - 0.1872003
FeesAndTaxes - 3012 - 0.7488013
Obligations - 109 - 0.0156001
ObligationsDetail - 274 0.0156
PermanentParties - 6 - 0.1404002
PartiesAffixes - 9 - 0.1248003
1.3416024

[escartefigue]

A chaque fois et avant de déployer mon appli je génère un jeux d’essai assez volumineux pour faire des tests en situation extrême.
Ma base actuelle devra contenir au max. un million de lignes au bout de 20 ans d’utilisation.

1 millions de lignes pour toute une base de données, on est très très loin d'une situation extrème

Voici le résultat d’un petit test de chargement des mouvement courants 5000 lignes en moyennes, avec des clé primaire varchar(8 à 36) que je vais bien sûr modifier.
Le résultat donne 1.34 secondes que je trouve très lourd, j’espère pouvoir le diviser par 3 grâce à vos conseils.

Le chargement est une opération qu'on ne réalise qu'une fois, ce n'est donc pas sur cette opération que les performances sont les plus critiques (sauf dans certains cas comme les chantiers de migration ou de convergence, mais ce n'est pas le sujet ici)

Le plus important est de mesurer les temps de traitement sur les opérations de gestion courante avec accès concurrents : consultations et mises à jour TP, travaux batch et notamment ceux de fin d'année qui gèrent des volumes importants. Vérifier aussi le temps d'exécution des servitudes (sauvegardes, restaurations, réorganisations, reconstructions d'index....)

De plus, il faut bien distinguer le temps CPU du temps elapsed et préciser si les mesures sont faites en heures pleines avec des travaux concurrents qui prennent des ressources, ou au contraire si l'on a fait des tests en conditions optimales, seul ou à peu près sur la machine.

Enfin, le fait de changer le format de vos colonnes clef pour passer de decimal à integer sera certes bénéfique aux performances, mais ce ne sera que marginal par rapport à la performance de l'ensemble

[hbfocus]

Situation extrème par rapport à l'utilisation de ma base petite base , j'ai généré un jeu d'un million de lignes qu'elle risque d'encaisser au bout de 20 ans d'utilisation, quoique ça ne sera pas le cas. Mais aussi une situation extrème aussi pour une base sous Access 2000. C'est ce que je pense du moins.

[hbfocus]

]Enfin, le fait de changer le format de vos colonnes clef pour passer de decimal à integer sera certes bénéfique aux performances, mais ce ne sera que marginal par rapport à la performance de l'ensemble

Au fait je vais passer de clés VARCHAR à INT mais je vais quand-même tester avec DECIMAL(15.9) qui fait apparemment 5 octets si ce n'est pas beaucoup plus qu'INT ça me servira en même temps pour enregistrer la date et heure de création de la ligne et ça me fera gagner une colonne sur chacune de mes tables mouvements.

[fsmrel]

Bonjour hbfocus,


Vous n'avez pas prêté attention à la règle d'or Yves Tabourier. Pour avoir gagné une colonne dans les tables, des entreprises ont dû mettre à la poubelle leurs bases données et repartir à zéro (à quel prix !)

Réfléchissez-y....

[escartefigue]

Au fait je vais passer de clés VARCHAR à INT mais je vais quand-même tester avec DECIMAL(15.9) qui fait apparemment 5 octets si ce n'est pas beaucoup plus qu'INT ça me servira en même temps pour enregistrer la date et heure de création de la ligne et ça me fera gagner une colonne sur chacune de mes tables mouvements.

JAMAIS

-1- Des dates et heures doivent impérativement être stockées dans des colonnes de format date et heure ! sinon c'est la catastrophe à tout point de vue
comment pensez vous controler des périodes contigues, trier par date etc... avec des formats inadaptés

-2- Et surtout, comme mentionné plus haut, une clef doit être non sémantique

-3- Et enfin, j'allais oublier, un horodatage ne garantit nullement l'unicité, or une clef primaire doit être unique

[hbfocus]

Bonjour,

Encore une fois merci pour vos conseils, j’ai pu gagner quelques précieuses secondes sur un traitement de 1 500 000 lignes.
Mais un problème se pose avec la clé increment au cas où j’aurai à fusionner 2 tables mouvements traitées au départ séparément.

[escartefigue]

Bonjour,

Encore une fois merci pour vos conseils, j’ai pu gagner quelques précieuses secondes sur un traitement de 1 500 000 lignes.
Mais un problème se pose avec la clé increment au cas où j’aurai à fusionner 2 tables mouvements traitées au départ séparément.

Non car les colonnes autoi-ncrémentées, en fonction des paramètres de DDL, calculent la valeur si elle n'est pas fournie
En utilisant ce paramétrage, vous ne serez pas confrontés à des clefs en double

De plus vous pouvez définir la valeur de début d'incrément et le pas d'incrément
Réservez vous des plages différentes le cas échéant

Selon les SGBD, les possibilités de paramétrage des identity column est plus ou moins riche

Edit : je profite de ce post pour rappeler qu'il ne faut jamais utiliser une identity column pour déterminer l'ordre d'arrivée des données :
- en cas de delete, les trous peuvent être réutilisés (fonction du paramétrage)
- les identifiants (en fonction aussi du paramétrage et du SGBD) peuvent être réservés par paquet, mais rien ne garantit que dans ce paquet, le n° le + petit sera commité en 1er
Pour connaitre l'historique des insertion, il faut donc utiliser un horodatage

[hbfocus]

Le problème se trouve au niveau des clef étrangères qui on été renseignées par chaque table à part, dans ce cas deux tables différentes peuvent créer une même valeur au niveau de la clé étrangère.

[hbfocus]

Dans ce cas n'est-il pas plus raisonnable d'opter pour une Replication ID.

[hbfocus]

Je suis passé de 1.34s à 0.3276006s encore une fois merci à vous tous pour vos conseils

[StringBuilder]

Juste mon grain de sel quand à une clé alternative (pour ne pas dire un index unique et/ou une contrainte d'unicité) sur un timestamp géré hors SGBD, à mon sens, c'est pas une bonne idée du tout.

En effet, que se passe-t-il si :
1/ On doit créer des lignes directement dans la base, sans passer par le programme gérant les valeurs de cette colonne ?
2/ Si on crée des lignes à partir d'une insertion de masse (donc toutes avec le même timestamp) ?

Bref, pour moi c'est une donnée comme une autre, éventuellement indexée pour de bonnes performances en recherche, mais à mon avis, absolument pas une clé unique.

[escartefigue]

Techniquement un timestamp ne garantit pas l'unicité : avec une CPU très performante et/ou peu sollicitée, le cas de double reste possible même s'il est marginal

[StringBuilder]

Ça dépends de l'implémentation du timestamp : si c'est un horodatage "horaire", alors non, il peut y avoir des doublons. Si c'est une implémentation comme elle (au moins fût, je ne sais pas ce que ça donne maintenant) sur SQL Server, c'est un horodatage par incrément, donc chaque valeur est bien unique, même si plusieurs écritures ont lieu dans le même tick.

-- Edit : depuis 2008, ça a été renommé de façon plus logique en "rowversion" d'ailleurs.
https://msdn.microsoft.com/fr-fr/lib...=sql.120).aspx

Ceci dit, en cas de mise à jour de la ligne, le timestamp évolue, ce qui en soit est plutôt mauvais pour une gestion de clé primaire (puisqu'il faut faire péter en cascade les clé étrangères, qui sont peut-être elles aussi horodatée, et ainsi de suite )