Discussion :

[François DORIN]

Ok, mais alors il vaut mieux stocker un hash une fois et l'utiliser pour la comparaison, plutôt que d'utilisé la fonction hash sur le champs a chaque requête.

Oui !

Le guid(uniqueidentifier) est alors le champs natif le plus proche d'un hash md5, (128bit pour les 2). aucune perte, ni aucun octet en plus.

BINARY(16) est tout aussi proche qu'un guid. Maintenant, un hash ce n'est pas la même chose qu'un MD5. MD5 est juste un exemple de hash. Il en existe beaucoup d'autres, avec des longueurs variables. Sur le premier message, je réagissais sur l'inutilité de calculer un hash puis de le convertir en GUID.

[aserf]

Ce ne sont pas des fonctions plutot ? je parle d'un type de stockage comme VarChar, Int, UniqueIdentifier, ...

Non.. pas un ! Plusieurs !!!!!

CHECKSUM, CHECKSUM_AGG, BINARY_CHECKSUM, HASHBYTES !!!

A +

[aserf]

Merci je sais qu'il existe de nombreuse méthode de hash je suis également développer.
mais le faite de prendre un uniqueidentifier a la place d'un binaire est quand même plus souple quand on code (Type Guid en C#).
de plus je pense que l'uniqueidentifier étant un type natif de sql server je pense qu'il a été un minimum optimisé par microsoft.
et est ce qu'il est possible de faire une jointure entre 2 champs varbinary ? je ne sais pas, mais avec un guid c'est possible.

Apres j'avais fait des test sur 30 millions de lignes et c'est la solution qui était la plus performante , entre les chaine, les binaires, ... (intersection de 20 000 millions de ligne avec 10 Millions de lignes), je parle donc de vécu.

Oui !


BINARY(16) est tout aussi proche qu'un guid. Maintenant, un hash ce n'est pas la même chose qu'un MD5. MD5 est juste un exemple de hash. Il en existe beaucoup d'autres, avec des longueurs variables. Sur le premier message, je réagissais sur l'inutilité de calculer un hash puis de le convertir en GUID.

[François DORIN]

mais le faite de prendre un uniqueidentifier a la place d'un binaire est quand même plus souple quand on code (Type Guid en C#).

La question ici est d'optimiser une requête, pas le confort du développeur.

de plus je pense que l'uniqueidentifier étant un type natif de sql server je pense qu'il a été un minimum optimisé par microsoft.

Je vous invite à faire un essai de clé primaire basé sur un UNIQUEIDENTIFIER. Vous allez avoir des surprises quant aux performances et à la qualité de l'index !

et est ce qu'il est possible de faire une jointure entre 2 champs varbinary ? je ne sais pas, mais avec un guid c'est possible.

Oui, c'est possible.

Apres j'avais fait des test sur 30 millions de lignes et c'est la solution qui était la plus performante , entre les chaine, les binaires, ... (intersection de 20 000 millions de ligne avec 10 Millions de lignes), je parle donc de vécu.

Mais relisez ce que j'ai écrit ! J'ai pas dit que ce n'était pas performant, j'ai dit que c'était inutile.

[aserf]

Pour la qualité de l'index je pense que vous parlez de la fragmentation ? cela peut être évité, il suffit de faire des guid ordonnés quand on utilise le guid en clef primaire, mais dans notre cas précis ce n'est pas possible puisse que c'est un hash a l'origine.

Dans notre cas il doit de toute façon faire un filtre sur un varchar, donc entre faire un filtre sur un varchar (avec des caractères spéciaux) et un filtre sur un guid, je pense que le guid sera plus performant, puisque ce qui sert au départ c'est une chaine pour la recherche.

je ne vois pas comment on peut le remplacer par un Int ... même si le varbinary(16) revient à la même chose en stockage, je ne pense pas que ce soit plus performant.

Donc pour moi si son critère de filtre sur 720 millions de lignes reste sur 2 chaines avec des caractère spéciaux, il pourra accélérer le filtre s'il le passe en guid.

[SQLpro]

Apres j'avais fait des test sur 30 millions de lignes et c'est la solution qui était la plus performante , entre les chaine, les binaires, ... (intersection de 20 000 millions de ligne avec 10 Millions de lignes), je parle donc de vécu.

