Discussion :

[JmL40]

Bonjour,

Je souhaiterai mettre en place un index sur une vue et j'avoue que je suis complétement bloqué. J'ai une table qui contient plusieurs millions d'enregistrements et pour éviter de surcharger l'application associée, j'utilise une vue avec INDEX pour récupérer des données.

Ma table source possède la structure suivante :

TABLE : DATA (INDIVIDU_1,INDIVIDU_2,MARQUEUR,NOM_SET,HETEROZYGOTE_1,HETEROZYGOTE_2,COMMUN_CM,DIFFERENT_CM,COMMUN_PM,DIFFERENT_CM)

Les clés primaires de la table DATA sont :

INDIVIDU_1,INDIVIDU_2,MARQUEUR,NOM_SET

Je souhaite donc mettre en place une vue qui permet de calculer des SUM et COUNT de la table principale. Voici la syntaxe de ma vue :

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 VIEW RESULTS WITH SCHEMABINDING AS
SELECT 
	COUNT_BIG(*) AS COUNTER,
	INDIVIDU_1,
	INDIVIDU_2,
	NOM_SET,
	COUNT_BIG(ISNULL(MARQUEUR,'0')) AS MARQUEUR, 	
	SUM(ISNULL(HETEROZYGOTE_1,'0')) AS HETEROZYGOTE_1,
	SUM(ISNULL(HETEROZYGOTE_2,'0')) AS HETEROZYGOTE_2, 
	SUM(ISNULL(COMMUN_CM,'0')) AS COMMUN_CM, 
	SUM(ISNULL(DIFFERENT_CM,'0')) AS DIFFERENT_CM,                     
	SUM(ISNULL(COMMUN_PM,'0')) AS COMMUN_PM, 
	SUM(ISNULL(DIFFERENT_PM,'0')) AS DIFFERENT_PM,
	CASE WHEN (CAST(SUM(ISNULL(COMMUN_CM,'0')) AS FLOAT) + CAST(SUM(ISNULL(DIFFERENT_CM,'0')) AS FLOAT)) * 100 = 0 THEN '0' ELSE ROUND((CAST(SUM(ISNULL(COMMUN_CM,'0')) AS FLOAT) / (CAST(SUM(ISNULL(COMMUN_CM,'0')) AS FLOAT) + CAST(SUM(ISNULL(DIFFERENT_CM,'0')) AS FLOAT)) * 100) / 2, 2) END AS DISTANCE_CM
FROM dbo.SYS_ANALYSE_DATA
GROUP BY 
	INDIVIDU_1,
	INDIVIDU_2,
	NOM_SET

Et voici la création de mon index sur INDIVIDU_1, INDIVIDU_2 et NOM_SET :

Code :

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

1
2
 
CREATE UNIQUE CLUSTERED INDEX TEST ON dbo.TESTING (INDIVIDU_1,INDIVIDU_2,NOM_SET)

Lors de la création, SQL renvoie une erreur du type :

"la liste de sélection de la vue contient une expression sur le résultat de la fonction d'agrégation ou de la colonne de regroupement."

Ma table DATA contient N lignes pour la clé primaire avec des valeurs égale à 0 ou 1 dans les colonnes COMMUN, DIFFERENT ... Je souhaiterai donc comptabilisé le nombre de valeur pour les colonnes COMMUN et DIFFERENT pour chaque combinaisons INDIVIDU_1, INDIVIDU_2 et pour un SET.

Je résous bien le problème en ajoutant dans ma clause GROUP BY de la vue la colonne MARQUEUR, mais dans ce cas les SUM des colonnes sont fausses.

Si une personne peut m'aider, ca sera avec plaisir ...

Cordialement

[elsuket]

Bonjour,

Les clés primaires de la table DATA sont :

INDIVIDU_1,INDIVIDU_2,MARQUEUR,NOM_SET

[...]

Code :

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

CREATE UNIQUE CLUSTERED INDEX TEST ON dbo.TESTING (INDIVIDU_1,INDIVIDU_2,NOM_SET)

Pas sûr que vous ayez intérêt à ré-indexer les mêmes colonnes.
Dans le cas d'un INSERT par exemple, ce sont au moins deux-index à maintenir (les deux index cluster).
Comme il est fort probable que vous ayez des index non-cluster sur la table et la vue, ça fait pas mal de boulot pour une ligne
Peut-être avez-vous intérêt à ne pas indexer votre vue.

Cela dit ce n'est pas la source de l'erreur (j'essaie de comprendre le message )

@++

[JmL40]

Bonjour,

Merci pour votre réponse.

En résumé, comme ma table source est déjà indexé, je n'ai éventuellement pas l'obligation d'indexé ma vue, c'est bien ca ?

Pour compléter mon problème, si je ne rajoute pas la colonne MARQUEUR dans mon GROUP BY lors de la création de la vue, il génère l'erreur. Sauf, que je ne souhaite pas faire un GROUP BY par marqueur, car à ce moment la, les colonnes d'agrégation SUM et COUNT me retourne 1 au lieu d'une somme ou d'une quantité.

Je viens de travailler une nouvelle fois sur ma table pour optimisation et pour l'instant, les performances sont relativement correctes .

Cordialement

[SQLpro]

C'est cette expression là qui n'est pas supportée :

Code :

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

CASE WHEN (CAST(SUM(ISNULL(COMMUN_CM,'0')) AS FLOAT) + CAST(SUM(ISNULL(DIFFERENT_CM,'0')) AS FLOAT)) * 100 = 0 THEN '0' ELSE ROUND((CAST(SUM(ISNULL(COMMUN_CM,'0')) AS FLOAT) / (CAST(SUM(ISNULL(COMMUN_CM,'0')) AS FLOAT) + CAST(SUM(ISNULL(DIFFERENT_CM,'0')) AS FLOAT)) * 100) / 2, 2) END

Lisez bien le message d'erreur. Il est clair :
la liste de sélection de la vue contient une expression sur le résultat de la fonction d'agrégation ou de la colonne de regroupement.

• Votre expression CASE / * + ....
• Votre agrégation : SUM, SUM, SUM...

Supprimez cette expression et réalisez là si possible sur une vue secondaire utilisant la vue indexée !

A +

[elsuket]

Ou une colonne calculée dans la table sous-jacente ?

@++