Update sur grosse volumétrie

**Darkcristal** · 22/05/2024, 16h54

Bonjour,

j'ai une table de données détaillées qui contient actuellement 240 millions d'enregistrements pour janvier à avril 2024. Elle fait ~50Go et je fois la recalculer en full 1 fois par mois sur l'année complète (elle se vide et se réalimente au complet).

L'alimentation depuis plusieurs sources et relativement rapide, aucun problème de coté la. Les problèmes arrivent quand je corrige les données avec toute une série d'UPDATE.

Dès qu'il arrive au premier update, le journal de transaction monte rapidement à plus de 60Go (croissance auto de 10%) et tourne pendant environ 30min.

Il enchaine ensuite les UPDATE suivants et mon script tourne pendant plusieurs heures.

Ma table aura à fin 2024 ~720 millions d'enregistrement et je dois ajouter de l'historique sur plusieurs années....

J'ai tenté de modifier mes update en utilisant :

Code :

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

1
2
3
WHILE EXISTS(SELECT …)
UPDATE TOP(200000) MaTable
SET

ca évite de faire grossir mon journal de transaction mais ca n'améliore pas les perfs.

Quelles seraient les pistes d'optimisations/améliorations que je peux apporter à mon traitement pour gagner en temps de traitement ?
Si je fixe la taille de mon journal de transaction a 100Go directement est ce que ca peut me faire gagner en perf ?
Je peux aussi mettre quelques index mais vu que mes updates sont tous différents et sur des colonnes différentes c'est assez compliqué, je ne peux pas en déclarer un spécifique par update ?

Je précise que je suis sous SQL Server 2008 avec environ 20To de données.

Merci pour votre aide.

**AaâÂäÄàAaâÂäÄàAaâÂäÄ** · 22/05/2024, 17h02

Envoyé par Darkcristal

Je précise que je suis sous SQL Server 2008

Est-ce qu'il y a des indexes et une clef primaire sur votre table ?

ça serait bénéfique d'avoir la DDL de la table et les update pour voir si ça peut être optimisé.

**Darkcristal** · 22/05/2024, 18h02

Voila la table en version simplifiée. J'ai supprimé pas mal de colonnes :

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
 
CREATE TABLE dbo.MESURES (
	annee [varchar](4) NOT NULL,
	mois [varchar](2) NOT NULL,
	dimension [varchar](15) NOT NULL,
	reseau [varchar](10) NULL,
	direction [varchar](10) NULL,
	produit1 [varchar](250) NULL,
	produit2 [varchar](250) NULL,
	produit3 [varchar](250) NULL,
	produit4 [varchar](250) NULL,
	struct1 [varchar](5) NULL, --vide
	struct2 [varchar](5) NULL, --vide
	struct3 [varchar](5) NULL, --vide
	struct4 [varchar](5) NULL, --vide
	struct5 [varchar](5) NULL, --vide
	struct6 [varchar](5) NULL,
	id_client [varchar](8) NULL,
	libelle_client [varchar](250) NULL,
	type_valeur [varchar](10) NOT NULL,
	type_resultat [varchar](15) NOT NULL,
	valeur [decimal](38, 4) NULL,
)

et 3 update :

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
UPDATE MESURES
SET struct1 = s.struct1, struct2 = s.struct2, struct3 = s.struct3, struct4 = s.struct4, struct5 = s.struct5
from MESURES i
inner join (select struct1, struct2, struct3, struct4, struct5, struct6 from orga) s on (i.struct6 = s.struct6)
where coalesce(i.reseau, '') = '' and coalesce(i.struct6, '') <> ''
and i.annee = '2024'
 
update MESURES
set reseau = case when struct2='12' then 'RH' 
                  when struct1='27' then 'JUR' end
where (struct2 in ('12') or struct1 in ('27')) and coalesce(reseau, '') = ''
and annee = '2024'
 
