[SQL Server 2012] Table de fait - persisted vs caclul dans l'etl

Version imprimable

01/06/2015, 11h23
yohann_h

[SQL Server 2012] Table de fait - persisted vs caclul dans l'etl

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
01/06/2015, 13h38
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
01/06/2015, 15h26
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).

Voyons par un exemple :

Code:

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:

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:

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:

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:

SELECT * FROM FAIT

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

=> GAIN de performances : x 3

CQFD !

A +

02/06/2015, 08h45
escartefigue

Citation:

Envoyé par StringBuilder

Il parle des colonnes calculées

oups en effet, autant pour moi :D
02/06/2015, 13h49
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.
02/06/2015, 15h47
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.
02/06/2015, 15h52
StringBuilder

Citation:

Envoyé par SQLpro

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

Code:

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:

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.