[TSQL]optmiser une requete select

[pagyles007]

Bonjour,
Pouvez vous SVP,me dire ce qui cloche dans cette requete?

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 SELECT    a.colonne1, min(a.colonne2) 
FROM    TABLE a
WHERE    a.colonne3 >= 1
AND a.colonne4 = 0
AND a.colonne5 NOT IN ('5','8')
AND  a.colonne2 > (SELECT    max(b.colonne2) 
                    FROM    TABLE b
                    WHERE    b.colonne3 >= 1
                    AND b.colonne4 <> 0
                    AND b.colonne5 NOT IN ('5','8')
                    AND  b.colonne1 = a.colonne1)
 
GROUP BY a.colonne1

colonne2 --->type DATETIME
TABLE contient environ 1.5Millions de ligne


Merci de votre aide.

[kagemaru]

bonjour,

j'imagine qu'elle tourne longtemps ? si oui, êtes-vous allé jusqu'au bout ?

bref, déjà les NOT IN impliquent un scan complet de la table.

Est-ce que le "and a.colonne5 not in ('5','8')" est discriminant ou pas ?
Est-ce que le "and a.colonne4=0 est discriminant ou pas ?
Est-ce que le "and a.colonne3 >= 1 est discriminant ou pas ?

Est-ce que le colonne1 est bien indexé ?

il y a plusieurs pistes à regarder, la réécriture peut également être une solution.

Y-a-t-il des index sur TABLE ?

[Roller]

Envoyé par kagemaru

bref, déjà les NOT IN impliquent un scan complet de la table.

Non. a.colonne5 not in ('5','8') est interprété comme deux sargs, a.colonne5 <> 5 et a.colonne5 <> 8. L'optimiseur peut donc décider faire un non-matching index scan sur un index couvrant ayant pour première colonne colonne5, si l'un de ces sarg est le plus disciminant de tous. Sinon il peut bien sur utiliser un index sur une colonne d'un autre sarg.

Pour le reste, je suis d'accord, il nous faut la liste des indexes, le plan d'exécution et les stats sur la table pour pouvoir vraiment t'aider, et
avoir un index sur les colonnes présentes dans les sargs les plus discriminants est un bon départ. Réécrire en passant par une ou plusieurs tables temporaires pour aider l'optimiseur peut aussi aider.

[kagemaru]

Non. a.colonne5 not in ('5','8') est interprété comme deux sargs, a.colonne5 <> 5 et a.colonne5 <> 8.

Dans la mesure où tu as un index en effet.

Dans le cas d'un nonmatching scan sur une grosse table, c'est en général peu performant, bien qu'un peu plus efficace qu'un scan de table.

[pagyles007]

Bonjour,
Désolé mais j'ai été absent un petit moment.Alors pour répondre à vos questions:
1- la requete est effectivement tres tres longue,je n'ai pas eu la patience de la laisser finir.il existe en effet un index non clusterisé sur TABLE --> idx_table_02 (colonne1,colonne2) qui est pertinent pour la requete.
j'ai meme essayé de forcer le plan d'xécution à prendre ne compte l'index dans la 1ere étape mais ,cela ne change rien...requete toujours aussi longue.

ci dessous le plan d'exécution:

Code :

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
 QUERY PLAN FOR STATEMENT 1 (at line 1).
 
 
    STEP 1
        The type of query IS SELECT (INTO Worktable1).
        GROUP BY
        Evaluate Grouped MINIMUM AGGREGATE.
 
        FROM TABLE
            TABLE
            a
        Nested iteration.
        TABLE Scan.
        Forward scan.
        Positioning at start of TABLE.
 
        Run subquery 1 (at nesting level 1).
        USING I/O Size 32 Kbytes FOR DATA pages.
        WITH LRU Buffer Replacement Strategy FOR DATA pages.
        TO TABLE
            Worktable1.
 
    STEP 2
        The type of query IS SELECT.
 
        FROM TABLE
            Worktable1.
        Nested iteration.
        TABLE Scan.
        Forward scan.
        Positioning at start of TABLE.
        USING I/O Size 32 Kbytes FOR DATA pages.
        WITH MRU Buffer Replacement Strategy FOR DATA pages.
    STEP 1
 
NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.
 
  QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 AND at line 6).
 
    Correlated Subquery.
    Subquery under an EXPRESSION predicate.
 
 
    STEP 1
        The type of query IS SELECT.
        Evaluate Ungrouped MAXIMUM AGGREGATE.
 
        FROM TABLE
            TABLE
            b
        Nested iteration.
        INDEX : idx_table_02
        Backward scan.
        Positioning at INDEX end.
        Scanning only up TO the first qualifying row.
        USING I/O Size 4 Kbytes FOR INDEX leaf pages.
        WITH LRU Buffer Replacement Strategy FOR INDEX leaf pages.
        USING I/O Size 4 Kbytes FOR DATA pages.
        WITH LRU Buffer Replacement Strategy FOR DATA pages.
 
  END OF QUERY PLAN FOR SUBQUERY 1.

Merci

[arona]

Ben visiblement cela fait des table scan, donc n'utilise pas l'index en question ...
si tu as créé l'index as tu fais un update stat de ta table?? ou alors l'index est pas positionné sur la bonne col.
Je vois que cela, mais je suis pas un dev alors ...
Bon courage
A+

[pagyles007]

oui mais un scan table sur 1,5 million de lignes,c'est pas dramatique, non?
l'index idx_table_02 sur colonne1 existe déjà depuis pas mal de temps, donc les update stats, on a en fait des paquets.
bon ben, si qq'un voit une solution ou des ptits tuyaux pour re-ecrire la requete, je suis preneur...

Merci.

[arona]

Ben moi un table scan sur 1,5 Million de lignes cela me semble beaucoup ... surtout que cela dépend de ta mémoire allouer à ASE plus les acces disk, as tu des baies ? du RAID ?? enfin il me semble qu'un index mieux positionné solutionnerai pas mal de choses !!! Fait des dump de stats avec optdiag et test la création d'autres indexes, update stat et en cas de gain null refais ton index d'origine, et reloa tes stats du début.
Vila pour moi.
A+

[craryb]

la request est elle dépendante de la base sur le serveur?

[fadace]

En plus, des caches de 4K pour index et data, c'est pas des plus optimum à ma connaissance... j'ai plutôt l'habitude de laisser ça pour du log...

[mpeppler]

Envoyé par pagyles007

Bonjour,
Pouvez vous SVP,me dire ce qui cloche dans cette requete?

Code :

Quels sont les indexes existants sur TABLE ?

Michael

[Roller]

Envoyé par fadace

En plus, des caches de 4K pour index et data, c'est pas des plus optimum à ma connaissance... j'ai plutôt l'habitude de laisser ça pour du log...

Je pense que son serveur utilise des pages de 4K, comme l'indiquent les I/O de 32K (1 extent) lors du scan de la outer table.

Sinon, que revoient les requêtes suivantes :

Code :

1
2
3
4
 
SELECT count(1) FROM TABLE a WHERE a.colonne3 >= 1
SELECT count(1) FROM TABLE a WHERE a.colonne4 = 0
SELECT count(1) FROM TABLE a WHERE a.colonne5 NOT IN ('5','8')

Un de ces search arguments (sarg) est peut être discriminant et un index sur l'une de ces colonnes pourrait être plus pertinant.

Sinon, n'hésite pas à découper cette requête en plusieurs, et à utiliser des tables tempo, pour aider l'optimiseur.