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

Adaptive Server Enterprise Sybase Discussion :

[T-SQL] Optimisation de proc


Sujet :

Adaptive Server Enterprise Sybase

  1. #1
    Membre à l'essai
    Inscrit en
    Juin 2006
    Messages
    27
    Détails du profil
    Informations forums :
    Inscription : Juin 2006
    Messages : 27
    Points : 18
    Points
    18
    Par défaut [T-SQL] Optimisation de proc
    Bonjour,

    Je rencontre de gros problèmes de performances avec une de mes procs.
    Voilà résumé le problème :
    Le gros de cette proc est d'insérer dans une table via un curseur.
    Comme vous pouvez le voir plus bas, une jointure a été supprimée (AND ACTNAIRE_NOMI.CD_VALEUR = @CD_VALEUR). Cette jointure apparaissait à plusieurs endroits de la proc, et depuis les performances ont chuté.
    Il n'y avait pas d'index portant sur cette colonne mais pourtant les perf étaient bonnes. Mais depuis qu'on a supprimé cette jointure, sans toucher aux indexs, les perfs ont chuté.

    Pouvez-vous m'aider ? Merci

    Pour les courageux je peux envoyer le script complet de la proc

    Exemple d'un bout de la proc :
    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
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
     
    DECLARE cur_nomi CURSOR
    FOR
    SELECT  ID_NOMINATIF,
            ID_TYPE_PROP,
            ID_CIVIL,
            NOM,
            PRENOM,
            BATIMENT,
            RUE,
            COMPLEMENT,
            CODE_POSTAL,
            VILLE,
            PAYS,
            NB_ACT_VS,
            NB_ACT_VP,
            REF_SOCIETE,
            REF_SALARIE,
            CD_REG_COU,
            CD_SS_REG_COU,
            CD_VALEUR,
            ADRE_PAYS_IDEN,
            IND_BLOCAGE,
            LANG
     
    FROM    NOMINATIF_TRANSIT
            WHERE ID_ASS_GEN  = @ID_ASS_GEN
              AND CD_COMPUTER = @CD_COMPUTER
              AND CD_USER     = @CD_USER
     
     
    open cur_nomi
    fetch cur_nomi into @ID_NOMINATIF,
                        @ID_TYPE_PROP,
                        @ID_CIVIL,
                        @NOM,
                        @PRENOM,
                        @BATIMENT,
                        @RUE,
                        @COMPLEMENT,
                        @CODE_POSTAL,
                        @VILLE,
                        @PAYS,
                        @NB_ACT_VS,
                        @NB_ACT_VP,
                        @REF_SOCIETE,
                        @REF_SALARIE,
                        @CD_REG_COU,
                        @CD_SS_REG_COU,
                        @CD_VALEUR,
                        @ADRE_PAYS_IDEN,
                        @IND_BLOCAGE,
                        @LANG
     
     
    /* Boucle sur les lignes */
    WHILE @@sqlstatus = 0
    BEGIN
     
       /* Recherche dans la base des infos existantes concernant cet actionnaire */
       SELECT   @ID_ACTNAIRE      = ACTNAIRE.ID_ACTNAIRE,
                @ID_ACTNAIRE_NOMI = ACTNAIRE_NOMI.ID_ACTNAIRE,
                @NB_ACT_VS_OLD    = ACTNAIRE_NOMI.NB_ACT_VS,
                @NB_ACT_VP_OLD    = ACTNAIRE_NOMI.NB_ACT_VP
     
       FROM   ACTNAIRE, ACTNAIRE_NOMI
     
       WHERE ACTNAIRE.ID_ASS_GEN        =  @ID_ASS_GEN
       AND   ACTNAIRE.ID_NOMINATIF      =  @ID_NOMINATIF
       AND   ACTNAIRE_NOMI.ID_ASS_GEN   =  @ID_ASS_GEN
       AND   ACTNAIRE_NOMI.ID_ACTNAIRE  =  ACTNAIRE.ID_ACTNAIRE
       AND   ACTNAIRE_NOMI.ID_TYPE_PROP =  @ID_TYPE_PROP
       -- Supression du contrôle sur le code valeur
       -- AND   ACTNAIRE_NOMI.CD_VALEUR    =  @CD_VALEUR

  2. #2
    Membre chevronné

    Profil pro
    Inscrit en
    Janvier 2006
    Messages
    1 307
    Détails du profil
    Informations personnelles :
    Âge : 64
    Localisation : Suisse

    Informations forums :
    Inscription : Janvier 2006
    Messages : 1 307
    Points : 1 828
    Points
    1 828
    Par défaut
    Il faudrait voir le SHOWPLAN ainsi que la définition des indexes sur les deux tables pour voir ce qui ce passe.

    Michael
    Michael Peppler
    Membre de TeamSybase - www.teamsybase.com

    "A successful [software] tool is one that was used to do something undreamed of by its author." -- S. C. Johnson

  3. #3
    Membre à l'essai
    Inscrit en
    Juin 2006
    Messages
    27
    Détails du profil
    Informations forums :
    Inscription : Juin 2006
    Messages : 27
    Points : 18
    Points
    18
    Par défaut
    J'ai justement un problème avec le showplan.

    J'essaie de l'obtenir dans SQL Advantage et j'ai le message suivant quand j'execute le script de ma proc :

    DECLARE CURSOR must be the only statement in a query batch.

    Et du coup je n'arrive pas à avoir mon plan... Que faire ?

  4. #4
    Membre chevronné

    Profil pro
    Inscrit en
    Janvier 2006
    Messages
    1 307
    Détails du profil
    Informations personnelles :
    Âge : 64
    Localisation : Suisse

    Informations forums :
    Inscription : Janvier 2006
    Messages : 1 307
    Points : 1 828
    Points
    1 828
    Par défaut
    Soit il faut exécuter la proc elle-même avec le SHOWPLAN, soit il faut splitter le code en deux partie et executer le "DECLARE ... CURSOR" séparément. Dans isql cela fait:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
     
    set showplan on
    set statistics io on
    go
    declare ... cursor for select ...
    go
    ... reste du code
    go
    Michael
    Michael Peppler
    Membre de TeamSybase - www.teamsybase.com

    "A successful [software] tool is one that was used to do something undreamed of by its author." -- S. C. Johnson

  5. #5
    Membre à l'essai
    Inscrit en
    Juin 2006
    Messages
    27
    Détails du profil
    Informations forums :
    Inscription : Juin 2006
    Messages : 27
    Points : 18
    Points
    18
    Par défaut
    Citation Envoyé par mpeppler
    Soit il faut exécuter la proc elle-même avec le SHOWPLAN, soit il faut splitter le code en deux partie et executer le "DECLARE ... CURSOR" séparément. Dans isql cela fait:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
     
    set showplan on
    set statistics io on
    go
    declare ... cursor for select ...
    go
    ... reste du code
    go
    Michael
    Ca ne marche toujours pas
    Voila à quoi ressemble mon script :


    set showplan on
    set statistics io on
    go
    ...code...
    go
    declare cursor for select
    go
    reste du code
    go

    et j'obtiens toujours ceci :

    QUERY PLAN FOR STATEMENT 1 (at line 1).




    STEP 1
    The type of query is SET OPTION ON.


    Server Message: Number 3630, Severity 10
    Server 'TIT1_PAR_TST_SQL', Line 3:
    Total estimated I/O cost for statement 1 (at line 1): 0.




    QUERY PLAN FOR STATEMENT 2 (at line 2).




    STEP 1
    The type of query is SET STATISTICS ON.


    Server Message: Number 3630, Severity 10
    Server 'TIT1_PAR_TST_SQL', Line 3:
    Total estimated I/O cost for statement 2 (at line 2): 0.




    QUERY PLAN FOR STATEMENT 3 (at line 3).




    STEP 1
    The type of query is SET OPTION ON.


    Server Message: Number 3630, Severity 10
    Server 'TIT1_PAR_TST_SQL', Line 3:
    Total estimated I/O cost for statement 3 (at line 3): 0.


    Server Message: Number 7344, Severity 15
    Server 'TIT1_PAR_TST_SQL', Line 122:
    DECLARE CURSOR must be the only statement in a query batch.

    et ensuite plein de messages d'erreurs pour des variables non déclarées mais qui l'ont pourtant été dans la première partie du code...

    As-tu une idée ?

  6. #6
    Membre chevronné

    Profil pro
    Inscrit en
    Janvier 2006
    Messages
    1 307
    Détails du profil
    Informations personnelles :
    Âge : 64
    Localisation : Suisse

    Informations forums :
    Inscription : Janvier 2006
    Messages : 1 307
    Points : 1 828
    Points
    1 828
    Par défaut
    Est-ce que SQLAdvantage connait le "go" comme isql?

    Est-ce qu'il y a bien un "go" après le declare ... cursor, et PAS de declare ... cursor dans le batch suivant?

    Michael
    Michael Peppler
    Membre de TeamSybase - www.teamsybase.com

    "A successful [software] tool is one that was used to do something undreamed of by its author." -- S. C. Johnson

  7. #7
    Membre à l'essai
    Inscrit en
    Juin 2006
    Messages
    27
    Détails du profil
    Informations forums :
    Inscription : Juin 2006
    Messages : 27
    Points : 18
    Points
    18
    Par défaut
    Citation Envoyé par mpeppler
    Est-ce que SQLAdvantage connait le "go" comme isql?

    Est-ce qu'il y a bien un "go" après le declare ... cursor, et PAS de declare ... cursor dans le batch suivant?

    Michael
    Le go n'a pas l'air de lui poser de problèmes non.
    En fait il y a deux curseurs, l'autre est déclaré par la suite. Le code a donc cette forme :

    set showplan on
    set statistics io on
    go
    ...code...
    go
    declare cursor for select
    go
    code
    go
    declare cursor for select
    go
    reste du cide
    go

    et j'obtiens toujours
    Line 122:
    DECLARE CURSOR must be the only statement in a query batch.

    Sachant que la ligne 122 est le declare du 1er curseur.

  8. #8
    Membre chevronné

    Profil pro
    Inscrit en
    Janvier 2006
    Messages
    1 307
    Détails du profil
    Informations personnelles :
    Âge : 64
    Localisation : Suisse

    Informations forums :
    Inscription : Janvier 2006
    Messages : 1 307
    Points : 1 828
    Points
    1 828
    Par défaut
    Le fait que l'erreur indique la ligne 122 indique que le "go" n'a pas l'air d'être pris en compte comme séparateur de batch.

    Je n'utilise pas SQLAdvantage (sqsh sous Unix/Linux), donc je ne peux malheureusement pas donner plus d'info à ce niveau là...

    Michael
    Michael Peppler
    Membre de TeamSybase - www.teamsybase.com

    "A successful [software] tool is one that was used to do something undreamed of by its author." -- S. C. Johnson

  9. #9
    Membre à l'essai
    Inscrit en
    Juin 2006
    Messages
    27
    Détails du profil
    Informations forums :
    Inscription : Juin 2006
    Messages : 27
    Points : 18
    Points
    18
    Par défaut
    Citation Envoyé par mpeppler
    Le fait que l'erreur indique la ligne 122 indique que le "go" n'a pas l'air d'être pris en compte comme séparateur de batch.

    Je n'utilise pas SQLAdvantage (sqsh sous Unix/Linux), donc je ne peux malheureusement pas donner plus d'info à ce niveau là...

    Michael
    pourtant si j'execute un simple
    select * from X
    go

    sous Advantage, il ne rale pas et execute bien ma requête.

  10. #10
    Membre à l'essai
    Inscrit en
    Juin 2006
    Messages
    27
    Détails du profil
    Informations forums :
    Inscription : Juin 2006
    Messages : 27
    Points : 18
    Points
    18
    Par défaut
    Bon j'ai du mieux, j'ai réussi à identifier la sous requête qui gène :

    SELECT @ID_ACTNAIRE = ACTNAIRE.ID_ACTNAIRE,
    @ID_ACTNAIRE_NOMI = ACTNAIRE_NOMI.ID_ACTNAIRE,
    @NB_ACT_VS_OLD = ACTNAIRE_NOMI.NB_ACT_VS,
    @NB_ACT_VP_OLD = ACTNAIRE_NOMI.NB_ACT_VP

    FROM ACTNAIRE, ACTNAIRE_NOMI

    WHERE ACTNAIRE.ID_ASS_GEN = @ID_ASS_GEN
    AND ACTNAIRE.ID_NOMINATIF = @ID_NOMINATIF
    AND ACTNAIRE_NOMI.ID_ASS_GEN = @ID_ASS_GEN
    AND ACTNAIRE_NOMI.ID_ACTNAIRE = ACTNAIRE.ID_ACTNAIRE
    AND ACTNAIRE_NOMI.ID_TYPE_PROP = @ID_TYPE_PROP
    -- AND ACTNAIRE_NOMI.CD_VALEUR = @CD_VALEUR

    Quand la dernière ligne est commentée j'obtiens le plan suivant :


    QUERY PLAN FOR STATEMENT 6 (at line 81).




    STEP 1
    The type of query is SELECT.


    FROM TABLE
    ACTNAIRE_NOMI
    Nested iteration.
    Using Clustered Index.
    Index : PK_ACTNAIRE_NOMI
    Forward scan.
    Positioning by key.
    Keys are:
    ID_ASS_GEN ASC
    Using I/O Size 16 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.


    FROM TABLE
    ACTNAIRE
    Nested iteration.
    Using Clustered Index.
    Index : PK_ACTNAIRE
    Forward scan.
    Positioning by key.
    Keys are:
    ID_ASS_GEN ASC
    ID_ACTNAIRE ASC
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.


    Server Message: Number 3630, Severity 10
    Server 'TIT1_PAR_TST_SQL', Line 383:
    Total estimated I/O cost for statement 6 (at line 81): 88220.


    Si elle n'est pas commentée :


    QUERY PLAN FOR STATEMENT 6 (at line 81).




    STEP 1
    The type of query is SELECT.


    FROM TABLE
    ACTNAIRE_NOMI
    Nested iteration.
    Using Clustered Index.
    Index : PK_ACTNAIRE_NOMI
    Forward scan.
    Positioning by key.
    Keys are:
    ID_ASS_GEN ASC
    Using I/O Size 16 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.


    FROM TABLE
    ACTNAIRE
    Nested iteration.
    Using Clustered Index.
    Index : PK_ACTNAIRE
    Forward scan.
    Positioning by key.
    Keys are:
    ID_ASS_GEN ASC
    ID_ACTNAIRE ASC
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.


    Server Message: Number 3630, Severity 10
    Server 'TIT1_PAR_TST_SQL', Line 383:
    Total estimated I/O cost for statement 6 (at line 81): 13404.

    Donc mise à part la jointure en plus, je n'arrive pas à comprendre d'où vient une telle différence au niveau des io ??

  11. #11
    Rédacteur/Modérateur

    Avatar de Fabien Celaia
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Octobre 2002
    Messages
    4 220
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 53
    Localisation : Suisse

    Informations professionnelles :
    Activité : Administrateur de base de données
    Secteur : Service public

    Informations forums :
    Inscription : Octobre 2002
    Messages : 4 220
    Points : 19 549
    Points
    19 549
    Billets dans le blog
    25
    Par défaut
    Question basique déjà : pourquoi passez-vous par un curseur ???
    Sr DBA Oracle / MS-SQL / MySQL / Postgresql / SAP-Sybase / Informix / DB2

    N'oublie pas de consulter mes articles, mon blog, les cours et les FAQ SGBD

    Attention : pas de réponse technique par MP : pensez aux autres, passez par les forums !

  12. #12
    Membre à l'essai
    Inscrit en
    Juin 2006
    Messages
    27
    Détails du profil
    Informations forums :
    Inscription : Juin 2006
    Messages : 27
    Points : 18
    Points
    18
    Par défaut
    Citation Envoyé par fadace
    Question basique déjà : pourquoi passez-vous par un curseur ???
    Je ne suis pas le créateur de cette proc, on m'a juste demandé de l'optimiser.

    Les curseurs ne sont-ils pas censés être performants maintenant ? Peut-on systématiquement les remplacer par autre chose de mieux ?

    Dernière question : est-il possible d'optimiser le petit bout de code posté plus haut compte tenu de ce que j'ai dit (disparition d'une jointure) ?

  13. #13
    Membre chevronné

    Profil pro
    Inscrit en
    Janvier 2006
    Messages
    1 307
    Détails du profil
    Informations personnelles :
    Âge : 64
    Localisation : Suisse

    Informations forums :
    Inscription : Janvier 2006
    Messages : 1 307
    Points : 1 828
    Points
    1 828
    Par défaut
    Citation Envoyé par Deedoo2000
    Server Message: Number 3630, Severity 10
    Server 'TIT1_PAR_TST_SQL', Line 383:
    Total estimated I/O cost for statement 6 (at line 81): 88220.

    ...

    Server Message: Number 3630, Severity 10
    Server 'TIT1_PAR_TST_SQL', Line 383:
    Total estimated I/O cost for statement 6 (at line 81): 13404.

    Donc mise à part la jointure en plus, je n'arrive pas à comprendre d'où vient une telle différence au niveau des io ??
    Il est possible que la colonne CD_VALEUR ait des statistiques, et qu'elle soit prise en compte par l'optimiseur pour faire l'estimation du nombre de rows qui doivent être traités pour satisfaire la reqète.

    Il faut bien voir que le message est du nombre d'IO estimé, pas du nombre d'IO effectif pour la requète. Pour avoir le nombre d'IO effectif il faut voir l'output de SET STATISTICS IO ON à la fin de l'exécution de la requète.

    On peut aussi analyser les données utilisées par l'optimiseur en enablant les trace flags 302 et 310:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
     
    dbcc traceon(3604,302,310)
    go
    cela donne des informations sur toutes les décisions prisent par l'optimiseur...


    Michael
    Michael Peppler
    Membre de TeamSybase - www.teamsybase.com

    "A successful [software] tool is one that was used to do something undreamed of by its author." -- S. C. Johnson

  14. #14
    Membre à l'essai
    Inscrit en
    Juin 2006
    Messages
    27
    Détails du profil
    Informations forums :
    Inscription : Juin 2006
    Messages : 27
    Points : 18
    Points
    18
    Par défaut
    Citation Envoyé par mpeppler
    Il est possible que la colonne CD_VALEUR ait des statistiques, et qu'elle soit prise en compte par l'optimiseur pour faire l'estimation du nombre de rows qui doivent être traités pour satisfaire la reqète.

    Il faut bien voir que le message est du nombre d'IO estimé, pas du nombre d'IO effectif pour la requète. Pour avoir le nombre d'IO effectif il faut voir l'output de SET STATISTICS IO ON à la fin de l'exécution de la requète.

    On peut aussi analyser les données utilisées par l'optimiseur en enablant les trace flags 302 et 310:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
     
    dbcc traceon(3604,302,310)
    go
    cela donne des informations sur toutes les décisions prisent par l'optimiseur...


    Michael
    Je ne comprends pas comment faire la partie en gras. Peux-tu m'en dire plus ?

    Rajouter les trace flags n'a rien changé au plan, il n'y a aucune info supplémentaire rajoutée. Est-ce possible ?

    Merci pour l'aide en tout cas, c'est très sympa.

  15. #15
    Membre chevronné

    Profil pro
    Inscrit en
    Janvier 2006
    Messages
    1 307
    Détails du profil
    Informations personnelles :
    Âge : 64
    Localisation : Suisse

    Informations forums :
    Inscription : Janvier 2006
    Messages : 1 307
    Points : 1 828
    Points
    1 828
    Par défaut
    Voici un exemple - j'utilise sqsh comme client, mais isql devrait avoir le même comportement:

    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
     
    [22] MYDB.mondb.1> set statistics io on;
    Total writes for this command: 0
    [23] MYDB.mondb.1> select sum(CPUTime) from monEngine where servername='MYDB' and runDate > 'jul 31 2006';
     
     -----------
           41578
    Table: monEngine scan count 1, logical reads: (regular=32 apf=0 total=32), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
    Total writes for this command: 0
     
    (1 row affected)
    [24] MYDB.mondb.1> select sum(CPUTime) from monEngine where runDate > 'jul 31 2006';
     
     -----------
           41578
    Table: monEngine scan count 1, logical reads: (regular=1787 apf=0 total=1787), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
    Total writes for this command: 0
     
    (1 row affected)
    [25] MYDB.mondb.1>
    Dans la première exécution (avec servername dans la clause WHERE) on consomme 32 IO logiques pour satisfaire la requète. Dans le deuxième cas on consomme 1787 IO logiques. Dans les deux cas il n'y a pas d'IO physique - toutes les pages qui sont demandées sont déjà en cache. La différence du nombre d'IO indique que l'index n'est pas utilisé correctement dans la deuxième requète.

    En ce qui concerne les trace flags 302 et 310 ils devraient donner un output très volumineux, mais il est possible qu'il faille avoir "sa_role" pour pouvoir y avoir accès.

    Michael
    Michael Peppler
    Membre de TeamSybase - www.teamsybase.com

    "A successful [software] tool is one that was used to do something undreamed of by its author." -- S. C. Johnson

  16. #16
    Membre à l'essai
    Inscrit en
    Juin 2006
    Messages
    27
    Détails du profil
    Informations forums :
    Inscription : Juin 2006
    Messages : 27
    Points : 18
    Points
    18
    Par défaut
    Citation Envoyé par mpeppler
    Voici un exemple - j'utilise sqsh comme client, mais isql devrait avoir le même comportement:

    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
     
    [22] MYDB.mondb.1> set statistics io on;
    Total writes for this command: 0
    [23] MYDB.mondb.1> select sum(CPUTime) from monEngine where servername='MYDB' and runDate > 'jul 31 2006';
     
     -----------
           41578
    Table: monEngine scan count 1, logical reads: (regular=32 apf=0 total=32), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
    Total writes for this command: 0
     
    (1 row affected)
    [24] MYDB.mondb.1> select sum(CPUTime) from monEngine where runDate > 'jul 31 2006';
     
     -----------
           41578
    Table: monEngine scan count 1, logical reads: (regular=1787 apf=0 total=1787), physical reads: (regular=0 apf=0 total=0), apf IOs used=0
    Total writes for this command: 0
     
    (1 row affected)
    [25] MYDB.mondb.1>
    Dans la première exécution (avec servername dans la clause WHERE) on consomme 32 IO logiques pour satisfaire la requète. Dans le deuxième cas on consomme 1787 IO logiques. Dans les deux cas il n'y a pas d'IO physique - toutes les pages qui sont demandées sont déjà en cache. La différence du nombre d'IO indique que l'index n'est pas utilisé correctement dans la deuxième requète.

    En ce qui concerne les trace flags 302 et 310 ils devraient donner un output très volumineux, mais il est possible qu'il faille avoir "sa_role" pour pouvoir y avoir accès.

    Michael
    Exact, je n'ai pas le sa_role

  17. #17
    Membre à l'essai
    Inscrit en
    Juin 2006
    Messages
    27
    Détails du profil
    Informations forums :
    Inscription : Juin 2006
    Messages : 27
    Points : 18
    Points
    18
    Par défaut
    J'ai une question subsidiaire :

    Que signifie exactement cette syntaxe en gras dans le résultat d'un showplan :

    STEP 1
    The type of query is UPDATE.
    The update mode is direct.


    FROM TABLE
    ACTNAIRE_NOMI
    Nested iteration.
    Using Clustered Index.
    Index : PK_ACTNAIRE_NOMI
    Forward scan.
    Positioning by key.
    Keys are:
    ID_ASS_GEN ASC
    ID_ACTNAIRE ASC
    CD_VALEUR ASC
    ID_TYPE_PROP ASC

    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.
    TO TABLE
    ACTNAIRE_NOMI
    Using I/O Size 2 Kbytes for data pages.

    Merci

  18. #18
    Membre chevronné

    Profil pro
    Inscrit en
    Janvier 2006
    Messages
    1 307
    Détails du profil
    Informations personnelles :
    Âge : 64
    Localisation : Suisse

    Informations forums :
    Inscription : Janvier 2006
    Messages : 1 307
    Points : 1 828
    Points
    1 828
    Par défaut
    Cela signifie que l'optimiseur utilise les quatres cles pour se positioner dans la table.

    Prenons un exemple:

    Si on a un indexe comme ceci:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
     
    create index foo_ix on foo(user, datecr, location, type)
    l'optimiseur peut utiliser cet indexe si la requète a dans la clause WHERE une ou plusieurs colonnes qui font partie de l'indexe, pour autant qu'il n'y ait pas de trou (cad "user", "datecr", ou "user", "datecr", "location", mais PAS "datecr" tout seul).

    Michael

    PS. je ne suis pas très satisfait de mon explication, mais j'ai parfois un peu de peine à traduire les concepts que je connais bien en anglais (p.ex. "the leading columns of the index..."). J'espère que c'est quand même compréhensible!
    Michael Peppler
    Membre de TeamSybase - www.teamsybase.com

    "A successful [software] tool is one that was used to do something undreamed of by its author." -- S. C. Johnson

  19. #19
    Membre à l'essai
    Inscrit en
    Juin 2006
    Messages
    27
    Détails du profil
    Informations forums :
    Inscription : Juin 2006
    Messages : 27
    Points : 18
    Points
    18
    Par défaut
    Bon, je résume ma situation desespérée...

    Une proc s'est mise à avoir des performances complètement dégradées suite à la suppression d'une jointure dans les requêtes où elle apparait (jointure sur CD_VALEUR).
    Cette proc est asse longue, mais en executant le show plan avec et sans jointure je n'obtiens que les seules différences suivantes (les différences en gras) :


    pour le code :

    delete ACTNAIRE_NOMI
    from #modif
    where #modif.ID_ASS_GEN = ACTNAIRE_NOMI.ID_ASS_GEN
    and #modif.ID_ACTNAIRE = ACTNAIRE_NOMI.ID_ACTNAIRE
    and #modif.ID_TYPE_PROP = ACTNAIRE_NOMI.ID_TYPE_PROP
    --and #modif.CD_VALEUR = ACTNAIRE_NOMI.CD_VALEUR

    --------------------------------------------------------------------------
    QUERY PLAN FOR STATEMENT 5 (at line 98).




    STEP 1
    The type of query is DELETE.
    The update mode is deferred.


    FROM TABLE
    #modif
    Nested iteration.
    Table Scan.
    Forward scan.
    Positioning at start of table.
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.


    FROM TABLE
    ACTNAIRE_NOMI
    Nested iteration.
    Using Clustered Index.
    Index : PK_ACTNAIRE_NOMI
    Forward scan.
    Positioning by key.
    Keys are:
    ID_ASS_GEN ASC
    ID_ACTNAIRE ASC
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.
    TO TABLE
    ACTNAIRE_NOMI
    Using I/O Size 2 Kbytes for data pages.

    Server Message: Number 3630, Severity 10
    Server 'TIT1_PAR_TST_SQL', Line 118:
    Total estimated I/O cost for statement 5 (at line 98): 3054.

    au lieu de

    QUERY PLAN FOR STATEMENT 5 (at line 98).




    STEP 1
    The type of query is DELETE.
    The update mode is deferred.


    FROM TABLE
    #modif
    Nested iteration.
    Table Scan.
    Forward scan.
    Positioning at start of table.
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.


    FROM TABLE
    ACTNAIRE_NOMI
    Nested iteration.
    Using Clustered Index.
    Index : PK_ACTNAIRE_NOMI
    Forward scan.
    Positioning by key.
    Keys are:
    ID_ASS_GEN ASC
    ID_ACTNAIRE ASC
    CD_VALEUR ASC
    ID_TYPE_PROP ASC

    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.
    TO TABLE
    ACTNAIRE_NOMI
    Using I/O Size 2 Kbytes for data pages.


    Server Message: Number 3630, Severity 10
    Server 'TIT1_PAR_TST_SQL', Line 118:
    Total estimated I/O cost for statement 5 (at line 98): 3054.
    -------------------------------------------------------------------------
    Pour le select vu plus haut dans le topic :

    QUERY PLAN FOR STATEMENT 6 (at line 81).




    STEP 1
    The type of query is SELECT.


    FROM TABLE
    ACTNAIRE_NOMI
    Nested iteration.
    Using Clustered Index.
    Index : PK_ACTNAIRE_NOMI
    Forward scan.
    Positioning by key.
    Keys are:
    ID_ASS_GEN ASC
    Using I/O Size 16 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.


    FROM TABLE
    ACTNAIRE
    Nested iteration.
    Using Clustered Index.
    Index : PK_ACTNAIRE
    Forward scan.
    Positioning by key.
    Keys are:
    ID_ASS_GEN ASC
    ID_ACTNAIRE ASC
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.


    Server Message: Number 3630, Severity 10
    Server 'TIT1_PAR_TST_SQL', Line 383:
    Total estimated I/O cost for statement 6 (at line 81): 88220.


    au lieu de

    QUERY PLAN FOR STATEMENT 6 (at line 81).




    STEP 1
    The type of query is SELECT.


    FROM TABLE
    ACTNAIRE_NOMI
    Nested iteration.
    Using Clustered Index.
    Index : PK_ACTNAIRE_NOMI
    Forward scan.
    Positioning by key.
    Keys are:
    ID_ASS_GEN ASC
    Using I/O Size 16 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.


    FROM TABLE
    ACTNAIRE
    Nested iteration.
    Using Clustered Index.
    Index : PK_ACTNAIRE
    Forward scan.
    Positioning by key.
    Keys are:
    ID_ASS_GEN ASC
    ID_ACTNAIRE ASC
    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.


    Server Message: Number 3630, Severity 10
    Server 'TIT1_PAR_TST_SQL', Line 383:
    Total estimated I/O cost for statement 6 (at line 81): 13404.
    -------------------------------------------------------------------------

    Pour l'update suivant

    update ACTNAIRE_NOMI

    set NB_ACT_VS = @NB_ACT_VS,
    NB_ACT_VP = @NB_ACT_VP

    where ID_ASS_GEN = @ID_ASS_GEN
    and ID_ACTNAIRE = @ID_ACTNAIRE
    -- and CD_VALEUR = @CD_VALEUR
    and ID_TYPE_PROP = @ID_TYPE_PROP

    QUERY PLAN FOR STATEMENT 26 (at line 292).




    STEP 1
    The type of query is UPDATE.
    The update mode is direct.


    FROM TABLE
    ACTNAIRE_NOMI
    Nested iteration.
    Using Clustered Index.
    Index : PK_ACTNAIRE_NOMI
    Forward scan.
    Positioning by key.
    Keys are:
    ID_ASS_GEN ASC
    ID_ACTNAIRE ASC

    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.
    TO TABLE
    ACTNAIRE_NOMI
    Using I/O Size 2 Kbytes for data pages.

    au lieu de :

    QUERY PLAN FOR STATEMENT 26 (at line 292).




    STEP 1
    The type of query is UPDATE.
    The update mode is direct.


    FROM TABLE
    ACTNAIRE_NOMI
    Nested iteration.
    Using Clustered Index.
    Index : PK_ACTNAIRE_NOMI
    Forward scan.
    Positioning by key.
    Keys are:
    ID_ASS_GEN ASC
    ID_ACTNAIRE ASC
    CD_VALEUR ASC
    ID_TYPE_PROP ASC

    Using I/O Size 2 Kbytes for data pages.
    With LRU Buffer Replacement Strategy for data pages.
    TO TABLE
    ACTNAIRE_NOMI
    Using I/O Size 2 Kbytes for data pages.
    -------------------------------------------------------------------------

    Sachant que l'index PK_ACTNAIRE_NOMI utilisé dans ces requêtes est défini comme ceci :

    PK_ACTNAIRE_NOMI | clustered, unique located on default | ID_ASS_GEN, ID_ACTNAIRE, CD_VALEUR, ID_TYPE_PROP

    Je suis vraiment perdu, je ne comprends pas comment je peux récupérer cette perte de performances (la proc met maintenant plus de trois fois le temps initial)

    Quelqu'un peut-il me sauver ?

  20. #20
    Membre chevronné

    Profil pro
    Inscrit en
    Janvier 2006
    Messages
    1 307
    Détails du profil
    Informations personnelles :
    Âge : 64
    Localisation : Suisse

    Informations forums :
    Inscription : Janvier 2006
    Messages : 1 307
    Points : 1 828
    Points
    1 828
    Par défaut
    Je suis vraiment perdu, je ne comprends pas comment je peux récupérer cette perte de performances (la proc met maintenant plus de trois fois le temps initial)
    En enlevant le champ CD_VALEUR de la clause WHERE on empèche l'index d'être utilisé correctement. Puisque CD_VALEUR est le troisième champ sur quatre dans l'index l'optimizeur ne peut maintenant utiliser que les deux premiers champs pour se positionner, d'où la perte de performance observée.

    Si CD_VALEUR ne doit absolument PAS être utilisé dans la clause WHERE alors la solution serait probablement de modifier la composition de l'index, ou d'en créer un deuxième.

    Mais avant de faire cela il faudrait faire une analyse plus complète de la problématique, et il est tout à fait possible que la proc puisse être modifiée pour avoir un meilleur comportement global (mais pour ce faire il faut déjà avoir une bonne idée de la structure de la base, des tables utilisées, de l'objectif à atteindre, etc - pas toujours facile pour un problème non-trivial dans ce genre de forum...)

    Michael
    Michael Peppler
    Membre de TeamSybase - www.teamsybase.com

    "A successful [software] tool is one that was used to do something undreamed of by its author." -- S. C. Johnson

Discussions similaires

  1. [T-SQL] Optimisation de proc
    Par Deedoo2000 dans le forum Sybase
    Réponses: 19
    Dernier message: 02/08/2006, 09h40
  2. [ASE][T-SQL] Optimisation d'un update
    Par metheorn dans le forum Sybase
    Réponses: 2
    Dernier message: 06/03/2006, 14h12
  3. [PL/SQL] Optimisation traitement
    Par nako dans le forum Oracle
    Réponses: 1
    Dernier message: 29/12/2005, 17h01
  4. [SQL] optimisation
    Par s.grenet dans le forum Langage SQL
    Réponses: 6
    Dernier message: 13/05/2005, 12h57
  5. [PL/SQL] Optimisation requete SQL
    Par CDRIK dans le forum Langage SQL
    Réponses: 3
    Dernier message: 14/10/2004, 10h52

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