Gestion de cache d'un SGBD

**DOliv** · 29/08/2023, 17h39

Bonjour à tous.tes,
J'ai mis ce titre là parce que je ne savais pas quoi mettre d'autre.
En fait je me demande ce qui peut faire que l'exécution d'une procédure stockée devienne de plus en plus lente en cours d'exécution.

Elle met en oeuvre un cursor qui boucle dans un deuxième.

En clair, ou pas, le premier boucle sur un SELECT de lignes d'entête.
Chaque ligne de ce premier cursor sert de clé au deuxième cursor qui SELECT un ensemble de lignes liées par ID à l'entête.
Un nouvel ID est affecté à l'enreg du premier CURSOR et est donc UPDATé dans tous les enreg du deuxième cursor, qui sont ensuite INSERTés dans une table. Puis l'enreg du premier cursor est INSERTé dans une autre table.
Ni plus ni moins qu'un système d'historisation.

Le premier boucle sur plusieurs dizaines de milliers de lignes, et le deuxième sur plusieurs centaines de milliers de lignes.

Donc, c'est long. Mais mon questionnement concerne le fait que, au fur et mesure du traitement, celui-ci est de plus en plus long.
En effet, le nombre de lignes "détail" par ligne "principales" est en moyenne le même. Le traitement des premiers enregs sont très rapide, et les derniers sont beaucoup plus lents, et de plus en plus lents pendant le traitement.
D'où ma question :
Ne serait-il pas possible de faire une opération quelconque, par exemple après chaque INSERT, pour maintenir le même niveau de performance tout au long du traitement ?
Est ce nécessaire de préciser le SGBD ? Ou le problème, comme je le pense, est inhérent à tous les SGBD.

Merci d'avance pour vos observations et, au-moins, d'avoir lu jusqu'au bout

**tatayo** · 29/08/2023, 18h03

Bonjour,
Sans connaitre ni le code, ni la description des tables, ni le SGBD concerné, difficile de répondre.

Mais j'ai tout de même nue question: plutôt que d'utiliser 2 curseurs imbriqués, n'est-il pas possible d'utiliser des requêtes de type INSERT INTO ... SELECT ?
Je pense que ce serait bien plus rapide, surtout s'il faut traiter plusieurs milliers de lignes.

Tatayo.

**SQLpro** · 29/08/2023, 19h39

Envoyé par DOliv

...
En fait je me demande ce qui peut faire que l'exécution d'une procédure stockée devienne de plus en plus lente en cours d'exécution.

Elle met en oeuvre un cursor qui boucle dans un deuxième.

Vous êtes en train de faire de la programmation spaghetti (itérations avec des boucles dans tous les sens) alors qu'un SGBD Relationnel est fait pour exécuter très rapidement (grâce aux index, au parallélisme et à l'optimiseur) des requêtes ensemblistes !

Dès lors le comportement de votre code sera strictement linéaire et va devenir de plus en plus lent à la charge...

Envoyé par DOliv

En clair, ou pas, le premier boucle sur un SELECT de lignes d'entête.
Chaque ligne de ce premier cursor sert de clé au deuxième cursor qui SELECT un ensemble de lignes liées par ID à l'entête.
Un nouvel ID est affecté à l'enreg du premier CURSOR et est donc UPDATé dans tous les enreg du deuxième cursor, qui sont ensuite INSERTés dans une table. Puis l'enreg du premier cursor est INSERTé dans une autre table.

NOTE importante : dans un SGBDR le terme "enregistrement" n'a pas sa place. On parle de lignes (rows).
A lire : https://sqlpro.developpez.com/cours/sqlaz/erreurs/#L2

Vous êtes typiquement dans le piège RBAR... Row By Agonizing Row... À lire :
https://www.red-gate.com/simple-talk...agonizing-row/

Envoyé par DOliv

Ni plus ni moins qu'un système d'historisation.

Le premier boucle sur plusieurs dizaines de milliers de lignes, et le deuxième sur plusieurs centaines de milliers de lignes.

Donc, c'est long. Mais mon questionnement concerne le fait que, au fur et mesure du traitement, celui-ci est de plus en plus long.

Chaque fois que vous exécutez une requête les données sont mise en cache. Une requête elle même se compose de plusieurs phases d'exécution dont certaines parties peuvent comporter des dizaines d'appel à des tables système... Plus vous avancez dans vos boucles, plus il y aura besoin de mémoire et plus cela deviendra lent....

Envoyé par DOliv

En effet, le nombre de lignes "détail" par ligne "principales" est en moyenne le même. Le traitement des premiers enregs sont très rapide, et les derniers sont beaucoup plus lents, et de plus en plus lents pendant le traitement.

Tout à fait symptomatique du RBAR...

Envoyé par DOliv

