Discussion :

[yohann_h]

Bonjour,

Dans une table de fait, je voudrais ajouter une colonne qui est le résultat d'un calcul de plusieurs autres colonnes de la même table.


Y a t-il une différence (performances, bonne pratique...) entre déclarer cette colonnes en tant que PERSISTED ou la déclarer comme une colonne "normale" et faire le calcul dans mon traitement SSIS ?

Merci de vos réponses,
Bonne journée

[escartefigue]

Par définition, une valeur calculée n'a rien à faire dans une table (sauf quelques exceptions réglementaires genre valeur TTC = HT+TVA)

On peut mettre une valeur calculée dans une vue, ou faire calculer la valeur par l'application

[StringBuilder]

Il parle des colonnes calculées :

https://msdn.microsoft.com/en-US/Library/ms188300.aspx

Et elles, elles ont toute leur place dans une base de données (car l'intégrité de leur calcul est garantie, contrairement aux colonnes contenant des valeurs calculées, qui sont à bannir).


Sinon, pour en revenir à la question originale, je pense que si c'est uniquement dans l'objectif d'un export de données, ça ne vaut pas le coup de créer des colonnes calculées.

D'un point de vue performances, je doute que ça change quoi que ce soit entre un calcul directement dans la requête de sélection.

Et rendre persisted les données d'une colonne calculée, ça n'a de sens, selon moi, que si un index y est associé (genre sélection de toutes les lignes de commandes dont le total est supérieur à 100 €).

Ici, on va perdre en espace de stockage inutilement, et niveau performances, on va franchement pas gagner grand chose (sauf si on exporte très souvent des données qui bougent peu).

[SQLpro]

Voyons par un exemple :

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
USE tempdb
GO
 
CREATE TABLE FAIT 
(
C1 INT,
C2 float,
C3 DECIMAL(23,6),
C4 INT,
C5 float,
C6 DECIMAL(23,6),
C7 INT,
C8 float,
C9 DECIMAL(23,6),
C10 INT,
C11 float,
C12 DECIMAL(23,6)
)
GO

Code :

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

1
2
3
4
5
6
7
8
-- les données (50 000 lignes)
SET NOCOUNT ON;
INSERT INTO FAIT 
SELECT CHECKSUM(NEWID()), CHECKSUM(NEWID()) / 12345.6789, CHECKSUM(NEWID())*0.0123456789, 
       CHECKSUM(NEWID()), CHECKSUM(NEWID()) / 12345.6789, CHECKSUM(NEWID())*0.0123456789, 
       CHECKSUM(NEWID()), CHECKSUM(NEWID()) / 12345.6789, CHECKSUM(NEWID())*0.0123456789, 
       CHECKSUM(NEWID()), CHECKSUM(NEWID()) / 12345.6789, CHECKSUM(NEWID())*0.0123456789
GO 50000

Requête brute avec calcul dans le SELECT :

Code :

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

1
2
3
SET STATISTICS TIME ON;
SELECT *, C1 / LOG(ABS(C2)) / LOG10(ABS(C3)) - C4 / LOG(ABS(C5)) / LOG10(ABS(C6)) + C7 / LOG(ABS(C8)) / LOG10(ABS(C9)) - C10 / LOG(ABS(C11)) / LOG10(ABS(C12))
FROM   FAIT;

--> Temps UC = 94*ms, temps écoulé = 581*ms.

Ajout colonne calculée persistante :

Code :

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

1
2
3
ALTER TABLE FAIT 
ADD CALC AS C1 / LOG(ABS(C2)) / LOG10(ABS(C3)) - C4 / LOG(ABS(C5)) / LOG10(ABS(C6)) + C7 / LOG(ABS(C8)) / LOG10(ABS(C9)) - C10 / LOG(ABS(C11)) / LOG10(ABS(C12)) PERSISTED;
GO

Requête avec la colonne précalculée :

Code :

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

SELECT * FROM FAIT

--> Temps UC = 31*ms, temps écoulé = 580*ms.

=> GAIN de performances : x 3

CQFD !

A +

[escartefigue]

Il parle des colonnes calculées

oups en effet, autant pour moi

[yohann_h]

Merci pour vos réponses !

Je vais m'orienter vers les colonnes calculées persistantes, le gain de performance est vraiment intéressant, d'autant plus que je peux atteindre le million d'entrées dans la table en questions. Avec les précisions de SQLpro, cela me parait le plus adapté en effet.

[aieeeuuuuu]

Bonjour,

Attention, il faut quand même peser le pour et le contre.

L'exemple de SQLPro montre en effet des temps de réponses plus court avec la colonne calculée, mais cache l’envers du décors :
temps de mise à jour (beaucoup) plus long, nombre de lectures plus importantes (y compris pour les requêtes qui n'accéderont pas à cette colonne ! ).
Il faut voir aussi la nature du calcul : SQLPro à volontairement fait un calcul relativement complexe. Si c'est pour faire une addition, ça vaut moins le coup

Notez par ailleurs qu'il est possible de créer un index sur une colonne calculée non persistante : si cette colonne n'est utilisée que par quelques requêtes, il peut être intéressant de l'ajouter aux index supportant ces requêtes.

il convient donc de se poser les bonnes questions :
1/ le calcul est-il complexe
2/ les colonne entrant en jeu dans le calcul seront-elles souvent mises à jour
3/ dans quelle proportion de requêtes portant sur la tables la colonne calculée sera-t-elle utilisée
4/ la colonne calculée sera-t-elle utilisée comme filtre ?
...

Et ce n'est pas exhaustif. Le mieux ensuite, c'est de tester avec des données de production

N'hésitez pas a donner plus de détail sur votre contexte, voire nous fournir la structure de la table.

[StringBuilder]

Voyons par un exemple

Et l'insertion ne sera-t-elle pas plus lente ?

Le temps de calcul (gagné au moment du select) n'a-t-il pas été perdu au moment de la création de la colonne ?

Ce qui tends à me faire maintenir que la colonne calculée persisted n'a d'intérêt que :
- si on pose un index et qu'on effectue des filtres/tris dessus
- si on requête souvent les mêmes lignes dont les valeurs restent statiques

Sinon, on oblige le SGBD à recalculer inutilement la valeur persisted à chaque mise à jour des autres colonnes, alors peut-être qu'on ne va sélectionner la ligne qu'une seule fois (par exemple dans le cas d'une synchro entre deux serveurs sur une table de faits qui ne bouge pas -factures par exemple-).

[StringBuilder]

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
 
USE SandBox
GO
 
drop table FAIT;
go
CREATE TABLE FAIT 
(
C1 INT,
C2 float,
C3 DECIMAL(23,6),
C4 INT,
C5 float,
C6 DECIMAL(23,6),
C7 INT,
C8 float,
C9 DECIMAL(23,6),
C10 INT,
C11 float,
C12 DECIMAL(23,6)
)
GO
 
SET NOCOUNT ON;
 
select getdate();
go
declare @i int;
set @i = 0
 
while @i < 50000
begin
	INSERT INTO FAIT 
	SELECT CHECKSUM(NEWID()), CHECKSUM(NEWID()) / 12345.6789, CHECKSUM(NEWID())*0.0123456789, 
		   CHECKSUM(NEWID()), CHECKSUM(NEWID()) / 12345.6789, CHECKSUM(NEWID())*0.0123456789, 
		   CHECKSUM(NEWID()), CHECKSUM(NEWID()) / 12345.6789, CHECKSUM(NEWID())*0.0123456789, 
		   CHECKSUM(NEWID()), CHECKSUM(NEWID()) / 12345.6789, CHECKSUM(NEWID())*0.0123456789
	set @i = @i + 1;
end;
GO
 
SELECT *, C1 / LOG(ABS(C2)) / LOG10(ABS(C3)) - C4 / LOG(ABS(C5)) / LOG10(ABS(C6)) + C7 / LOG(ABS(C8)) / LOG10(ABS(C9)) - C10 / LOG(ABS(C11)) / LOG10(ABS(C12))
FROM   FAIT;
go
select getdate();
go

=> 24 secondes sur mon laptop

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
 
USE SandBox
GO
 
drop table FAIT;
go
CREATE TABLE FAIT 
(
C1 INT,
C2 float,
C3 DECIMAL(23,6),
C4 INT,
C5 float,
C6 DECIMAL(23,6),
C7 INT,
C8 float,
C9 DECIMAL(23,6),
C10 INT,
C11 float,
C12 DECIMAL(23,6),
CALC AS C1 / LOG(ABS(C2)) / LOG10(ABS(C3)) - C4 / LOG(ABS(C5)) / LOG10(ABS(C6)) + C7 / LOG(ABS(C8)) / LOG10(ABS(C9)) - C10 / LOG(ABS(C11)) / LOG10(ABS(C12)) PERSISTED
)
GO
 
SET NOCOUNT ON;
 
select getdate();
go
declare @i int;
set @i = 0
 
while @i < 50000
begin
	INSERT INTO FAIT 
	SELECT CHECKSUM(NEWID()), CHECKSUM(NEWID()) / 12345.6789, CHECKSUM(NEWID())*0.0123456789, 
		   CHECKSUM(NEWID()), CHECKSUM(NEWID()) / 12345.6789, CHECKSUM(NEWID())*0.0123456789, 
		   CHECKSUM(NEWID()), CHECKSUM(NEWID()) / 12345.6789, CHECKSUM(NEWID())*0.0123456789, 
		   CHECKSUM(NEWID()), CHECKSUM(NEWID()) / 12345.6789, CHECKSUM(NEWID())*0.0123456789
	set @i = @i + 1;
end;
GO
 
SELECT *, CALC
FROM   FAIT;
go
select getdate();
go

=> 25 secondes sur le même laptop

Donc on peut pas parler de différence significative, même si au contraire, mon exemple temps à invalider le gain présenté par SQLPro

Donc attention : sur des données qui ne bougent pas des masses, pas de souci. Si c'est des données qui bougent beaucoup, attention aux temps de mise à jour !

Quant à une colonne non persistante, aucun intérêt si ce n'est simplifier la requête de sélection.