hé bien vos tests, je pense que vous pouvez vous les foutre ou je pense... car les principaux problèmes du GUID ne sont pas la fragmentation ni la vitesse des jointures (ça c'est accessoire) mais :
1) le hot spot créé par le calcul du GUID car c'est un bout de code de niveau WINDOWS et si plusieurs utilisateurs l'appellent en même temps, ça ralentit fortement...
2) si c'est la clef principale en index clustered cela obère tous les index secondaires non clustered de références obèses...
3) comparer à un identity sur INT ou BIGINT c'est entre 2 et 4 fois plus couteux en mémoire pour les jointures (merci le cache qui en prend un coup...)
y'en as d'autre des pièges à con avec le GUID !!!!

A +

[aserf]

SqlPro merci de relire la discutions
je n'ai pas dit que une jointure avec un guid est plus performance qu'une jointure avec un int, mais une jointure avec un guid est plus performant qu'une jointure avec un varchar(20) ou un VarBinary(16).

donc si vous devez faire une jointure sur un hash je ne voie pas ce que vous pouvez prendre, une bigint c'est 8 octet et un md5 c'est 16 octet, donc impossible de faire tenir le premier dans le deuxième.
Donc si jointure sur un hash de 16 octet quelle est le champs le plus adapter ? car la contrainte est le hash de 16 octet.

Ensuite le temps de génération d'un guid est infime comparer au temps de transfert réseaux de la requête sur le serveur.
donc si vous avez des données en escalier (un xml avec des entêtes et des lignes, un fichiers plats multi niveau) vous pouvez le faire en un seul accès réseaux, alors qu'avec un int il en faudra au moins un par ligne (pour récupérer l'autoincrement), le guid gagnera largement.

Le guid n'est pas une solution miracle, mais dans certain cas ce sera le plus performant, dans d'autre cas non.
il faut prendre le guid en connaissance de cause.

Et je viens de faire un test 1 000 000 guid générer en 139 millisecondes.... donc on est proche du milliard de guid à la seconde, ... je pense que dans une application cela ne ce verra pas, donc je ne vois pas d'ou vous sortez le point 1.

[François DORIN]

je n'ai pas dit que une jointure avec un guid est plus performance qu'une jointure avec un int, mais une jointure avec un guid est plus performant qu'une jointure avec un varchar(20) ou un VarBinary(16).

Pour le VARCHAR(20), oui, pour le BINARY(16) (pas besoin de VAR ici !) non.

donc si vous devez faire une jointure sur un hash MD5 je ne voie pas ce que vous pouvez prendre, une bigint c'est 8 octet et un md5 c'est 16 octet, donc impossible de faire tenir le premier dans le deuxième.
Donc si jointure sur un hash de 16 octet quelle est le champs le plus adapter ? car la contrainte est le hash de 16 octet.

car la contrainte est le hash de 16 octet.

Non, c'est vous qui fixée cette contrainte. Nulle part elle n'a été spécifiée.

donc si vous avez des données en escalier (un xml avec des entêtes et des lignes, un fichiers plats multi niveau) vous pouvez le faire en un seul accès réseaux, alors qu'avec un int il en faudra au moins un par ligne (pour récupérer l'autoincrement), le guid gagnera largement.

Je n'ai rien compris !

Le guid n'est pas une solution miracle, mais dans certain cas ce sera le plus performant, dans d'autre cas non.
il faut prendre le guid en connaissance de cause.

Sauf qu'ici, et c'est ce que je me tue à dire depuis le début, le GUID n'apporte rien. Ce qui apporte, c'est l'utilisation d'un hash.

je ne vois pas comment on peut le remplacer par un Int ... même si le varbinary(16) revient à la même chose en stockage, je ne pense pas que ce soit plus performant.

Quitte à me répéter : parce qu'il n'y a pas que MD5 dans la vie !
Dans le cas qui nous occupe ici, si on modifie la structure de la base, alors autant ne pas passer par un hash, mais faire une table qui contient les numéro des clients. Ensuite, la table "principale" aura deux clés étrangères (numero_client et numero_client_bis) qui pointent sur cette nouvelle table. Cette nouvelle table aura un ID autoincrémenté. Résultat : un INT pour le numéro client, et un autre INT pour le numéro bis.8 octets. Mieux que le 16x2 du GUID, sans risque de collision en plus !

