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

MS SQL Server Discussion :

Problème TOP 1 [2008R2]


Sujet :

MS SQL Server

  1. #21
    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 : 42
    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
    Points : 12 371
    Points
    12 371
    Par défaut
    Absolument : c'est pour ça que nous avons besoin de savoir ce qu'avait prévu l'optimiseur de requêtes, et de le comparer à la façon dont ça s'est réellement exécuté

  2. #22
    Expert éminent
    Avatar de berceker united
    Profil pro
    SQL
    Inscrit en
    Février 2005
    Messages
    3 487
    Détails du profil
    Informations personnelles :
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : SQL
    Secteur : Finance

    Informations forums :
    Inscription : Février 2005
    Messages : 3 487
    Points : 6 030
    Points
    6 030
    Par défaut
    C'est bien gentil cette discussion mais ça en est ou maintenant ? Merci
    Mon avatar ? Ce n'est rien, c'est juste la tête que je fais lorsque je vois un code complètement frappa dingue !...

  3. #23
    Rédacteur
    Avatar de imikado
    Homme Profil pro
    Développeur informatique
    Inscrit en
    Décembre 2006
    Messages
    5 239
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Finance

    Informations forums :
    Inscription : Décembre 2006
    Messages : 5 239
    Points : 19 100
    Points
    19 100
    Billets dans le blog
    17
    Par défaut
    Désolé, mais j'ai pas eu le temps de m'en occuper, d'autant que j'ai changé de machine entre temps (donc il faut que je reinstalle les différents outils)

    Mais ça reste quand même bizarre comme comportement
    Framework php sécurisé et simple à prendre en main avec générateur web http://mkframework.com/ (hebergé sur developpez.com)
    Mes cours/tutoriaux

  4. #24
    Expert éminent
    Avatar de berceker united
    Profil pro
    SQL
    Inscrit en
    Février 2005
    Messages
    3 487
    Détails du profil
    Informations personnelles :
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : SQL
    Secteur : Finance

    Informations forums :
    Inscription : Février 2005
    Messages : 3 487
    Points : 6 030
    Points
    6 030
    Par défaut
    Oui j'explique pas trop mais en effet, seul le schéma du plan d'exécution peut nous éclairer. Derrière le cas du TOP x dont personnellement je l'utilise pour des requêtes à chaud ça peut, éventuellement, être utile dans des cas spécifiques et optimiser le temps d'exécution si nous comprenons le mécanisme.
    Mon avatar ? Ce n'est rien, c'est juste la tête que je fais lorsque je vois un code complètement frappa dingue !...

  5. #25
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Points : 13 092
    Points
    13 092
    Par défaut
    Bizarre, pas vraiment : le moteur SQL s'appuie sur des statistiques pour trouver le meilleur plan possible. Et les statistiques... ce sont des statistiques !

    La réalité est parfois très différente que ce qu'indiquent les statistiques !

    Prenons un exemple :
    1/ créons une table T1, avec un clef primaire et une colonne num, contenant les nombre de 1 à 10 000 :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
     
    CREATE  TABLE T1(
        i INT NOT NULL PRIMARY KEY IDENTITY
        ,num INT NOT NULL
     
    )
    GO
    INSERT INTO T1(num)
        SELECT TOP(10000) ROW_NUMBER() OVER(ORDER BY (SELECT 1))
        FROM sys.objects A, sys.objects B, sys.objects C
    2/ créons une table T2, référençant la table T1, avec une ligne pour les num de 5000 à 10 000 :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
     
     
    CREATE TABLE  T2 (
        j INT NOT NULL PRIMARY KEY IDENTITY
        ,t1_i INT NOT NULL FOREIGN KEY REFERENCES t1(i)
     
    )
    GO
    INSERT INTO T2(t1_i)
        SELECT i
        FROM T1
        WHERE num > 5000
    3/ créons un index sur T1(num)
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    CREATE INDEX IX_T1_num ON T1(num)
    4/ exécutons une requête avec TOP(1), ordonnée selon l'index :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
    SELECT TOP(1) t1.i
    FROM T1
    INNER JOIN T2
        ON T2.T1_i = T1.i
    ORDER BY T1.num
    Table 'T2'. Nombre d'analyses 1, lectures logiques 110003, ...
    Table 'T1'. Nombre d'analyses 1, lectures logiques 9, ...
    (1 ligne(s) affectée(s))

    SQL Server \endash Temps d'exécution :
    , Temps UC = 2792 ms, temps écoulé = 2910 ms.
    5/ puis la même requête avec une variable :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
     
     
    DECLARE @x INT = 1;
     
    SELECT TOP(@x) t1.i
    FROM T1
    INNER JOIN T2
        ON T2.T1_i = T1.i
    ORDER BY T1.num
    Table 'Worktable'. Nombre d'analyses 0, lectures logiques 0,...
    Table 'T1'. Nombre d'analyses 1, lectures logiques 16, lectures ...
    Table 'T2'. Nombre d'analyses 1, lectures logiques 22, lectures ...
    (1 ligne(s) affectée(s))

    SQL Server \endash Temps d'exécution :
    , Temps UC = 16 ms, temps écoulé = 52 ms.
    Que s'est-il passé ?

    Pour la requête TOP(1), SQL server utilise l'index. D'après les statistiques, la jointure entre T1 et T2 donnera un résultat pour une ligne sur deux de T1. L'utilisation de l'index semble donc en effet intéressante, malgré la boucle imbriquée qu'elle engendre pour trouver la correspondance dans T2.
    Mais dans la pratique, s'est en fait contre performant, car SQL Server doit traiter les 5001 premières lignes de l'index sur T1 avant de trouver une ligne correspondante dans T2. On est très loin des deux lignes qui avaient été estimées.

    Mais alors pourquoi la requête avec TOP(@x) est-elle plus rapide ?
    Lors de la compilation de la requête, la valeur de @x n'est pas connue de SQL Server. Il construit donc un plan de requête générique, pour lequel il estime arbitrairement que @x vaudra 100. SQL Server estime donc qu'il lui faudrait traiter 200 lignes de l'index s'il utilisait le même type de plan que pour la première requête. Il estime alors que la boucle imbriquée serait trop couteuse (ce qui se passe d'ailleurs en réalité dans la requête 1) et préfére effectuer directement la jointure complète, puis un tri afin de ne prendre que les 100 premières lignes (une seule au final lors de l’exécution puisque @x vaut en fait 1).

    Ici, on ne peut donc pas vraiment dire que le plan généré pour la requête top(1) soit mauvais en soit, c'est juste la répartition des données qui engendre ce phénomène. D'ailleurs, si on met des données non pas pour les 5000 dernières valeurs de num, mais pour les valeurs paires afin d'avoir une répartition plus homogène, la requête avec TOP(1) devient en effet plus efficace que la requête avec TOP(@x) :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
     
    DELETE FROM T2
     
    INSERT INTO T2(t1_i) 
        SELECT i
        FROM T1
        WHERE num%2 = 0
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
    SELECT TOP(1) t1.i
    FROM T1
    INNER JOIN T2 
        ON T2.T1_i = T1.i
    ORDER BY T1.num
    Table 'T2'. Nombre d'analyses 1, lectures logiques 27,...
    Table 'T1'. Nombre d'analyses 1, lectures logiques 2,...
    (1 ligne(s) affectée(s))

    SQL Server \endash Temps d'exécution :
    , Temps UC = 0 ms, temps écoulé = 1 ms.
    Pour conclure, l'origine de ce comportement vient du fait que même si vos requêtes sont strictement équivalentes au moment de l'éxécution quand @x vaut 1, elles donnent lieux chacune à la génération d'un plan d’exécution propre, et que dans le cas du plan avec @x, SQL server lui attribue par défaut la valeur 100. Si vous le forcez à estimer que @x = 1, en ajoutant l'option OPTION(OPTIMIZE FOR(@x=1)) à la fin de votre requête, vous devriez retomber sur le même plan d’exécution que pour la requête TOP(1) et donc retrouver la même lenteur... ce qui n'est certes pas le but recherché

  6. #26
    Rédacteur
    Avatar de imikado
    Homme Profil pro
    Développeur informatique
    Inscrit en
    Décembre 2006
    Messages
    5 239
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 42
    Localisation : France, Val de Marne (Île de France)

    Informations professionnelles :
    Activité : Développeur informatique
    Secteur : Finance

    Informations forums :
    Inscription : Décembre 2006
    Messages : 5 239
    Points : 19 100
    Points
    19 100
    Billets dans le blog
    17
    Par défaut
    Merci beaucoup pour cette réponse, elle est pertinente même si embêtante
    Framework php sécurisé et simple à prendre en main avec générateur web http://mkframework.com/ (hebergé sur developpez.com)
    Mes cours/tutoriaux

  7. #27
    Modérateur

    Profil pro
    dba
    Inscrit en
    Janvier 2010
    Messages
    5 643
    Détails du profil
    Informations personnelles :
    Localisation : France, Rhône (Rhône Alpes)

    Informations professionnelles :
    Activité : dba

    Informations forums :
    Inscription : Janvier 2010
    Messages : 5 643
    Points : 13 092
    Points
    13 092
    Par défaut
    Ce n'est pas forcément embêtant.
    Si votre "problème" est bien similaire a celui-ci (ce que vos plans d'éxécution pourraient confirmer), alors il existe plusieurs solutions pour le contourner, dont certaines pourraient être même très avantageuses.

    Par exemple, si vous savez que cette requête sera exécutée souvent, et que vous voulez de bonnes performances, vous pouvez créer une vue et l'indexer :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
     
    CREATE VIEW V_T1T2
    WITH SCHEMABINDING
    AS
        SELECT T1.i, T1.num
        FROM dbo.T1
        INNER JOIN dbo.T2 
        ON T2.T1_i = T1.i
    GO
     
    CREATE UNIQUE CLUSTERED INDEX IX_VT1T2 ON dbo.V_T1T2(num,i)
    GO
    SQL server dispoe alors d'un nouvel index, qu'il ne se privera pas d'utiliser dans la requête TOP(1) :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
     
    SELECT TOP(1) t1.i
    FROM T1
    INNER JOIN T2 
        ON T2.T1_i = T1.i
    ORDER BY T1.num
    Table 'V_T1T2'. Nombre d'analyses 1, lectures logiques 2, ...
    (1 ligne(s) affectée(s))

    SQL Server \endash Temps d'exécution :
    , Temps UC = 0 ms, temps écoulé = 0 ms.
    Bien sûr, si T1 et/ou T2 est mise à jour très régulièrement, cette vue indexée pourrait aussi avoir un impact négatif... Il faut donc bien connaitre le contexte avant de choisir la solution la plus adaptée.

    EDIT : Avec une édition entreprise, SQL server saura utiliser l'index de la vue même si la requête ne fait pas explicitement référence à la vue. En revanche, il me semble qu'avec une édition express, il faudra modifier la requête... mais le résultat sera le même !

  8. #28
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 966
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 966
    Points : 30 778
    Points
    30 778
    Billets dans le blog
    16
    Par défaut
    Bonsoir,


    Juste une petite question.


    Citation Envoyé par aieeeuuuuu Voir le message
    Et les statistiques... ce sont des statistiques !
    C’est bien vrai ! Mais à l’instar de DB2 for z/OS, y at-il des tables du catalogue relationnel où certaines colonnes (dédiées aux statistiques) peuvent être mises à jour manuellement ?

    Du temps (bien lointain) où je prototypais les performances des requêtes, ça me rendait d’immenses services, puisqu’avant que ne commencent les développements applicatifs, je pouvais valoriser ces colonnes, simuler des volumétries de N millions de lignes pour des tables en réalité vides, et connaître ainsi de façon anticipée la stratégie de l’optimiseur pour les requêtes.

    Merci,

    François
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

  9. #29
    Expert éminent sénior
    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 : 45
    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
    Points : 12 891
    Points
    12 891
    Par défaut
    C’est bien vrai ! Mais à l’instar de DB2 for z/OS, y at-il des tables du catalogue relationnel où certaines colonnes (dédiées aux statistiques) peuvent être mises à jour manuellement ?
    Du temps (bien lointain) où je prototypais les performances des requêtes, ça me rendait d’immenses services, puisqu’avant que ne commencent les développements applicatifs, je pouvais valoriser ces colonnes, simuler des volumétries de N millions de lignes pour des tables en réalité vides, et connaître ainsi de façon anticipée la stratégie de l’optimiseur pour les requêtes.
    Hello François,

    Oui il est possible de valoriser certaines colonnes sans même avoir de données représentatives en jouant avec les statistiques de SQL Server. Pour cela il faut utiliser UPDATE STATISTICS et les options ROWCOUNT et PAGECOUNT.

    ++

  10. #30
    Expert éminent sénior
    Avatar de fsmrel
    Homme Profil pro
    Spécialiste en bases de données
    Inscrit en
    Septembre 2006
    Messages
    7 966
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Essonne (Île de France)

    Informations professionnelles :
    Activité : Spécialiste en bases de données
    Secteur : Conseil

    Informations forums :
    Inscription : Septembre 2006
    Messages : 7 966
    Points : 30 778
    Points
    30 778
    Billets dans le blog
    16
    Par défaut
    Bonjour David,


    Merci à toi.


    Il faudra que j'aille faire mumuse avec ça.


    A la prochaine !
    (a) Faites simple, mais pas plus simple ! (A. Einstein)
    (b) Certes, E=mc², mais si on discute un peu, on peut l’avoir pour beaucoup moins cher... (G. Lacroix, « Les Euphorismes de Grégoire »)
    => La relativité n'existerait donc que relativement aux relativistes (Jean Eisenstaedt, « Einstein et la relativité générale »)

    __________________________________
    Bases de données relationnelles et normalisation : de la première à la sixième forme normale
    Modéliser les données avec MySQL Workbench
    Je ne réponds pas aux questions techniques par MP. Les forums sont là pour ça.

+ Répondre à la discussion
Cette discussion est résolue.
Page 2 sur 2 PremièrePremière 12

Discussions similaires

  1. Problème TOP SQL Server 2000
    Par mino26tz dans le forum MS SQL Server
    Réponses: 29
    Dernier message: 21/12/2010, 05h53
  2. [AC-2003] Problème TOP et ORDER BY
    Par Nheil dans le forum Requêtes et SQL.
    Réponses: 12
    Dernier message: 21/04/2010, 09h40
  3. Réponses: 4
    Dernier message: 28/06/2007, 09h49
  4. Problème avec top
    Par byloute dans le forum Debian
    Réponses: 3
    Dernier message: 05/01/2007, 15h52
  5. Problème avec select top
    Par franculo_caoulene dans le forum MS SQL Server
    Réponses: 8
    Dernier message: 10/12/2004, 15h55

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