Optimisation requête avec jointure externe SQL Server

[ICEMAN_60]

Bonjour à tous,

Petite question SQL, sur un requête avec jointure externe.

J'ai une base avec deux tables, une comprenant une liste d'entrée avec un ID (T1.item), des champs d'info (T1.UseTime, T1.Code, T1.Status, T1.Qty) et une autre table avec tous les ID possibles des Item (T2.itemID) présent dans la première table.

L’objectif est de récupérer toutes avec un UseTime, un Code et un Status donné sur tous les item qui existent, pas seulement ceux retournées par la une requête sur T1.

Exemple :
T1.Item;T2.itemID;Sum(T1.Qty)

1;1;950
2;2;1986
3;3;NULL
4;4;54
5;5;NULL

et pas seulement

1;1;950
2;2;1986
4;4;54

J'ai donc utilisé le SQL suivant :

Code :

1
2
3
4
5
SELECT T1.item, T2.itemID, Sum(T1.Qty)
FROM DATABASE.dbo.table1 T1 RIGHT OUTER JOIN DATABASE.dbo.table2 T2
ON T1.item = T2.itemID AND (T1.UseTime>='2011-09-01 00:00:00' AND T1.UseTime<='2011-09-02 00:00:00') AND (T1.Code=0 AND T1.STATUS=23 )
GROUP BY T1.item, T2.itemID
ORDER BY T1.item, T2.itemID

Le résultat est OK. Par contre le temps d’exécution me parait un peu long (10s).

Si j’essaye de voir le plan d’exécution estimé de la requête, j’ai l’impression que le moteur fait :

- Une requête liste itemID de la table T2

- Pour chaque itemID retourné, une requête sur la table T1 (soit 70 ! car il y a 70 itemID).

Est-t-il possible d'optimiser cette requête? Je ne connais pas très bien les mécanismes SQL Server.

Merci d’avance de votre expertise.

[elsuket]

Bonjour,

C'est le plan d'exécution réel qu'il vous faut : il s'obtient en pressant CTRL+M avant l'exécution de la requête, dans SQL Server Management Studio.
A la fin de l'exécution de la requête, un nouvel onglet va s'afficher, vous donnant le plan d'exécution réel.

Vous pouvez précéder celle-ci de :

Code :

1
2
SET STATISTICS IO ON
GO

Ce qui vous donnera le nombre d'IO produites contre chaque table.
Avec cet indice et le plan de requête réel, je suis sûr que vous allez trouver l'index manquant.

A priori je dirai qu'il vous manque un index sur la colonne T1.Item.
D'autre part le calcul d’agrégats (SUM(), COUNT(), ...) sur une colonne qui n'est pas indexée peut s'avérer long.

Si vous ne trouvez pas de solution, postez le plan d'exécution réel.

@++

[SQLpro]

Au passage votre requête gagnerait à être écrite de la sorte :

Code :

1
2
3
4
5
6
7
8
9
10
SELECT T2.item, T1.itemID, Sum(T2.Qty)
FROM   DATABASE.dbo.table2 T1 
       LEFT OUTER JOIN dbo.table1 T2
            ON  T2.item = T1.itemID 
            AND T2.UseTime >= '2011-09-01' 
            AND T2.UseTime <= '2011-09-02'
WHERE  T2.Code=0 
  AND  T2.STATUS=23 
GROUP BY T2.item, T1.itemID
ORDER BY T2.item, T1.itemID

Pour l'optimiser, rajoutez les index suivants :

Code :

1
2
T2 (Code, STATUS, item, UseTime) INCLUDE (Qty)
T1 (ItemId) -- sauf s'il existe déjà

Testez votre requête telle quelle et voyez aussi si la récriture suivante :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT T2.item, T1.itemID, Sum(T2.Qty)
FROM   DATABASE.dbo.table2 T1 
       INNER JOIN dbo.table1 T2
            ON  T2.item = T1.itemID 
            AND T2.UseTime >= '2011-09-01' 
            AND T2.UseTime <= '2011-09-02'
WHERE  T2.Code=0 
  AND  T2.STATUS=23 
GROUP BY T2.item, T1.itemID
UNION ALL
SELECT NULL, T1.itemID, 0
FROM   DATABASE.dbo.table2 T1 
WHERE  NOT EXISTS(SELECT * 
                  FROM   dbo.table1 T2
                  WHERE  T2.item = T1.itemID 
                    AND  T2.UseTime >= '2011-09-01' 
                    AND  T2.UseTime <= '2011-09-02'
                    AND  T2.Code=0 
                    AND  T2.STATUS=23)
ORDER BY 1, 2

Donne le même résultat, mais plus rapidement.

A +