Discussion :

[guigui69]

Bonjour à tous,

Je dispose de deux requêtes sql, qui me retourne un nombre de ligne différent.
Est-il une méthode/option qui permettrai d'avoir uniquement les lignes qui sont différentes ?

Merci d'avance pour votre aide

guigui69

[tatayo]

Bonjour,
Vu de loin dans le brouillard, je dirai que tu peux faire ainsi:

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
 
With req1 as
(
ici le code de la première requête
),
req2 as
(
ici le code de la seconde requête
)
(
select * from req1
except
select * from req2
)
union
(
select * from req2
except
select * from req1
)

Il faut bien sûr remplacer les * par la liste des colonnes .

Tatayo.

[escartefigue]

ou à l'inverse utiliser INTERSECT pour vérifier ce qui est commun selon que le commun ou le différent est plus volumineux.

[StringBuilder]

Bonjour,

J'ai un besoin assez similaire pour effectuer un traitement d'export différentiel de données.

Avec le volume des données que j'ai qui augmente, le traitement que j'ai mis en place devient de plus en plus consommateur et lent, et à tendance à remplir tempdb, le journal des transactions et durer trois plombes...

Je travaille avec les tables (pour simplifier drastiquement) :

Code :

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

1
2
personne (id, nom, prenom) PK(id)
tampon (version, id, nom, prenom) PK(version, id)

Le résultat attendu est :

NEW;lignes avec un ID absent du précédent export
UPD;lignes avec un ID exporté la fois précédente, mais au moins une autre colonne avec des données différentes
DEL; lignes avec un ID exporté précédement, mais absent de la nouvelle itération

J'ai donc le traitement suivant :

Code sql :

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

1
2
3
4
5
6
 
delete tampon where version > 1;
update tampon set version = version +1; // déjà, là ça merde car on modifie la PK, je pense que SQL Server n'est pas trop optimisé pour ça... remplacer la PK par une contrainte unique ?
 
insert into tampon (version, id, nom, prenom)
select 1, id, nom, prenom from personne;

Et la vue :

Code sql :

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

1
2
3
4
5
6
7
8
9
10
11
 
create view diff (statut, id, nom, prenom)
select 'NEW', a1.id, a1.nom, a1.prenom from tampon a1 where a1.version = 1 and not exits (select 1/0 from tampon a2 where a2.version = 2 and a2.id = a1.id)
union all
select 'DEL', a1.id, a1.nom, a1.prenom from tampon a1 where a1.version = 2 and not exits (select 1/0 from tampon a2 where a2.version = 1 and a2.id = a1.id)
union all
select 'UPD', a1.id, a1.nom, a1.prenom 
from tampon a1 
inner join tampon a2 on a2.id = a1.id and a2.version = 2
where a1.version = 1
and (a1.nom <> a2.nom or a1.prenom <> a2.prenom)

Ca marche très bien en soit.

Sauf que là je recopie plusieurs millions de lignes à chaque fois, et que j'ai plusieurs dizaines de colonnes, dont des varchar(max) et en plus je me coltine des colonnes nullables, avec toute la complexité que ça rajoute dans la comparaison...

Bref, la memerde...

La solution à base de except est séduisante, mais elle ne permet pas d'identifier facilement les NEW/UPD/DEL : ça demande à stocker de résultat dans une CTE puis re-comparer avec la table tampon pour déterminer pour chaque ligne si son ID est présent en version 1 et/ou version 2 pour déterminer la nature de la différence... Est-ce que je vais réellement y gagner au final ?

Un truc qui serait performance je pense, ce serait d'avoir une fonction permettant de faire un hash d'un ensemble de colonne, et de comparer ce hash lors de mon troisième union, plutôt que toutes les colonnes. Sauf que si je commence à devoir faire un HASHBYTES(concat(col1, col2, col3, etc...)) ça me semble tout aussi catastrophique... et accessoirement, passer concat(), je vais plus savoir faire la différence quand un colonne passe de null à '' par exemple.

Une autre solution serait d'utiliser des tables temporelles, et de (c'est possible ?) identifier les lignes ajoutées, modifiées et supprimées depuis al dernière itération, mais non seulement je n'ai aucune idée de comment faire, mais à nouveau, j'ai peur que ça doit moyen niveau perfs. D'autant que certaines lignes sont modifiées tous les jours (par exemple dans la table des clients, l'encours de paiement bouge presque tous les jours) et je ne veux pas que l'historique de mes lignes se mette à remplir la base... j'ai juste besoin de connaître la valeur de la veille.

Enfin, pas de traitement basé sur la date de dernière mise à jour car :
- les requêtes sont évidement des résultats de jointures avec agrégation, dès lors c'est compliqué de savoir quelle est la date réelle de dernier envoi
- y'a toujours un con pour mettre à jour en SQL et ne pas mettre à jour la date de dernière modif
- je n'exporte pas toutes les colonnes, donc une ligne modifiée n'est pas forcément différente à l'export

Des suggestions ?

[escartefigue]

On peut aussi utiliser une jointure externe FULL OUTER JOIN puis vérifier ce qui est marqué "null" à droite, à gauche et nulle part

[SQLpro]

