Discussion :

[Waldar]

Je vois pas avec Oracle comment tu fais plus rapide et plus simple avec :old et :new

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;
/

[StringBuilder]

Plus simple, je veux bien.

Maintenant, tu mets à jour 5000 lignes en bloc, je doute que ce soit plus rapide :o

[Waldar]

Non, pas de soucis de performance même avec le trigger FOR EACH ROW (désolé pour la digression PL/SQL) :

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).

J'extrait la conclusion de ce test très simple.
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.

[StringBuilder]

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

[Sergejack]

Peut-être avec une CTE ?

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
 
with cte as
(
   SELECT B.*, ROW_NUMBER() OVER (PARTITION BY B.INDEXEDCOLUMN ORDER BY B.ORD) AS NB
    FROM B
)
SELECT
*
FROM A
INNER JOIN CTE ON (
    CTE.INDEXEDCOLUMN  = A.XXX
    AND
    CTE.NB = 1
)
WHERE A.NAME LIKE 'Blah%'

Qu'est-ce que la CTE change ?

[StringBuilder]

C'étais juste une proposition d'écriture alternative

[hmira]

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

Code SQL :

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

MaColonneEparse  INT DEFAULT 0

et où la valeur 0 (zéro) représente plus 80% des valeurs de la colonne MaColonneEparse.

Que pensez-vous de cette nouvelle fonctionnalité ?

[iberserk]

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...

[hmira]

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.

[iberserk]

Daccord, donc en effet ça manque

[Michel.Priori]

Bonsoir,

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.

En créant des colonnes {une colonne "nullable" + une colonne calculée (isnull(macolonne,0)) + trigger instead_of } c'est pas jouable ?

[hmira]

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

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

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
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+

[aieeeuuuuu]

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.

[iberserk]

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

[hmira]

@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

[aieeeuuuuu]

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...

[hmira]

@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+

[Michel.Priori]

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é

[aieeeuuuuu]

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

[Fabien Celaia]

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...)