Et dans le cas d'un index non couvrant, le bénéfice serait a priori largement visible puisque les données utiles pour un enregistrement serait de 4+4+7(=DATETIME2) = 15octets, contre les 22(VARCHAR, il faut tenir compte de deux octets supplémentaires pour la taille)+22+7 = 51 octets initiaux. Bref, les index seront donc plus compact, donc moins d'I/O. Dans le cas d'un index couvrant, vu le nombre de colonne, le bénéfice serait très certainement moins intéressant.

[aserf]

Je ne fais pas une fixette sur hash. mais les exemple que vous soulevez sont exactement ceux qui peuvent être repris pour mon explication.
j'ai probablement fait un raccourcis et je vais donc détaille.

Il est claire que si nous pouvons tout modifier il est préférable de rajouter une colonne avec un Int. et de faire une clef étrangère.

Mais s'il parle de 720 000 millions de lignes en 4 mois je suppose qu'il importe énormément de données (environ 6 millions par jour).
dans ce contexte je pense qu'ils ont aplatie la table, car si on avait une une jointure, pour les nouvelle ligne, il devrai pour chaque ligne, vérifier si la ligne de la relation existe (le client) avant d’insérer la ligne de stat.
ensuite si la ligne n'existe pas il est obligé de l'insérer, de récupérer ce nouvelle id, puis de le mettre dans la FK de la ligne de stat.

Cette opération est très lente lors de l'insertion, le faite de faire un hash peu être fait lors de lecture et ensuite insérer en bulk.
Une insertion en bulk de quelle que millions de lignes prends quelle que minute, la meme chose ligne en ligne pour avoir l'id créé prendre 100 fois plus de temps (si ce n'es pas 1000 dans certain cas).

La ou le guid peu être performant c'est que vous pouvez très bien pour les nouveau clients les créé avant l'insertion en bulk. donc vous pouvez mettre les id qui seront utilisé dans la relation avant l'insertion et en une fois.
donc vous garder le bulk et la relation.

je suppose que c'est sa car je fait le même type de traitement que lui, tout les mois je doit insérer des 100 de millions de ligne, avec des relation entre elle, nous avons pu divisier par 100 les imports de ces fichiers grâce a ces techniques (passer de l'autoincrement au guid et utilisé le bulk sur plusieurs niveau).

j’espère avoir été plus clair.

[François DORIN]

Mais s'il parle de 720 000 millions de lignes en 4 mois je suppose qu'il importe énormément de données (environ 6 millions par jour).
dans ce contexte je pense qu'ils ont aplatie la table, car si on avait une une jointure, pour les nouvelle ligne, il devrai pour chaque ligne, vérifier si la ligne de la relation existe (le client) avant d’insérer la ligne de stat.
ensuite si la ligne n'existe pas il est obligé de l'insérer, de récupérer ce nouvelle id, puis de le mettre dans la FK de la ligne de stat.

Sans plus d'information sur les données, difficile de se prononcer. Mais à moins qu'il y a 1 million de nouveaux clients par jour, je ne suis pas certains que cela représente un problème. Il s'agit de numéro de clients, donc je pense (mais à vérifier) qu'il n'y aura que peu d'insertions.

Cette opération est très lente lors de l'insertion, le faite de faire un hash peu être fait lors de lecture et ensuite insérer en bulk.
Une insertion en bulk de quelle que millions de lignes prends quelle que minute, la meme chose ligne en ligne pour avoir l'id créé prendre 100 fois plus de temps (si ce n'es pas 1000 dans certain cas).

Attention, il y a du mélange. D'un côté tu parles de hash et de bulk insert, de l'autre d'ID et d'insertion ligne par ligne. Les deux sont orthogonaux ! Tu peux très bien faire de l'insertion de hash en ligne par ligne et de l'insertion d'ID en bulk.

Et oui, l'insertion bulk est plus performante pour l'ajout d'une quantité importante de données (c'est même fait pour ça !).

La ou le guid peu être performant c'est que vous pouvez très bien pour les nouveau clients les créé avant l'insertion en bulk. donc vous pouvez mettre les id qui seront utilisé dans la relation avant l'insertion et en une fois.
donc vous garder le bulk et la relation.