update MESURES
set produit1 = p.produit1, produit2 = p.produit2, produit3 = p.produit3
from MESURES i
INNER JOIN referentiel_produit p on (i.produit4 = p.produit4)
where coalesce(i.produit4, '') <> '' and coalesce(i.produit4, '') = ''
and i.annee = '2024'

je n'ai aucun index ni pk sur ma table. Une fois que la table est calculée elle est montée en RAM pour restitution dans un outil de dataviz.

**SQLpro** · 22/05/2024, 19h44

Commencez par recréer votre table comme suit :

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
CREATE TABLE dbo.MESURES (
   pk BIGINT IDENTITY PRIMARY KEY,
	annee char(4) NOT NULL,
	mois char(2) NOT NULL,
	dimension char(15) NOT NULL,
	reseau char(10) NULL,
	direction char(10) NULL,
	produit1 varchar(250) NULL,
	produit2 varchar(250) NULL,
	produit3 varchar(250) NULL,
	produit4 varchar(250) NULL,
	struct1 char(5) NULL, --vide
	struct2 char(5) NULL, --vide
	struct3 char(5) NULL, --vide
	struct4 char(5) NULL, --vide
	struct5 char(5) NULL, --vide
	struct6 char(5) NULL,
	id_client char(8) NULL,
	libelle_client [varchar](250) NULL,
	type_valeur CHAR(10) NOT NULL,
	type_resultat CHAR(15) NOT NULL,
	valeur decimal(38, 4) NULL,
...

1) on ajoute une PK une table sans PK c'est revenir au COBOL
2) on met des CHAR au lieu des VARCHAR pour tout ce qui est de longeur <= 10
3) on dimensionne les fichiers de données et du JT correctement. Si votre table fait 50 Go, mettez 250 Go par sécurité pour les données
4) de même pour le JT => au moins 100 Go dans votre cas
5) on prévoit par sécurité un incrément de 64 Mo pour tous les fichiers
6) on met du RAID 0, 1, 0+1 ou 10 au niveau des disques du JT et des données
7) on change de version pour aller sur la toute dernière. La 2008 est obsolète et dangereuse

Avec tout ça vous allez sans doute diviser par 10 les temps de réponse

Il y a d'autres réglages à faire si vous êtes sur une VM

A +

**escartefigue** · 23/05/2024, 09h51

Également, il s'agit d'une table des mesures, du coup le libellé du client n'a rien à y faire : à remplacer par une clef étrangère qui fait référence à la table des clients.

Que mesure-t-on dans cette table ? Il y a également 4 produits par ligne... étrange, si la mesure se rapporte à un produit, alors il faut une ligne par produit et remplacer là aussi le libellé du produit par une clef étrangère référençant le produit.
Et que sont les 6 colonnes "vides" struct1 à struct6 ?
De plus, les colonnes "nullables" devraient être l'exception, or ici, la plupart des colonnes sont nullables

Vu la remarque "j'ai supprimé pas mal de colonnes", il s'agit très probablement d'une table obèse, source de tous les maux : redondance de données, intégrité non vérifiée, performances désastreuses, accès concurrents dégradés, index pléthoriques, espaces data et index surdimensionnés, etc.

La modélisation d'une base de données est essentielle pour la fiabilité, la stabilité et les performances. Malheureusement, c'est un aspect bien trop souvent négligé.

**Darkcristal** · 23/05/2024, 10h19

Super merci pour ces préconisations. Je vais déjà appliquer le points 1 à 5.

Concernant les derniers points, je ne suis pas admin sur la VM et l'upgrade de sql server 2008 est bloqué jusqu'en 2026 par le groupe

Mais si tu as des réglages à me donner pour le VM je peux passer les infos.

Merci

**vanagreg** · 23/05/2024, 10h32

Envoyé par escartefigue

La modélisation d'une base de données est essentielle pour la fiabilité, la stabilité et les performances. Malheureusement, c'est un aspect bien trop souvent négligé.