D'où ma question :
Ne serait-il pas possible de faire une opération quelconque, par exemple après chaque INSERT, pour maintenir le même niveau de performance tout au long du traitement ?
Est ce nécessaire de préciser le SGBD ? Ou le problème, comme je le pense, est inhérent à tous les SGBD.

NON !!!

Il faut tout simplement faire une seule requête de tout votre traitement....

Postez votre code que l'on vous aide !

A +

PS : certains SGBDR, comme Microsoft SQL Server, sont dotés d'une historisation automatique des lignes des tables. À lire :
https://blog.developpez.com/sqlpro/p...r-presentation

**DOliv** · 30/08/2023, 11h13

Envoyé par tatayo

Bonjour,
Sans connaitre ni le code, ni la description des tables, ni le SGBD concerné, difficile de répondre.

Mais j'ai tout de même nue question: plutôt que d'utiliser 2 curseurs imbriqués, n'est-il pas possible d'utiliser des requêtes de type INSERT INTO ... SELECT ?
Je pense que ce serait bien plus rapide, surtout s'il faut traiter plusieurs milliers de lignes.

Tatayo.

Oui mais là, ce n'est pas possible de faire la même chose avec un INSERT INTO ... SELECT.

**DOliv** · 30/08/2023, 11h17

Intéressant. Je vais déjà lire tout ça et repenser ma copie. Merci à tous.

**DOliv** · 30/08/2023, 16h29

Mouai... En fait, j'ai beau fouiller toutes les techniques SQL, même avec des CTE à foison, je ne vois pas bien comment il pourrait être possible de sélecter un ensemble de données issu de deux tables différentes, dont les lignes sont liées par un ID.
Modifier la valeur des IDs de liaison des lignes d'entête ET, évidemment des lignes de détail liées.
INSERTé le résultat dans deux nouvelles tables.
Le Tout dans une seule requête

Alors je pense à un script SQL, peut-être. Mais pourquoi un script SQL serait-il plus rapide qu'une procédure stockée ?

**tatayo** · 30/08/2023, 16h52

Pourquoi avec une seule requête ?
Tu peux faire l'historisation avec 2 requêtes, une pour les entêtes et une pour les lignes.
J'imagine bien qu'il y a un "lien" entre la table des entête et son pendant "historisé".
Donc ça va donner un truc du genre:

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
 
-- On commence par historiser les entêtes
insert into histo_ent(col1,col2,col3...)
select col1,col2,col3,...
from ent
where ...
 
- Ensuite on historise les lignes correspondantes
insert into histo_lig(idHistoEnt,colx,coly,col7,...)
select histo_ent.ident,colx,coly,colz...
from histo_ent
inner join ent on ent.id = histo_ent.idEnt
inner join lig on lig.idEnt = ent.idEnt
left outer join histo_lig on histo_lig.idHistoEnt = histo_ent.Id
where ... -- Les même critères que ci-dessus
and histo_lig.id is null -- On peut aussi tester l'absence de ligne, pour plus de sécurité

En choisissant le bon niveau d'isolation bien sûr (serializable ?).
C'est écrit "un peu à la va vite", mais l'idée est là.

Tatayo.

**DOliv** · 30/08/2023, 19h05

Merci Tatayo,
mais une fois les entêtes INSERTés avec le nouvel ID tu ne connais plus le lien avec leurs lignes détail qui contiennent toujours l'ancien ID.
Sauf erreur de ma part, la seule solution est de updater le nouvel ID affecté à un entête, à chacune des lignes détails de cet entête, avant de passer à l'entête suivant. Sinon le lien est brisé

**SQLpro** · 30/08/2023, 19h08

Tout dépend du SGBDR que vous utilisez... Certains SGBDR ont une clause OUTPUT ou équivalent, qui, lors d'une mise à jour renvoie au gré de l'écriture de cette clause, les données avant ou après la mise à jour...

On peut aussi passer par une construction XML en sortie d'une requête multitable pour alimenter les deux tables en parsant le XML... Facile à faire avec SQL Server par exemple... Clause FOR XML pour générer une sortie XML de la requête au lieu de tabulaire et ensuite parser le XML deux fois, pour chacune des insertions.

Donc, sans le SGBDR ciblé et sans le DDL des tables difficile de te répondre !

A +

**tatayo** · 31/08/2023, 09h27

Envoyé par DOliv

Merci Tatayo,
mais une fois les entêtes INSERTés avec le nouvel ID tu ne connais plus le lien avec leurs lignes détail qui contiennent toujours l'ancien ID.
Sauf erreur de ma part, la seule solution est de updater le nouvel ID affecté à un entête, à chacune des lignes détails de cet entête, avant de passer à l'entête suivant. Sinon le lien est brisé

Tu n'as pas de lien entre un entête et son historique ?
Quand je mets en place une table d'historique, je garde toujours un lien entre la ligne d'historique et la ligne historisé.