Là encore, les concepts sont orthogonaux ! Remplace GUID par ID et ça marche toujours.

[aserf]

Justement non, vous ne pouvez pas remplacé Guid par int dans tout les cas.

Exemple un fichier xml :

<Facture>
<Ligne/>
<Ligne/>
<Ligne/>
</Facture>
<Facture>
<Ligne/>
<Ligne/>
</Facture>

vous avez la première facture avec 3 lignes et la 2ieme avec 2 lignes.
vous ne pouvez pas faire de bulk avec un int, car il n'y as pas de relation entre la facture et la ligne, on c'est que ce sont les lignes de la facture car elle ce trouve dans la sous balises.
donc vous êtes obligé d’insérer d'abord la première facture récupérer l'id, puis ensuite vous pouvez insérer les ligne de la facture en bulk.

avec un guid, vous mettez le guid sur la facture vous bouclez ensuite sur les lignes et vous pouvez mettre le guid dedans, puis pareil pour toutes les factures et les lignes.
ensuite vous faite un bulk de facture puis de ligne.

si on insère 1 000 facture, j'ai 2 bulk avec un guid comme pk, et 1000 insert et 1000 bulk avec un autoincrement.

Est ce que c'est plsu clair ?

[François DORIN]

Justement non, vous ne pouvez pas remplacé Guid par int dans tout les cas.

Exemple un fichier xml :

<Facture>
<Ligne/>
<Ligne/>
<Ligne/>
</Facture>
<Facture>
<Ligne/>
<Ligne/>
</Facture>

vous avez la première facture avec 3 lignes et la 2ieme avec 2 lignes.
vous ne pouvez pas faire de bulk avec un int, car il n'y as pas de relation entre la facture et la ligne, on c'est que ce sont les lignes de la facture car elle ce trouve dans la sous balises.
donc vous êtes obligé d’insérer d'abord la première facture récupérer l'id, puis ensuite vous pouvez insérer les ligne de la facture en bulk.

Et pourtant, on peut faire une insertion par lot. Certes, pas avec BULK INSERT. Mais SQL Server permet la manipulation de XML. Et pour faire cette insertion, je n'ai besoin que de... 2 INSERT !
J'utilise actuellement cette méthode dans un projet (transfert d'un catalague d'un AS400 vers un SQL Server), et ça marche tellement bien que l'import prend 10x moins de temps que l'export ! Et il ne s'agit pas que d'un simple transfert des données, il y a des traitements derrières...

Pas besoin de GUID. Surtout qu'il y a un aspect que vous passez sous silence, c'est le risque de collision. Même si les risques sont très faibles, plus la volumétrie est importante, plus les risques sont grands.

[SQLpro]

Et je viens de faire un test 1 000 000 guid générer en 139 millisecondes.... donc on est proche du milliard de guid à la seconde, ... je pense que dans une application cela ne ce verra pas, donc je ne vois pas d'ou vous sortez le point 1.

Encore une fois, vous n'avez pas fait ce test en concurrence , par exemple en ayant 10 postes qui font aussi des insertions de GUID dans une table.

Il est extrêmement rare d'avoir des bases de données mono utilisateur !!!!!

A +

[aserf]

Bonjour,

Je suis curieux que vous m'expliquer votre technique,je connaissait bcp avec un fichier xml, mais à ma connaissance nous ne pouvez pas spécifier les clef enfants.
Ensuite vous expliquer que pour les 2 factures vous faite 2 insert. donc le jour ou il y as 1 000 000 de facture vous faite 1 000 000 d'insert et 100 000 000 donne alors 100 000 000 d'insert?
dans ce cas ma méthode restera largement plus performant.
Le risque de collision sur un guid est inexistant (2^128) vous avez plus de chance de gagner au loto tout les jours que d'avoir un collision de guid.
(et ne me cité pas les collisions de hashmd5 ... cela n'a rien a voir le md5 n'est pas construit avec un horodatage,mac adresse, ...) le seul moyen d'augmenter e doublon sur un guid est de remonter le temps de l'ordinateur qui généré, et même la il faudrait les générer en boucle le plus rapidement possible dans le même intervalle de temps.

