IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

VUE ET INDEX


Sujet :

Développement SQL Server

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre éclairé Avatar de JmL40
    Profil pro
    Inscrit en
    Mai 2007
    Messages
    348
    Détails du profil
    Informations personnelles :
    Âge : 39
    Localisation : France

    Informations forums :
    Inscription : Mai 2007
    Messages : 348
    Par défaut VUE ET INDEX
    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

  2. #2
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Par défaut
    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 )

    @++

  3. #3
    Membre éclairé Avatar de JmL40
    Profil pro
    Inscrit en
    Mai 2007
    Messages
    348
    Détails du profil
    Informations personnelles :
    Âge : 39
    Localisation : France

    Informations forums :
    Inscription : Mai 2007
    Messages : 348
    Par défaut
    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

  4. #4
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 998
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 998
    Billets dans le blog
    6
    Par défaut
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  5. #5
    Modérateur

    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2005
    Messages
    5 826
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Haute Garonne (Midi Pyrénées)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Janvier 2005
    Messages : 5 826
    Par défaut
    Ou une colonne calculée dans la table sous-jacente ?

    @++

Discussions similaires

  1. vue et index
    Par Helau dans le forum PostgreSQL
    Réponses: 4
    Dernier message: 10/04/2008, 15h08
  2. Vues et Index
    Par Antoun dans le forum Administration
    Réponses: 4
    Dernier message: 05/12/2007, 09h59
  3. [Oracle 9] Vue materialisée indexée et optimisation
    Par scornille dans le forum Oracle
    Réponses: 4
    Dernier message: 18/11/2005, 15h11
  4. [9i] Vue, Performances et Index
    Par nuke_y dans le forum Oracle
    Réponses: 5
    Dernier message: 27/06/2005, 10h47
  5. [DB2] Question sur les index et les vues
    Par ahoyeau dans le forum DB2
    Réponses: 1
    Dernier message: 14/03/2005, 08h30

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo