Discussion :

[Batou69]

Bonjour à tous,

Nous utilisons une colonne calculée dans l'une des tables de notre base. La définition de la table est :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
CREATE TABLE [CAB](
	[NO_CAB] [BIGINT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[DATE_CREATION] [DATETIME] NOT NULL,
	[TYPE_CAB] [TINYINT] NULL,
	[VALEUR] [VARCHAR](2000) NOT NULL,
	[CHECKSUM_VALEUR]  AS (CHECKSUM([VALEUR])),
 CONSTRAINT [PK_CAB_NO_CAB] PRIMARY KEY CLUSTERED 
(
	[NO_CAB] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

La colonne calculée n'est PAS marquée PERSITED. Nous avons mis un index sur cette colonne pour améliorer les recherches.

Code :

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

1
2
3
4
CREATE NONCLUSTERED INDEX [IX_CAB_CHECKSUM] ON [CAB]
(
	[CHECKSUM_VALEUR] ASC
)

Dans notre cas, que fait le moteur ? La colonne est elle stockée physiquement ou bien calculée à chaque accès ?
Est il utile de la marquer PERSITED ?

Merci pour votre aide

[SQLpro]

Vous l'avez créée en persistante sans le savoir du moment que vous avez mis l'index....

Notez :

Code :

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

1
2
 
SELECT COLUMNPROPERTY(OBJECT_ID('CAB'), 'CHECKSUM_VALEUR', 'IsIndexable')

A +

[Batou69]

Merci pour votre réponse !

La requete que vous me donnez retourne 1, mais si j'ai bien compris, c'est normal, l'index existe déjà sur la colonne.

Je retiens qu'il est inutile de mettre PERSISTED si on a créé un index sur une colonne calculé.

[aieeeuuuuu]

Vous l'avez créée en persistante sans le savoir du moment que vous avez mis l'index....

Non !

Les valeurs sont bien calculées et stockées dans l'index, mais cela n'en fait pas une colonne calculée persistante pour autant.

Pour une requête qui n'utilisera pas l'index, les valeurs seront recalculées.

[Batou69]

Aie !!

Y a t -il un moyen de "voir" le recalcul dans le plan d'éxecution d'un requete ?
ou bien dans les dmv ?

[aieeeuuuuu]

oui, vous devriez retrouver l'opération correspondante dans le plan d’exécution.

[Batou69]

Bon, après avoir regardé le plan d'execution, je ne comprend plus grand chose :
J'ai crée une table identique à la précedente mais avec la colonne calculée en PERSISTED :

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
CREATE TABLE CAB2
(
	[NO_CAB] [BIGINT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[DATE_CREATION] [DATETIME] NOT NULL,
	[TYPE_CAB] [TINYINT] NULL,
	[VALEUR] [VARCHAR](2000) NOT NULL,
	[CHECKSUM_VALEUR]  AS (CHECKSUM([VALEUR])) PERSISTED,
 CONSTRAINT [PK_CAB_NO_CAB] PRIMARY KEY CLUSTERED 
(
	[NO_CAB] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
 
GO
 
CREATE NONCLUSTERED INDEX [IX_CAB2_CHECKSUM] ON CAB2
(
	[CHECKSUM_VALEUR] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

puis j'ai mis des données dedans (20000 lignes) et fait les requetes suivantes :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT 
	NO_CAB, DATE_CREATION, TYPE_CAB, VALEUR, CHECKSUM_VALEUR 
FROM CAB2 
WHERE VALEUR = '250714402033980017'
GO
SELECT
	NO_CAB, DATE_CREATION, TYPE_CAB, VALEUR 
FROM CAB2 
WHERE VALEUR = '250714402033980017'

et j'obtiens bien la trace du re-calcul de la colonne même si elle est persistante ! Je le vois dans le plan d’exécution :



et la deuxieme ne fait pas le calcul :



Qu'en pensez vous ???

Edit : je suis sur SQL 2014 entreprise edition (12.0.2000.8)

[aieeeuuuuu]

Votre première requete ne fait pas de recalcul.

Lorsque vous faites appel à une colonne calculée quelle qu'elle soit, vous aurez toujours cette opération dans le plan, dont j'ignore par ailleurs la raison.

Si la colonne n'est pas persistante, alors vous aurez en plus une deuxième opération similaire (opérateur scalaire) faisant cette fois explicitement référence à la fonction définie dans la déclaration de la colonne calculée. C'est cette opération qui indique le calcul du résultat.

Cependant, il se peut qu'une colonne persistante soit recalculée lors de l’exécution d'une requete, par exemple pour éviter une lecture (l'optimiseur estimera, -parfois voire souvent à tort : cette estimation est biaisée par le faible cout qu'il attribue aux fonctions scalaires- qu'il sera moins couteux de refaire le calcul plutôt qu'une lecture pour obtenir la valeur de la colonne calculée)

[Batou69]

En effet, sur une table sans persistance j'ai deux fois l'opérateur,
mais je remarque aussi que dans ce cas, il retourne l'intégralité de la table, effectue les operateurs, puis fait le filtre de la clause where !!



étrange.....

[elsuket]

Bonjour,

Persister une colonne calculée permet de ne pas avoir à calculer cette valeur à chaque lecture de cette colonne.
C'est utile lorsque une bonne part des requêtes référençant ladite table référencent aussi cette colonne, mais ne filtrent pas par cette colonne.
Dans ce cas, la valeur calculée est stockée dans les pages de la table, et est mise à jour dès que les colonnes utilisées dans sa formule sont mises à jour.

Si en revanche la plupart des requêtes référençant cette table filtrent et sélectionnent par celle-ci, alors l'index seul est très utile.
Dans ce cas-là, les valeurs de la colonne calculée seront dans les pages de l'index, mais pas dans les pages de la table.
On peut également placer un index couvrant, en référençant la colonne calculée et d'autres colonnes de la table.
Ceci me semble utile dans votre cas, et expliquerait pourquoi on a le calcul scalaire en plus dans le premier plan d'exécution.
Les requêtes que vous donnez sont-elles représentatives de la charge de travail que cette table va subir ?

Je n'ai jamais eu besoin de persister et indexer une colonne calculée.
Peut-être existe-t-il des cas particuliers de charge de travail qui requièrent les deux.

@++

[mikedavem]

Lorsque vous faites appel à une colonne calculée quelle qu'elle soit, vous aurez toujours cette opération dans le plan, dont j'ignore par ailleurs la raison.

By design. Une colonne définie en tant que colonne calculée sera toujours associée à un opérateur scalaire. Si la colonne est persistée alors l'expression de l'opérateur référera directement le nom de la colonne (pas de recalcule de la fonction). A noter également que l'optimiseur peut décider de réévaluer la colonne calculée en fonction du coût estimé.

mais je remarque aussi que dans ce cas, il retourne l'intégralité de la table, effectue les operateurs, puis fait le filtre de la clause where !!

Si j'ai bien compris ta requête ceci est normal puisque tu filtres sur la colonne valeur qui n'est pas indexée dans ton cas.

Code :

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

1
2
3
4
5
SELECT 
	NO_CAB, DATE_CREATION, TYPE_CAB, VALEUR, CHECKSUM_VALEUR 
FROM CAB2 
WHERE VALEUR = '250714402033980017'
GO

Tu ne peux avoir qu'un index scan (notamment la clé cluster de ta table qui est ta table au final). De plus puisque ta colonne n'étant pas persistée, l'optimiseur va devoir calculer toutes les valeurs VALEUR_CHECKSUM (d'où les 2 opérateurs scalaires de ton plan - 1 pour le calcul du checksum : Instrinsic > Functioname : checksum > scalarOperator = checksum([AdventureWorks2012].[dbo].[CAB3].[VALEUR]) > Column referenced : [AdventureWorks2012].[dbo].[CAB3].VALEUR et le 2ème by design qui référence directement le nom de ta colonne sans recalcul : Identifier : [AdventureWorks2012].[dbo].[CAB3].[CHECKSUM_VALEUR] > column : CHECKSUM_VALEUR > Computed column : True). Le filtre ne peut donc arriver qu'en fin de plan dans ce cas d'où ton dernier opérateur correspond.

++

[Batou69]

Merci pour vos réponses. J'y vois plus clair.

Mais pour bien préciser où je veux en venir, il faut que je précise l'utilisation de cette table :

Cette table contient des valeurs "chaine de caractères" dont je ne connais pas la longueur à priori. Etant donné que le logiciel doit pouvoir s'adapter au plus grand nombre de cas de figure, j'ai dimensionné la chaine à 2000 octets.
Mais le logiciel effectue beaucoup de recherche sur la valeur, et on ne peut pas indexer un varchar(2000). On a donc ajouté la colonne calculée Checksum à la table et on index cette colonne. Pour l'instant la colonne calculée n'est pas PERSISTED.

Les recherches dans cette table, qui contient facilement 2 à 3 millions de lignes doivent impérativement s'effectuer à la fois sur le checksum (colonne indexée) ET sur la valeur. La raison est que le checksum SQL n'est pas unique et deux valeurs de chaine peuvent donner la même valeur de checksum.
Cette façon de faire fonctionne plutôt pas mal pour l'instant

Donc pour répondre à Elsuket :

Bonjour,
Si en revanche la plupart des requêtes référençant cette table filtrent et sélectionnent par celle-ci, alors l'index seul est très utile.
Dans ce cas-là, les valeurs de la colonne calculée seront dans les pages de l'index, mais pas dans les pages de la table.
On peut également placer un index couvrant, en référençant la colonne calculée et d'autres colonnes de la table.
Ceci me semble utile dans votre cas, et expliquerait pourquoi on a le calcul scalaire en plus dans le premier plan d'exécution.
Les requêtes que vous donnez sont-elles représentatives de la charge de travail que cette table va subir ?

Je n'ai jamais eu besoin de persister et indexer une colonne calculée.
Peut-être existe-t-il des cas particuliers de charge de travail qui requièrent les deux.
@++

Les requêtes que nous faisons sur cette table utilisent toujours le checksum... Je suis très étonné que vous n'ayez jamais eu le cas de figure...
La question principale de mon topic est : dans mon cas, est il nécessaire de rendre la colonne PERSISTED afin d'améliorer encore les perfs ???

Tu ne peux avoir qu'un index scan (notamment la clé cluster de ta table qui est ta table au final). De plus puisque ta colonne n'étant pas persistée, l'optimiseur va devoir calculer toutes les valeurs VALEUR_CHECKSUM (d'où les 2 opérateurs scalaires de ton plan - 1 pour le calcul du checksum : Instrinsic > Functioname : checksum > scalarOperator = checksum([AdventureWorks2012].[dbo].[CAB3].[VALEUR]) > Column referenced : [AdventureWorks2012].[dbo].[CAB3].VALEUR et le 2ème by design qui référence directement le nom de ta colonne sans recalcul : Identifier : [AdventureWorks2012].[dbo].[CAB3].[CHECKSUM_VALEUR] > column : CHECKSUM_VALEUR > Computed column : True). Le filtre ne peut donc arriver qu'en fin de plan dans ce cas d'où ton dernier opérateur correspond.
++

Super explication !! Mais comment faites vous pour avoir cette sortie ? J'ai bien essaye les traceflags 3604 et 8605 amis aucun ne me donnent ce que vous avez....

[SQLpro]

Dans ce cas, la meilleurs solution est à mon avis une colonne calculée persistante et un index comportant cette valeur en clef et la colonne en include.

A +