SqlPro.

Bien sur dans mon test c’était vite fait sur ma machine. mais je tourne sur un serveur Sql ou j'ai entre 10 et 20 ordonnanceur qui intégré des flux en même temps, le gain avec le guid en bulk sur plusieur niveau surclasse largement la perte de temps.

J'insert des milliards d'enregistrement tout les ans dans nos bases de donnés (certaine table font a elle seul 100go), nous avons essayer toute les techniques que vous citez. la plus rapide reste celle que je vous cite.
et malgré toutes ces insertions je n'es jamais eu une collision...

[escartefigue]

Il est extrêmement rare d'avoir des bases de données mono utilisateur

Une base multi-utilisateur peut devenir ponctuellement mono-utilisateur dans certains contextes particuliers comme le cas d'une migration en masse par exemple
Interdire les accès concurrents pendant ce type d'opération permet d'accélérer les traitements et de faciliter la reprise si nécessaire.
Peut être est-ce le cas ici ?

[aserf]

Je précise aussi que si vous avez des technique que je ne connais pas je suis près à les tester et vous faire un retour.

j'avais a l’époque développer une application qui permettais de faire un bench avec toute les options possible.
j’intègre la votre et je vous dit les comparatifs de temps pour 1000, 100 00, 1 000 000 de ligne.

[François DORIN]

Ensuite vous expliquer que pour les 2 factures vous faite 2 insert. donc le jour ou il y as 1 000 000 de facture vous faite 1 000 000 d'insert et 100 000 000 donne alors 100 000 000 d'insert?
dans ce cas ma méthode restera largement plus performant.

Non, on s'est mal compris. Je fais un premier INSERT pour toutes les factures, et un second pour toutes les lignes. Ainsi, je n'ai que 2 INSERT pour toutes les données.

Le risque de collision sur un guid est inexistant (2^128) vous avez plus de chance de gagner au loto tout les jours que d'avoir un collision de guid.

Il n'est pas inexistant. Il est très faible certes, mais il est présent malgré tout. Surtout qu'il dépend de l'algo utilisé pour la génération. Certains sont véritablement merdiques de ce point de vue.

(et ne me cité pas les collisions de hashmd5 ... cela n'a rien a voir le md5 n'est pas construit avec un horodatage,mac adresse, ...) le seul moyen d'augmenter e doublon sur un guid est de remonter le temps de l'ordinateur qui généré, et même la il faudrait les générer en boucle le plus rapidement possible dans le même intervalle de temps.

Je vois que vous avez une profonde méconnaissance du GUID. Ce n'est pas parce qu'il y a une implémentation qui utilise l'adresse mac que toutes l'utilise (pour la petite histoire, microsoft a arrêté de l'utiliser d'ailleurs pour des problèmes de vie privée).

Ensuite, je ne vais pas le comparer à MD5 car vous comparez, une fois encore, ce qui est incomparable ! Un GUID, c'est quelque chose qui sort de nul part. Un MD5, c'est un hash réalisé à partir d'une entrée. Et chose rigolote, un MD5, et un GUID étant de taille identique, il y a en théorie autant de risque de collision avec l'un qu'avec l'autre.

[aserf]

SVP arrêter de supposer ce que je sais et ne sais pas, restons sur les faits. (cela fait plusieurs fois que vous le faite, je ne vous est pas repris par correction)

je sais qu'il existe plusieurs techniques pour générer les guid de Microsoft, et lire vite fait la doc sur wikipedia le montre.
https://fr.wikipedia.org/wiki/Global...que_Identifier

et je n'es jamais dit que j'utilisais le guid de Microsoft ... puisque je vous avais dit pour des questions de fragmentation de la base, que je construisait un guid ordonnée ...
une article pour l'ordre des guid

http://sqlblog.com/blogs/alberto_fer...ql-server.aspx

Donc expliquer moi plus en détail votre technique, car pour insérer les lignes enfant, vous devez alors refaire un select sur les lignes parent pour récupérer les ID ?
Je suis très curieux, mon bute n'es pas de prouver que j'ai raison, mais de trouver la méthode la plus performante.

Vous utilisez une datatable ? entity ? ...

Détaillé la technique utilisé, la méthode, et ensuite le temps pour une certaine volumétrie.