Si ce lien existe bel et bien, à partir d'un historique d'entête tu peux "remonter" à l'entête d'origine, puis au lignes à historiser et en ayant les identifiant qui vont bien.
Et là tu as bien l'identifiant de l'entête d'origine et celui de la "version" historisé.

Tatayo.

**DOliv** · 31/08/2023, 10h38

Merci SQLpro. Très intéressant, je vais étudier ça.
Non tatayo, dans mon cas j'aurai peut-être dû parler d'archivage plutôt que d'historisation. Quoi que, dans ma tête, c'est un peu la même chose.
En fait, en continuant de schématiser, on peut dire que seul le lien ligne "maîtresse" lignes "détails" est conservé. Aucun lien utile entre les tables d'origine et les tables de destination.

**Waldar** · 31/08/2023, 11h19

Toujours sans un exemple un peu plus détaillé c'est difficile de vous aider, mais je vois trois étapes dans votre traitement :

Conserver le lien entête détail dans une table de travail.
Modifier vos entêtes.
Modifier votre détail.

**escartefigue** · 31/08/2023, 12h13

Voici un exemple possible, qui s'appuie sur une table de renumérotation :

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
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
-- création du jeu d'essai source
   create table T1ENT 
         (  T1ID  integer primary key
          , T1CH  char(4) not null
         )
   ; 
   create table T2DTL
         (  T1ID  integer 
          , T2DT  date    not null
          , PRIMARY KEY (T1ID, T2DT)
          , FOREIGN KEY (T1ID) REFERENCES T1ENT(T1ID)
         )
   ; 
   insert into T1ENT (T1ID, T1CH)
   values (1, 'truc')
        , (2, 'azer')
   ;
   insert into T2DTL (T1ID, T2DT)
   values (1, '2023-02-14')
        , (1, '2023-05-20')
        , (2, '2022-11-03')
        , (2, '2022-12-15')
        , (2, '2022-12-28')
   ;
-- création de la table de renumérotation ID source <-> ID cible
-- la contrainte PK garantit l'unicité de l'ID source
-- la contrainte UNIQUE garantit l'unicité de l'ID cible
   create table T3RENUM 
         (  T3IDOLD  integer primary key
          , T3IDNEW  integer not null
          , UNIQUE (T3IDNEW)
         )
   ;  
   insert into T3RENUM (T3IDOLD, T3IDNEW)
   values (1, 11)
        , (2, 03)
   ;
-- creation des tables cibles
   create table T1BENT 
         (  T1ID  integer primary key
          , T1CH  char(4) not null
         )
   ; 
   create table T2BDTL
         (  T1ID  integer 
          , T2DT  date    not null
          , PRIMARY KEY (T1ID, T2DT)
          , FOREIGN KEY (T1ID) REFERENCES T1BENT(T1ID)
         )
   ; 
-- alimentation des tables cibles avec ID renumérotés
   insert into T1BENT (T1ID, T1CH)
   select T3IDNEW, T1CH
   from T1ENT as T1
   inner join T3RENUM as T3
      on T3.T3IDOLD=T1.T1ID
   ;
   insert into T2BDTL (T1ID, T2DT)
   select T3IDNEW, T2DT
   from T2DTL T2
   inner join T3RENUM T3
      on T3.T3IDOLD=T2.T1ID
   ;
 
-- contrôle des résultats
   select * from T1BENT
   ;
   select * from T2BDTL
   ;

Résultat (on a bien conservé les liens entre entête et détail, mais avec les nouveaux identifiants) :

T1ID	T1CH
3	azer
11	truc

T1ID	T2DT
3	2022-11-03
3	2022-12-15
3	2022-12-28
11	2023-02-14
11	2023-05-20

Si la renumérotation est un simple calcul (par exemple ajout de 1000 à chaque identifiant), alors c'est encore plus facile, la table de renumérotation est inutile, le nouvel ID peut être calculé dans la requête.
Mais attention, cette deuxième méthode n'est pas applicable avec Access ou MYSQL qui ne sont pas ensemblistes...

**Séb.** · 31/08/2023, 14h55

Si la renumérotation est un simple calcul (par exemple ajout de 1000 à chaque identifiant), alors c'est encore plus facile, la table de renumérotation est inutile, le nouvel ID peut être calculé dans la requête.
Mais attention, cette deuxième méthode n'est pas applicable avec Access ou MYSQL qui ne sont pas ensemblistes...

C'est faisable avec MySQL à condition de spécifier un ordre d'opération :

UPDATE ma_table
SET id = id + 1000
ORDER BY id DESC;

Les ID les plus grands seront traités en premier => Pas de collision

Gestion de cache d'un SGBD

Langage SQL

Discussions similaires

Partager

Partager