Discussion :

[Donpi]

Bonjour,

Alors voilà j'ai besoin de calculé un Hash SHA1 pour chaque record de chaque table qui va servir d'identifiant.
Ce hash est calculé sur certaines colonnes des tables pas toujours les mêmes d'une table à l'autre.

En revanche la chaine hashée doit avoir un format très précis pour évité les duplicats.

Du coup il est important que son calcul ne soit qu'a un seul endroit pour être certain qu'il est le même d'un dev à l'autre, régénérable, maintenable, etc...

Bref, quels seraient vos idées / propositions pour ce problème ?

A suivre quelques précisions
- Oui je sais que les hash ont un risque de créer des doublons. Mais tellement infime que ce risque est ignoré.
- J'utilise un Hash plutôt qu'un GUID pour qu'il puisse être connu a l'avance et régénérable.
- J'ai essayer d'utiliser les fonction de Hash de sql serveur, mais comme les paramètres doivent être fixe, c'est compliqué.

Enfin la solution actuellement implémentée qui ne satisfait pas complétement
Nous avons créé une CLR qui prendre un nombre variable de SQLVariant en paramètres.
La CLR fait parfaitement le travail mais pose les problèmes suivants :
- Les [n]Varchar(MAX) ne fonctionnent pas car ne peuvent pas être converti en SQL variant.
- Les CLR ont un nombre de paramètres défini, j'ai du donc créé une SP pour chaque nombre de paramètres.
- Dans les requêtes simples ça va très vite mais dés que c'est un peu compliqué l'Optimiseur pète un câble et une requête de 20 secondes passe à 1h. Un work around à ce dernier point est de stocker les hashs calculés dans une table temporaire puis d'utiliser cette dernière dans la requête complexe.

[François DORIN]

Mes idées en vrac :
- générer un identifiant unique sous forme de chaine de caractère par concaténation des colonnes qui vont bien, par table (par ex : NOM + DATECREATION + COL18) avec un séparateur entre chaque champ
- utiliser une colonne calculée pour déterminer cet identifiant
- calculer le hash à partir de la colonne calculer (là, se sera très simple)
- utiliser une colonne calculée pour stocker le hash
- persister les colonnes calculées
- ajouter un INDEX UNIQUE sur l'identifiant et/ou hash pour s'assurer de l'unicité (ou sur les colonnes de base servant au calcul de l'identifiant)

[SQLpro]

Je ne voit vraiment pas l'intérêt de cette usine à gaz... Que va t'il se passer lors d'un UPDATE ?

A +

[Donpi]

Il n'y a pas d'update dans cette DB.
C'est du pur insert.

[SQLpro]

Bonjour,

Alors voilà j'ai besoin de calculé un Hash SHA1 pour chaque record de chaque table qui va servir d'identifiant.
Ce hash est calculé sur certaines colonnes des tables pas toujours les mêmes d'une table à l'autre.

En revanche la chaine hashée doit avoir un format très précis pour évité les duplicats.

Du coup il est important que son calcul ne soit qu'a un seul endroit pour être certain qu'il est le même d'un dev à l'autre, régénérable, maintenable, etc...

Donc vous aller créer un hotspot qui induira des problèmes de performance...

Bref, quels seraient vos idées / propositions pour ce problème ?

A suivre quelques précisions
- Oui je sais que les hash ont un risque de créer des doublons. Mais tellement infime que ce risque est ignoré.
- J'utilise un Hash plutôt qu'un GUID pour qu'il puisse être connu a l'avance et régénérable.

Un GUID peut lui aussi être généré à l'avance. Dans tous les langage de programmation vous avez une fonction de génération des GUID. Exemple en C# NewGuid(). Vous pouvez aussi appeler la fonction NEWID() de SQL Server dans une requête préalable. Exemple ;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT NEWID() AS GUID;

- J'ai essayer d'utiliser les fonction de Hash de sql serveur, mais comme les paramètres doivent être fixe, c'est compliqué.

Enfin la solution actuellement implémentée qui ne satisfait pas complétement
Nous avons créé une CLR qui prendre un nombre variable de SQLVariant en paramètres.
La CLR fait parfaitement le travail mais pose les problèmes suivants :
- Les [n]Varchar(MAX) ne fonctionnent pas car ne peuvent pas être converti en SQL variant.