En effet, et une mauvaise modélisation peut avoir un impact très négatif sur les performances. C'est vraiment crucial de bien modéliser dès le départ, sinon une fois le mauvais modèle validé, faire marche arrière est très compliqué voire impossible. Obligé de faire avec!

**Darkcristal** · 23/05/2024, 12h10

Envoyé par escartefigue

Également, il s'agit d'une table des mesures, du coup le libellé du client n'a rien à y faire : à remplacer par une clef étrangère qui fait référence à la table des clients.

Que mesure-t-on dans cette table ? Il y a également 4 produits par ligne... étrange, si la mesure se rapporte à un produit, alors il faut une ligne par produit et remplacer là aussi le libellé du produit par une clef étrangère référençant le produit.
Et que sont les 6 colonnes "vides" struct1 à struct6 ?
De plus, les colonnes "nullables" devraient être l'exception, or ici, la plupart des colonnes sont nullables

Vu la remarque "j'ai supprimé pas mal de colonnes", il s'agit très probablement d'une table obèse, source de tous les maux : redondance de données, intégrité non vérifiée, performances désastreuses, accès concurrents dégradés, index pléthoriques, espaces data et index surdimensionnés, etc.

La modélisation d'une base de données est essentielle pour la fiabilité, la stabilité et les performances. Malheureusement, c'est un aspect bien trop souvent négligé.

C'est une table dénormalisée qui remonte dans un outil de dataviz. Cette table agrège plusieurs tables de fait sur différents périmètres.
Les colonnes produit et struct sont des hiérarchies.

Les colonnes sont null à l'init mais sont complétées par UPDATE justement. Se sont ces updates qui mettent beaucoup de temps.

**Michel.Priori** · 02/06/2024, 18h32

Envoyé par escartefigue

Vu la remarque "j'ai supprimé pas mal de colonnes", il s'agit très probablement d'une table obèse, source de tous les maux : redondance de données, intégrité non vérifiée, performances désastreuses, accès concurrents dégradés, index pléthoriques, espaces data et index surdimensionnés, etc.

Faire une modélisation et constater les écarts est la première étape.
A partir de là généralement on se dit que la charge de travail pour aligner la structure de la base, transférer les données, le tout en synchronicité avec le développement => pas possible.

C'est faux.
On peut :
* faire un nouveau schéma (c'est plus propre)
* y créer les tables telles qu'elles auraient due être (normalisées)
* Préparer la bascule
** scripter les migrations de données (INSERT et DELETE)
** DROP des tables "legacy" une fois vidées
** CREATE VIEW pour recréer les projections aux noms des tables legacy
** Scripter les triggers INSTEAD OF sur les vues pour alimenter les nouvelles tables

Une fois que la bascule est faite, on informe les développeurs que les choses ont changé ^^
In fine un audit viendra valider que plus aucun appel ne se fait sur les projections legacy et DROP de celles ci

Et oui,

C'est du boulot
Faudra tester/valider le code
Faudra prévoir une fenêtre de maintenance

**KyoshiroKensei** · 04/06/2024, 14h16

Certaines fois, j'ai des doutes sur mes modélisations. Heureusement, il y a ce genre de problème qui arrive sur le forum et me conforte dans mes choix

Je plussoie tout ce qui a été dit. Il m'a fallu 10 ans pour passer de tables dénormalisées à des tables plus proche de la norme dans tout nos systèmes, justement par avec la méthode de Michel.Priori, certaines fois les 2 versions de table étaient en production le temps de basculer au fur et à mesure les programmes. Et le résultat est là: moins de panne, plus de performance.

Les conseils fournis sont la bases des bonnes pratiques. Une petite astuce: si vous utilisez principalement les données les plus récentes, il est intéressant de mettre l'index cluster en décroissant (si l'ordre d'insertion correspond à l'âge des données).

Update sur grosse volumétrie

Développement SQL Server

Discussions similaires

Partager

Partager