Je ne sais pas si c'est le meilleur exemple :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7 when (:old.Artikelbez <> :new.Artikelbez) begin update CRM_04 set Text = :new.Artikelbez where id = :old.id_04; end; /
Je ne sais pas si c'est le meilleur exemple :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7 when (:old.Artikelbez <> :new.Artikelbez) begin update CRM_04 set Text = :new.Artikelbez where id = :old.id_04; end; /
Plus simple, je veux bien.
Maintenant, tu mets à jour 5000 lignes en bloc, je doute que ce soit plus rapide :o
Non, pas de soucis de performance même avec le trigger FOR EACH ROW (désolé pour la digression PL/SQL) :
J'extrait la conclusion de ce test très simple.
Code : 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
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113 create table crm_04 ( id integer not null primary key , text varchar2(500) null ); -- table CRM_04 créé(e). create table article ( id integer not null primary key , id_04 integer null , Artikelbez varchar2(500) null ); -- table ARTICLE créé(e). insert into crm_04 (id, text) select level, sys.dbms_random.string('a', 200) from dual connect by level <= 1000000; -- 1*000*000 lignes inséré. -- Elapsed: 00:02:53.618 commit; -- validé (commit). insert into article (id, id_04, Artikelbez) select level , floor(sys.dbms_random.value(1, 1000000)) , sys.dbms_random.string('a', 200) from dual connect by level <= 1000000; -- 1*000*000 lignes inséré. -- Elapsed: 00:02:57.179 commit; -- validé (commit). -- Temps de référence de la mise à jour simple update article set artikelbez = sys.dbms_random.string('a', 300) where id between 1 and 100000; -- 100*000 lignes mis à jour. -- Elapsed: 00:00:27.316 commit; -- validé (commit). -- Création du trigger FOR EACH ROW create or replace trigger tgbu_article before update on article for each row when (old.Artikelbez <> new.Artikelbez) begin update crm_04 set Text = :new.Artikelbez where id = :new.id_04; end; / -- Elément TRIGGER TGBU_ARTICLE compilé update article set artikelbez = sys.dbms_random.string('a', 300) where id between 100001 and 200000; -- 100*000 lignes mis à jour. -- Elapsed: 00:00:32.720 -- 5 secondes de plus que le temps de référence, on va dire 15 à 20% commit; -- validé (commit). -- Suppression du trigger FOR EACH ROW et simulation de la mise à jour ensembliste avec deux requêtes drop trigger tgbu_article; -- trigger TGBU_ARTICLE supprimé(e). update article set artikelbez = sys.dbms_random.string('a', 300) where id between 200001 and 300000; -- 100*000 lignes mis à jour. -- Elapsed: 00:00:27.651 merge into crm_04 tgt using ( select id_04 , max(id) as id , max(artikelbez) keep(dense_rank first order by id desc) as artikelbez from article group by id_04 ) src on (src.id_04 = tgt.id and src.id between 200001 and 300000) when matched then update set tgt.text = src.Artikelbez; -- 47*213 lignes fusionné. -- Elapsed: 00:00:03.051 commit; -- validé (commit).
Mise à jour de 100k lignes dans 1M : 27,5 secondes.
Avec un trigger for each row : 32,7 secondes
Sans trigger avec requête ensemblise : 30,7 secondes.
Le surcoût du trigger for each row sur ce test est inférieur à 10%, sachant que les triggers n'existent pas pour gérer ce genre de volume.
Mouais, enfin entre un update qui porte sur 100k lignes et 100k update d'une seule ligne, je pense quand même que "en conditions réelles", la balance penche quand même du côté du gros update![]()
C'étais juste une proposition d'écriture alternative
Permettre de définir la propriété éparse (SPARSE) sur une colonne non-nullable, mais, au lieu d'optimiser le stockage des valeurs nulles (NULL), on optimiserait le stockage d'une valeur particulière (une constante) non nulle (NOT NULL), utilisée généralement comme valeur par défaut.
J'ai eu récemment le besoin de cette fonctionnalité (qui actuellement n'existe pas !) pour des tables volumineuses d’un client où des colonnes présentent toutes les caractéristiques des colonnes éparses, excepté le fait que le caractères éparse n'est pas lié aux valeurs NULL, mais à une valeur (une constante) particulière non nulle, généralement la valeur par défaut, non nulle, de la colonne, et il était inenvisageable, pour diverses raisons, de remettre en cause la structure de ces tables, pour par exemple rendre ces colonnes nullables et adapter le code des applications. En effet, le risque de régression était trop important, compte tenu du poids énorme du code existant.
Imaginez une table volumineuse (plusieurs dizaines de millions de lignes) avec une colonne non-nullable nommée MaColonneEparse
et où la valeur 0 (zéro) représente plus 80% des valeurs de la colonne MaColonneEparse.
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part MaColonneEparse INT DEFAULT 0
Que pensez-vous de cette nouvelle fonctionnalité ?
Bonjour hmira.
Tu peux obtenir ce que tu veux avec SPARSE+index filtré non?
Alors certes ce n'est pas vraiment une fonctionnalité puis-qu’à gérer toi même...
Bonjour iberserk,
Non ! C'est une fonctionnalité qui n'existe pas.
Actuellement, tu ne peux pas définir SPARSE sur une colonne dotée d'une contrainte valeur par défaut.
Daccord, donc en effet ça manque![]()
Bonsoir,
En créant des colonnes {une colonne "nullable" + une colonne calculée (isnull(macolonne,0)) + trigger instead_of } c'est pas jouable ?Non ! C'est une fonctionnalité qui n'existe pas.
Actuellement, tu ne peux pas définir SPARSE sur une colonne dotée d'une contrainte valeur par défaut.
Bonjour Michel,
Et Merci beaucoup pour l'idée. C'est très astucieux ! Mais, je ne suis pas sûr que ce soit jouable. En effet, le code déjà existant, (ne pouvant être remis en cause) ne peut référencer la colonne calculée !
Le fait de rendre nullable ces colonnes et d’utiliser trigger instead_of pour forcer l’initialisation à 0 (Zéro) de ces colonne ne résoudra pas le problème puisque l’optimisation, en termes de stockage, s’applique uniquement aux valeurs nulles (NULL) et ne s’appliquent pas aux valeurs 0 (Zéro) pour un INT par exemple.
Ton idée astucieuse, me fait penser à une autre idée, mais je ne sais non plus pas si cela est jouable (?)
1 - Renommer la table initiale sous autre nom, par exemple renommer dbo.MaTable en dbo.MaTable_Internal
2 - Définir une vue portant le même nom que table initiale, soit dbo.MaTable établie avec SELECT sur la table interne dbo.MaTable_Internal Exemple
3 – Créer un trigger instead_of, non pas sur la table interne dbo.MaTable_Internal, mais sur la vue dbo.MaTable, désormais exposée à l’application, en lieu et place de la table interne, et ce, pour synchroniser et mettre à joint la table interne dbo.MaTable_Internal
Code SQL : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6 CREATE VIEW dbo.dbo.MaTable AS SELECT Col1, Col2, ISNULL(MaColonneEparse, 0) as MaColonneEparse, ... FROM dbo.MaTable_Internal
4 – Le code déjà existant continuera à référencer l'objet dbo.MaTable , mais cette fois-ci, il référencera la vue au lieu de la table interne, et ne verra que du feu.
Voilà, il me reste à mettre en œuvre, à tester et à valider cette idée.
Mais, ce serait mieux si cette fonctionnalité était disponible nativement, cela éviterait tout ce trafic et toute cette gymnastique d’esprit.
Merci.
A+
Bonjour,
Je m'interrogeai aussi sur l'utilité du trigger INSTEAD OF dans la solution proposée par Michel.Priori, mais a mon sens il n'a pas pour vocation de mettre une valeur par défaut.
Je pense qu'il voit le principe un peu comme vous avec la vue :
1/ ajout d'une (vraie) colonne nullable, eparse, et copie des valeurs spéciales (NULL lorsqu'il s'agit de la valeur par défaut)
2/ suppression de la colonne actuelle
3/ ajout de la colonne calculée, s'appuyant sur la nouvelle colonne, et portant le nom de l'ancienne.
Là aussi, les clients ne devraient y voir que du feu.
Attention malgré tout à l'ajout de colonne calculées qui peut entraîner des plantages sur les CUD (insert/update/delete) si les options de sessions ne sont pas compatibles, déjà maintes fois vécu (procédures stockées, job sql etc.).
Cordialement
@aieeeuuuuu,
Bonjour et merci pour la solution technique, que vous proposez et que je trouve aussi intéressante. Si j'ai bien suivi, il s'agit d'une variante de la solution que j'ai imaginée, où je passe par une vue, et un trigger INSTEAD OF etc.
Dans la solution que vous proposez, vous utilisez la table elle-même, avec les modifications indiquées etc, (donc plus besoin de vue), mais, sauf erreur de ma part, vous envisagez aussi de faire appel à un trigger INSTEAD OF pour réconcilier et synchroniser la colonne interne désormais nullable ?
@iberserk
Merci pour le rappel et l'alerte concernant les colonnes calculées, il y a effectivement des restrictions et des règles à respecter.
Pour celles et ceux qui veulent plus de détails à ce sujet voir lien ci-dessous :
https://www.developpez.net/forums/d1...s/#post8276490
il faut rendre à César... initialement la solution est de Michel.Priori
Pour ma part je voyais plutôt la même solution que vous...
et effectivement, dans mon dernier post, j'ai oublié de parler du trigger (qui était pourtant l'objet du post à la base...)
Je trouvais la solution avec colonne calculée intéressante, notamment si la colonne en question doit être clef d'index.
Mais... il y a un mais... et un gros !
Après test, je constate que même avec un trigger INSTEAD OF, SQL Server refuse une commande UPDATE portant sur une colonne calculée...
Il faudra donc bien une vue pour que ça reste transparent pour les utilisateurs...
@aieeeuuuuu,
Oui, il faut rendre à Cézar ce qui est à César.
C'est Cézar, de son vrai nom Michel.Priori, qui nous a insufflé l'idée initiale et on le remercie
Pour revenir à ce que vous disiez, et suite aux premiers tests que vous avez effectués, etc. (et un grand merci au passage), mon approche qui consiste à utiliser une vue serait la bonne, encore faut-il maintenant, mettre en œuvre, tester et valider cette solution, ce que je ne manquerai pas de faire ...
Merci à vous tous.
A+
Bonjour,
Merci à vous pour ces tests que moi non plus je n'avais pas mené en ce sens.
Le fait de passer par la vue me fait poser la question pour la valeur 0.
Vu qu'elle n'existe pas dans la table, est-ce que la transposition permet d'utiliser l'index ???
Donc
1- il faut étudier si la recherche de la valeur 0 est incidente sur le temps de traitement des rq actuelles
2- Effectivement, il manque la fonctionnalité![]()
et quitte a partir sur une telle solution (modification de modèle + vue pour garder le modèle externe stable), il serait intéressant de comparer avec une alternative :
Déplacer la colonne en question dans une nouvelle table, en multiplicité 0-1.
Avec bien entendu une vue (+ trigger) par dessus, sur le même principe
Etre en mesure de restaurer un objet / une table d'un backup, sans remonter toute la base...
Une écriture allégée des curseur (à la Oracle for cur as (select...))
Un "vrai" cluster, avec un vrai actif-actif (mais pour ça, il faut gérer les accès concurrents dans des mémoires différentes...)
Vous avez un bloqueur de publicités installé.
Le Club Developpez.com n'affiche que des publicités IT, discrètes et non intrusives.
Afin que nous puissions continuer à vous fournir gratuitement du contenu de qualité, merci de nous soutenir en désactivant votre bloqueur de publicités sur Developpez.com.
Partager