C'est totalement faux, il suffit d'utiliser la fonction CAST... qui est faite pour cela !
Mais stocker une valeur binaire dans du texte est une idiotie. Utilisez du binaire. Par exemple BINARY(20)...

- Les CLR ont un nombre de paramètres défini, j'ai du donc créé une SP pour chaque nombre de paramètres.

ça n'a aucun sens de créer une procédure pour cela. Un simple fonction suffit !

- Dans les requêtes simples ça va très vite mais dés que c'est un peu compliqué l'Optimiseur pète un câble et une requête de 20 secondes passe à 1h. Un work around à ce dernier point est de stocker les hashs calculés dans une table temporaire puis d'utiliser cette dernière dans la requête complexe.

Dans tous les cas vous aurez des problèmes de performance. En effet une clé pour être efficace doit être la plus petite possible. Un GUID c'est 16 octets ce qui impose une double lecture dans le processeur 64 bits (=8 octets) donc coute deux fois plus cher qu'un BIGINT... comme clé. De plus, comme les valeurs sont aléatoires, il y aura fragmentation des index. Tout ceci pèsera fortement sur les performances...
Quand au hash c'est pire encore... 20 octets pour le SHA1 ! Donc 3 passe dans le processeur, 6 pour faire une jointure.... Et fragmentation.

Sans parler de la lourdeur de la clé dont le nombre d'octets pèsera sur les sauvegardes, la maintenance...
C'est donc une très mauvaise idée, que l'on rencontre fréquemment chez les développeurs qui ne maîtrisent pas le fonctionnement des SGBDR !

Pourquoi ne pas utiliser un autoincrément à partir d'une séquence ? Il suffit de demander la nouvelle valeur avec :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT NEXT VALUE FOR MaSequence AS NOUVEL_ID;

Puis de l'utiliser dans la requête...

A +

[escartefigue]

Le seul cas où un hash est intéressant, c'est justement si l'on veut disperser les valeurs pour qu'elles ne soient pas contigües.
L'intérêt est de limiter les problèmes de blocages liés aux accès concurrents (les valeurs contigües étant le plus souvent rangées dans la même page en tout cas quand les espaces sont bien organisés, donc un verrou page prend toutes les valeurs contigües).
Dans les autres cas, je rejoins SQLPro : une colonne de type IDENTITY est préférable

[Donpi]

Vous êtes les deux hors sujet.

Vous vous douter bien que si je pars sur des HASH c'est par ce que je n'ai pas le choix.
Maintenant la question et de le faire au mieux en limitant les dégâts.

[SQLpro]

Le seul cas où un hash est intéressant, c'est justement si l'on veut disperser les valeurs pour qu'elles ne soient pas contigües.

Je suis d'accord sur cette première phrase, mais pas sur la suivante !

L'intérêt est de limiter les problèmes de blocages liés aux accès concurrents (les valeurs contigües étant le plus souvent rangées dans la même page en tout cas quand les espaces sont bien organisés, donc un verrou page prend toutes les valeurs contigües).

Parce que un HASH ne se fait pas en cache car il a besoin des données pour être calculé alors que les méthodes d'auto incrément font du cache... Donc d'un point de vue concurrentiel, c'est incommensurablement plus performant

Dans les autres cas, je rejoins SQLPro : une colonne de type IDENTITY est préférable

Enfin pour ta première phrase sur la non contiguïté du hash la seule fois ou j'y ais trouvé un intérêt c'était il y a plus de 20 ans pour mettre en parallèle plusieurs machines SQL Server coopératives dans le cadre du site web FNAC.com afin de faire du scale out

A +

[SQLpro]

Vous êtes les deux hors sujet.

Vous vous douter bien que si je pars sur des HASH c'est par ce que je n'ai pas le choix.
Maintenant la question et de le faire au mieux en limitant les dégâts.

Donc ça ne devrait pas être votre clé primaire !!!!

A +

[Waldar]

Bref, quels seraient vos idées / propositions pour ce problème ?

