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 :

Optimisation de requête sous SQL Server


Sujet :

Développement SQL Server

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre régulier
    Femme Profil pro
    Référente Numérique Responsable
    Inscrit en
    Mai 2011
    Messages
    10
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Référente Numérique Responsable
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : Mai 2011
    Messages : 10
    Par défaut Optimisation de requête sous SQL Server
    Bonjour,

    Habituée d'Oracle, je découvre SQL-Server.
    J'ai un modèle en étoile avec un table de fait d'environ 20 millions d'enregistrements et une centaine de colonnes qui sont des id de tables de référence. Ces tables de référence contiennent entre 2 et 10 enregistrements.
    Je construit une requête du type :
    Requête 1 :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    Select TR1.libelle, 
             TR2.libelle, 
             count(distinct TF.PrimaryKeyId)
    FROM TF 
    INNER JOIN TR1 ON (TF.TR1_ID=TR1.TR1_ID)
    INNER JOIN TR2 ON (TF.TR2_ID=TR2.TR2_ID)
    WHERE TF.TR3_ID=1
    GROUP BY TR1.libelle, 
             TR2.libelle;
    Requête 2 :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    Select TR1.libelle, 
             TR2.libelle, 
             TR4.libelle, 
             count(distinct TF.PrimaryKeyId)
    FROM TF 
    INNER JOIN TR1 ON (TF.TR1_ID=TR1.TR1_ID)
    INNER JOIN TR2 ON (TF.TR2_ID=TR2.TR2_ID)
    INNER JOIN TR4 ON (TF.TR4_ID=TR4.TR4_ID)
    WHERE TF.TR3_ID=1
    GROUP BY TR1.libelle, 
             TR2.libelle, 
             TR4.libelle;
    A chaque libelle de table de référence que j'ajoute dans ma requête, il s'ajoute 2mn30 au temps d'exécution.
    Ainsi la requête 2 met 2mn30 de plus que la requête 1.

    Dans Oracle, l'ajout de libellés de tables de référence dans le SELECT n'a aucune influence sur les temps d'exécution ?! Donc je cherche à comprendre pourquoi c'est différent sous SQL-Server.

    J'ai testé la requête sans jointure et les performances sont bonnes.
    Ce sont donc les jointures avec les tables de référence de qqs lignes qui ralentissent fortement le temps d'exécution.
    Pouvez-vous m'expliquer pourquoi ? Et comment faire pour que ce ne soit pas le cas ?

    Avec mes remerciements,
    Pitchoonet

  2. #2
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    22 010
    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 : 22 010
    Billets dans le blog
    6
    Par défaut
    C'est un problématique de star join. Quelle version /édition de SQL Server ? (2005, 20088 enterpriss, standard.... ?
    base relationnelle ou décisionnelle. ???
    Évidemment ce serait mieux si c'était déjà un cube décisionnel !

    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/ * * * * *

  3. #3
    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 : 44
    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,

    J'ai un modèle en étoile avec un table de fait d'environ 20 millions d'enregistrements et une centaine de colonnes qui sont des id de tables de référence
    Vous confondez modèle relationnel et analytique.

    Dans Oracle, l'ajout de libellés de tables de référence dans le SELECT n'a aucune influence sur les temps d'exécution?! Donc je cherche à comprendre pourquoi c'est différent sous SQL Server.
    Ça me paraît un peu gros ...
    Je suppose que vous avez les mêmes structures de table, mais avez-vous exactement les mêmes index ?

    Si c'est le cas, il vous faut voir d'où l'activité disque provient, à l'aide de l'option de session SET STATISTICS IO ON :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    SET STATISTICS IO ON
    GO
     
    script ...
    Vous trouverez le nombre de pages lues par table dans l'onglet Messages de SQL Server Management Studio (SSMS) après exécution de la requête.

    Prenez aussi le plan de requête réel, qui vous décrira ce que fait SQL Server.
    Pour cela, il vous suffit d'activer son exposition :



    Il sera affiché dans un nouvel onglet nommé Execution Plan.
    Vous pouvez le sauvegarder en cliquant-droit sur celui-ci, et le poster ici ensuite (si cela vous est autorisé )

    @++

  4. #4
    Membre régulier
    Femme Profil pro
    Référente Numérique Responsable
    Inscrit en
    Mai 2011
    Messages
    10
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Référente Numérique Responsable
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : Mai 2011
    Messages : 10
    Par défaut A SQLPro
    Merci pour votre aide.

    Version :
    Microsoft SQL Server Enterprise Edition (64-bit) 2008 R2

    Base de données relationnelle

    Pitchoonet

  5. #5
    Candidat au Club
    Profil pro
    Inscrit en
    Novembre 2010
    Messages
    4
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Novembre 2010
    Messages : 4
    Par défaut
    bonjour,
    En plus de ce que vous a dit elsuket essayer de modifiant la requête comme ci-dessous, et comparer les plan d'executions avec votre requête d'origine.
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    Select 
    MIN(TR1.libelle) as TR1_Libelle, 
    MIN(TR2.libelle) as TR2_Libelle, 
    MIN(TR4.libelle) as TR4_Libelle, 
    count(distinct TF.PrimaryKeyId)
    FROM TF 
    INNER JOIN TR1 ON (TF.TR1_ID=TR1.TR1_ID)
    INNER JOIN TR2 ON (TF.TR2_ID=TR2.TR2_ID)
    INNER JOIN TR4 ON (TF.TR4_ID=TR4.TR4_ID)
    WHERE TF.TR3_ID=1
    GROUP BY 
    TF.TR1_ID, 
    TF.TR2_ID, 
    TF.TR4_ID;

  6. #6
    Membre régulier
    Femme Profil pro
    Référente Numérique Responsable
    Inscrit en
    Mai 2011
    Messages
    10
    Détails du profil
    Informations personnelles :
    Sexe : Femme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Référente Numérique Responsable
    Secteur : High Tech - Multimédia et Internet

    Informations forums :
    Inscription : Mai 2011
    Messages : 10
    Par défaut
    Grâce à votre aide, j'ai pu identifier 2 facteurs de ralentissement de ma requête :
    - le passage de 9 à 10 jointures avec des tables de référence : ma requête passe de 1mn30 à 8mn. J'ai essayer avec plusieurs jointures différentes afin d’identifier si le facteur discriminant était le nombre de jointures ou la volumétrie ramenée et j'obtiens donc 1mn30 d'exécution en ramenant 230 000 lignes et 8mn en ramenant 130 000 lignes.
    Svp, existe-t-il une limitation de ce type à votre connaissance?

    - l'utilisation d'un case dans ma fonction d'agrégation : count(distinct case when TF.TR_ID=1 then TF.PrimaryKeyId end)

    J'imagine que le second facteur discriminant doit pouvoir se résoudre avec des indexs.
    Svp, avez-vous des recos?

    Avec mes remerciements,
    Pitchoonet

  7. #7
    Modérateur
    Avatar de Waldar
    Homme Profil pro
    Sr. Specialist Solutions Architect @Databricks
    Inscrit en
    Septembre 2008
    Messages
    8 454
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 47
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Sr. Specialist Solutions Architect @Databricks
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Septembre 2008
    Messages : 8 454
    Par défaut
    Il faudrait voir la structure de façon un peu plus globale, déjà est-ce que la centaine de colonnes se justifie totalement dans la table de faits ?

    C'est beaucoup cent colonnes, j'espère que vous n'avez pas construit une matrice creuse pour coller au modèle en étoile.

    Votre table de faits est-elle construite avec une clef primaire clustered ou non-clustered ?
    Dans ce cas-ci, vu que l'accès aux données ne se fait pas sur la clef primaire, j'opterais pour une non-clustered, au contraire de toutes vos tables de références.

    Est-ce que vos colonnes de références sont toutes indexées ?
    Est-ce que vos relations sont correctement définies par des clefs étrangères ?
    Est-ce que vous avez essayé votre requête avec des index composites ?

    Si la requête que vous avez présenté au départ est représentative de votre vraie requête, le distinct dans le count est inutile.
    Pour le case à l'intérieur de celui-ci, je ne sais pas, ça dépend de vos données et de ce que vous essayez d'en extirper.

  8. #8
    Membre Expert Avatar de iberserk
    Homme Profil pro
    Architecte de base de données
    Inscrit en
    Novembre 2004
    Messages
    1 795
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 43
    Localisation : France, Gironde (Aquitaine)

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

    Informations forums :
    Inscription : Novembre 2004
    Messages : 1 795
    Par défaut
    le passage de 9 à 10 jointures avec des tables de référence : ma requête passe de 1mn30 à 8mn
    Postez le plan d’exécution...

    Sur le plan d’exécution estimé, le nombre de lignes qu'il estime traiter est'il cohérent avec le nombre réel de lignes?
    J'ai eu le soucis sur une requête avec de multiples LEFT JOIN... il manquait une statistiques sur un couple de colonnes... je suis ainsi passé de 5mn... à 3secondes :-)
    Un coups d'oeil de ce côté là ne fera pas de mal...

  9. #9
    Expert confirmé
    Avatar de mikedavem
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Août 2005
    Messages
    5 450
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 46
    Localisation : France, Ain (Rhône Alpes)

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Distribution

    Informations forums :
    Inscription : Août 2005
    Messages : 5 450
    Par défaut
    J'ai eu le soucis sur une requête avec de multiples LEFT JOIN... il manquait une statistiques sur un couple de colonnes... je suis ainsi passé de 5mn... à 3secondes :-)
    Un coups d'oeil de ce côté là ne fera pas de mal...
    Ah tiens ca me rappelle qqch

Discussions similaires

  1. Réponses: 4
    Dernier message: 17/01/2009, 22h34
  2. Performance d'une requête sous SQL Server 2005
    Par Menoto dans le forum Développement
    Réponses: 4
    Dernier message: 20/03/2008, 09h58
  3. Performance d'une requête sous SQL Server 2005
    Par Menoto dans le forum MS SQL Server
    Réponses: 4
    Dernier message: 20/03/2008, 09h58
  4. Requête analyse croisée sous SQL SERVER
    Par motus_z dans le forum MS SQL Server
    Réponses: 2
    Dernier message: 23/02/2006, 16h54
  5. Requêtes analyses croisées sous SQL Server 2000
    Par callo dans le forum MS SQL Server
    Réponses: 3
    Dernier message: 24/09/2005, 19h27

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