si j'ai 1 000 000 de ligne sur plusieurs niveau (facture, ligne facture, ...) donc pas forcement 2, je les insert en 80s environ (peut importe le nombre de niveau).
si on est sur des accès simultané (20 import en parallèle sur 20 ordonnanceurs) je ne perd pas trop de temps (sa n'es pas ,non plus multiplier).

[François DORIN]

SVP arrêter de supposer ce que je sais et ne sais pas, restons sur les faits. (cela fait plusieurs fois que vous le faite, je ne vous est pas repris par correction)

Justement, je reste dans les faits et je constate que vous maîtrisez mal le sujet, notamment en mélangeant et comparant des concepts différents.

Donc expliquer moi plus en détail votre technique, car pour insérer les lignes enfant, vous devez alors refaire un select sur les lignes parent pour récupérer les ID ?
Je suis très curieux, mon bute n'es pas de prouver que j'ai raison, mais de trouver la méthode la plus performante.

Je l'ai déjà fait. Maintenant, je fourni un exemple

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
USE master;
GO
CREATE DATABASE TestImportXML;
GO
USE TestImportXML;
GO
CREATE TABLE facture (
	id INT IDENTITY,
	numero VARCHAR(20),
	CONSTRAINT PK_facture PRIMARY KEY(id),
	CONSTRAINT UK_facture UNIQUE(numero)
);
 
CREATE TABLE ligne (
	id INT IDENTITY,
	facture_ref INT NOT NULL,
	designation VARCHAR(20) NOT NULL,
	montant DECIMAL(18,2) NOT NULL,
	CONSTRAINT PK_ligne PRIMARY KEY(id),
	CONSTRAINT FK_ligne_facture_ref FOREIGN KEY(facture_ref) REFERENCES facture(id)
);
GO
DECLARE @Xml XML = '<factures>
<facture numero="456">
	<ligne designation="truc" montant="45.21" />
	<ligne designation="truc" montant="50.00" />
</facture>
<facture numero="789">
	<ligne designation="machin" montant="1.23" />
</facture>
</factures>';
 
INSERT INTO facture(numero)
SELECT XmlTable.x.value('@numero', 'VARCHAR(20)') FROM @Xml.nodes('/factures/facture') AS XmlTable(x);
 
INSERT INTO ligne(facture_ref, designation, montant)
SELECT F.id, XmlLigne.x.value('@designation', 'VARCHAR(20)'), XmlLigne.x.value('@montant', 'DECIMAL(18,2)')
FROM @Xml.nodes('/factures/facture') AS XmlFacture(x)
CROSS APPLY XmlFacture.x.nodes('ligne') AS XmlLigne(x)  
INNER JOIN facture AS F ON F.numero = XmlFacture.x.value('@numero', 'VARCHAR(20)')

2 INSERT pour insérer toutes les factures et toutes les lignes. Le XML dans l'exemple est simple et ne contient que peu d'éléments, mais il peut en contenir beaucoup plus sans aucun soucis (je fais quelque chose de similaire sur un projet actuellement avec un XML de 15Mo qui est traité en 45s, sachant que les données sont transformées avant insertion en BD), et que ma problématique c'est l'optimisation des recherches et non l'optimisation de l'insertion (qui se fait de nuit quand personne ne travaille :-) ).

[SQLpro]

Je précise aussi que si vous avez des technique que je ne connais pas je suis près à les tester et vous faire un retour.

j'avais a l’époque développer une application qui permettais de faire un bench avec toute les options possible.
j’intègre la votre et je vous dit les comparatifs de temps pour 1000, 100 00, 1 000 000 de ligne.

Pour information au sujet de votre bench, ce n'est pas l'elasped time qui est important. Dans différentes config, cet elapsed time variera de beaucoup et est fonction des IHM et des round trips...
Il faudrait faire de nombreux tests répétitifs avec différentes config machine, notamment RAM et parallélisme des CPU et faire une moyenne par catégorie de machine (quand je fais des tests de ce genre, je roule le test 10 fois et n'en retient que les 8 "centraux" pour établir une moyenne)
Il vaudrait mieux se baser sur le temps CPU représentatif de la consommation des ressources les plus critiques....

A +