Vous nous avez décrit ce que vous voulez faire, comment vous essayez de le faire mais pas pourquoi vous voulez le faire.

[escartefigue]

Je suis d'accord sur cette première phrase, mais pas sur la suivante !Parce que un HASH ne se fait pas en cache car il a besoin des données pour être calculé alors que les méthodes d'auto incrément font du cache... Donc d'un point de vue concurrentiel, c'est incommensurablement plus performant

Certes, mais nous ne parlons pas de la même chose, je parle du blocage des pages de données lié aux valeurs contiguës d'identifiants, quand l'identifiant est défini comme critère de clusterisation. Ici on peut arriver à une situation de blocage (time out).

[SQLpro]

Certes, mais nous ne parlons pas de la même chose, je parle du blocage des pages de données lié aux valeurs contiguës d'identifiants, quand l'identifiant est défini comme critère de clusterisation. Ici on peut arriver à une situation de blocage (time out).

Jamais constaté sur du SQL Server depuis plus de 20 ans !

ça a existé... Mais c'était il y a très très longtemps.... Du temps de la RAM à tambour probablement !

A +

[Donpi]

Vous nous avez décrit ce que vous voulez faire, comment vous essayez de le faire mais pas pourquoi vous voulez le faire.

C'est toujours compliqué d'expliquer le pourquoi dans sur un forum. Habituellement les gens lisent une phase sur deux voir aucune quand le post est un peu long.
Et passent leur temps a essayer de trouver un moyen de pas faire ce qui est demandé ou a critiquer l'approche.


En gros j'ai un datawarehouse avec des relations entre les tables qui doit rester "potentiellement" cohérent. ( c'est le potentiellement qui est important )
Un bon 80% des données se trouvent dans la db SQL Server.
Le 20% qui reste peut être un peu n'importe ou.
Fichier Excel, autre base de données en tout genre etc...
Dans ces conditions la PK ou la clé de substitution comme vous voulez l'appeler doit être prédictible sans accéder au datawareHouse d'où le choix des hash qui ont une taille définie.
De plus l'utilisation de pk prédictible me permet de paralléliser là totalité des chargements ce qui me fait gagner un temps considérable lors chargement.

Pour plus de détail cherchez DataVault 2.0 sur internet.

Cordialement

[François DORIN]

C'est toujours compliqué d'expliquer le pourquoi dans sur un forum. Habituellement les gens lisent une phase sur deux voir aucune quand le post est un peu long.
Et passent leur temps a essayer de trouver un moyen de pas faire ce qui est demandé ou a critiquer l'approche.

Ce n'est pas à prendre personnellement. 80% des questions posées ici sur DVP (stat au doigt mouillé par moi) relève d'une mauvaise approche / méconnaissance / autre. Avant d'aborder un sujet complexe, il est donc souvent utile de demander pourquoi avant de dire comment, surtout quand la solution qui en découle est "complexe".

Le "pourquoi" permet de comprendre la situation, et dans bien des cas, simplifier la solution.

[Waldar]

Pour plus de détail cherchez DataVault 2.0 sur internet.

Ok, je connais un peu DV - j'ai fait du DV 1.0 il y a une dizaine d'année, je sais qu'une des nouveautés de la 2.0 est justement le remplacement des clefs subrogées par des clefs hash.
DV 2.0 a été pensé dans le cadre d'Hadoop plus que de nos bonnes vielles bases de données SQL.

Je rajouterai enfin un bémol sur votre architecture DV 2.0 + SQL-Server, qui est vraiment pensé pour une modélisation d'entrepôt de données d'entreprise (Enterprise DWH en anglais dans le texte), et c'est quand même un domaine où SQL-Server ne brille pas du tout.

J'ai un petit doute sur la compatibilité de ces deux propos :
- j'ai besoin de calculer un Hash SHA1 pour chaque record de chaque table qui va servir d'identifiant.
- En revanche la chaine hashée doit avoir un format très précis pour évité les duplicats.
Si c'est demandé par DV 2.0 pas de soucis bien entendu.

Si vous restez purement sur SQL-Server, je n'ai pas de meilleures idées par rapport à celles que vous avez déjà évoquées.