...
Un truc qui serait performance je pense, ce serait d'avoir une fonction permettant de faire un hash d'un ensemble de colonne, et de comparer ce hash lors de mon troisième union, plutôt que toutes les colonnes. Sauf que si je commence à devoir faire un HASHBYTES(concat(col1, col2, col3, etc...)) ça me semble tout aussi catastrophique... et accessoirement, passer concat(), je vais plus savoir faire la différence quand un colonne passe de null à '' par exemple.
...

Le hachage c'est jutsment ce que fais SQL Server pour ces comparaisons...

Voir le plan d'exécution générée pour la requête :

Code :

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

1
2
3
SELECT * FROM sys.objects 
EXCEPT
SELECT * FROM sys.objects WHERE name LIKE '%abc%'

Tu y verras 2 correspondances de hash qui réalise l'opération...

En fait tu peut les réaliser avec :

Code :

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

SELECT CHECKSUM(*) FROM MaTable

et plus encore par agrégation :

Code :

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

SELECT CHECKSUM_AGG(*) FROM MaTable

A +

[Michel.Priori]

J'ai un besoin assez similaire pour effectuer un traitement d'export différentiel de données.

Salut StringBuilder,

Je pense qu'il serait judicieux d'ouvrir un nouveau post pour ce besoin

[Michel.Priori]

Je travaille avec les tables (pour simplifier drastiquement) :

Code :

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

1
2
personne (id, nom, prenom) PK(id)
tampon (version, id, nom, prenom) PK(version, id)

Le résultat attendu est :
NEW;lignes avec un ID absent du précédent export
UPD;lignes avec un ID exporté la fois précédente, mais au moins une autre colonne avec des données différentes
DEL; lignes avec un ID exporté précédement, mais absent de la nouvelle itération
[...]
Des suggestions ?

Perso, je ferais ceci:

• Ajouter une colonne row_version à la table personne : https://learn.microsoft.com/fr-fr/sq...l-server-ver16
• Créer une table export_personne_history (Id int identity(1,1), export_date, id_personne, row_vers_personne binary(8)
• Lors de l'export utiliser la clause output pour alimenter la table export_personne_history
• les valeurs NEW, UPD et DEL sont à déduire avec un CASE

Il me semble qu'utiliser une valeur stockée pour suivre la modification de valeur sur la ligne est plus rentable que de faire un hash à la volée.

[SQLpro]

Bonjour,

J'ai un besoin assez similaire pour effectuer un traitement d'export différentiel de données.

Avec le volume des données que j'ai qui augmente, le traitement que j'ai mis en place devient de plus en plus consommateur et lent, et à tendance à remplir tempdb, le journal des transactions et durer trois plombes...

Je travaille avec les tables (pour simplifier drastiquement) :

Code :

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

1
2
personne (id, nom, prenom) PK(id)
tampon (version, id, nom, prenom) PK(version, id)

Le résultat attendu est :

NEW;lignes avec un ID absent du précédent export
UPD;lignes avec un ID exporté la fois précédente, mais au moins une autre colonne avec des données différentes
DEL; lignes avec un ID exporté précédement, mais absent de la nouvelle itération

[...]

Pourquoi ne pas utilisé la fonctionnalité intégrée CDC pour ce faire ?
https://learn.microsoft.com/fr-fr/sq...l-server-ver16

A +

[Michel.Priori]

Pourquoi ne pas utilisé la fonctionnalité intégrée CDC pour ce faire ?

De ce que j'ai compris ce n'est pas un différentiel de données stockées mais un différentiel des données exportées.
Une sorte de change traking mais en version export

[StringBuilder]

De ce que j'ai compris ce n'est pas un différentiel de données stockées mais un différentiel des données exportées.
Une sorte de change traking mais en version export

Oui c'est ça.
Toutes les modifications ne m'intéressent pas.

J'ai bien noté les différentes pistes proposées, et je vais tâcher de les essayer, mais dans l'immédiat je me retrouve confronté à d'autres urgences... en attendant j'ai trouvé un palliatif en réduisant la quantité de données d'historique (en fait, ce qui était long c'était les deux lignes suivantes :

Code code :

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

1
2
3
 
delete tampon where version > 7;
update tampon set version = version +1;

Et pas l'usine à gaz derrière (enfin... un peu aussi, mais c'est de l'ordre de 1/10).
En effet, contrairement à mon exemple initial, je gardais 8 jours d'historique. Et visiblmeent mettre à jour l'id de 7 * 15 millions de lignes SQL Server ça le pénalise plus que d'aller faire un requête de psychopathe sur 25 tables qui retourne 15 millions de lignes...

Là je ne garde plus que les données de la veille réellement nécessaire au différentiel, et j'ai retrouvé un comportement raisonable.

La prochaine étape je pense c'est que garder dans un coin la dernière génération exportée et ne plus mettre à jour toutes les lignes pour comparer systématiquement les génération 1 et 2... Ca me permettra de conserver à nouveau un peu plus d'historique au cas où on me demande de justifier ce qui c'est passé il y a 3 jours.

[Michel.Priori]

J'ai bien noté les différentes pistes proposées, et je vais tâcher de les essayer, mais dans l'immédiat je me retrouve confronté à d'autres urgences... en attendant j'ai trouvé un palliatif en réduisant la quantité de données d'historique (en fait, ce qui était long c'était les deux lignes suivantes :

Code code :

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

1
2
3
 
delete tampon where version > 7;
update tampon set version = version +1;

Plutôt que faire évoluer la version, en stockant la date d'export on peut purger sur un delais, donc sans update préalable ; on gagne 1 instruction sur 2.