[T-SQL] Optimisation requête

Version imprimable

Bonjour,

Je voudrais savoir comment je pourrais optimiser la requête ci-dessous qui me retourne les lignes modifiées entre deux traitements.
Code:

1 2 3 4 5 6 7 8 SELECT * FROM emp t1 JOIN emp t2 ON t1.col1=t2.col1 AND t1.col2=t2.col2 AND t1.col3 = 1 AND t2.col3 = 2 AND (t1.col4 <> t2.col4 or t1.col5 <> t2.col5 or t1.col6 <> t2.col6 or t1.col7 <> t2.col7)
Merci.

17/07/2019, 10h55
StringBuilder
Tu peux essayer de faire ça :
Code:

1 2 3 4 5 6 7 8 select col1, col2, col4, col5, col6, col7 from emp where col3 = 1 except select col1, col2, col4, col5, col6, col7 from emp where col3 = 2
Cela te retournera toutes les lignes pour col3 = 1 qui n'ont pas de ligne avec col3 = 2 dont les colonnes col1, col2, col4, col5, col6 et col7 sont identiques.

Pas certain que ce soit plus rapide cependant.
17/07/2019, 11h09
tatayo

Bonjour,
Pour moi les deux requêtes ne sont pas identiques.
Dans la première, pour qu'une ligne de emp avec col3 = 1 soit renvoyée, il faut qu'il existe une ligne de emp avec col3 = 2 et une correspondance des colonnes col1 et col2 et une valeur différente dans au moins une colonne col4, col5, col6 ou col7.
Avec la deuxième requête, une ligne de emp avec col3 = 1 sera renvoyée s'il n'existe pas une autre ligne avec col3 = 2 et les autres colonnes identiques.

Donc si je prends l'exemple d'une ligne de emp avec col3 = 1 et aucune autre ligne ayant les mêmes valeurs dans col1 et col2 pour col3 = 2, la première requête ne renvoie pas la ligne, la deuxième oui.

Tatayo.
17/07/2019, 11h26
escartefigue
Bonjour,

Quelques remarques et questions
- si la colonne 3 est un chrono traitement, alors tester "en dur" la valeur 1 ou 2 n'est pas une solution pérenne.
- n'y a -t-il pas des mouchards de mise à jour dans cette table (horodatage) ?
- quel est le volume approximatif de cette table ?
17/07/2019, 11h38
Jinkas99

En fait je lance un traitement de chargement de données dans une table. La colonne col3 est une colonne qui s'incrémente avec chaque chargement.

Ce que je cherche à faire c'est de retourner toutes les différences entre deux chargements ou de préférence retourner toutes les lignes (en se basant sur les colonnes citées précédemment) et rajouter une colonne qui va contenir une valeur ou un flag informant qu'il s'agit d'une ligne modifiée.

Testez :

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
SELECT *
FROM   emp t1 
      JOIN emp t2 ON t1.col1=t2.col1
                     AND t1.col2=t2.col2
AND t1.col3 = 1
AND t2.col3 = 2
AND t1.col4 <> t2.col4
UNION ALL
SELECT *
FROM   emp t1 
      JOIN emp t2 ON t1.col1=t2.col1
                     AND t1.col2=t2.col2
AND t1.col3 = 1
AND t2.col3 = 2
AND t1.col5 <> t2.col5
UNION ALL
SELECT *
FROM   emp t1 
      JOIN emp t2 ON t1.col1=t2.col1
                     AND t1.col2=t2.col2
AND t1.col3 = 1
AND t2.col3 = 2
AND t1.col6 <> t2.col6
UNION ALL
SELECT *
FROM   emp t1 
      JOIN emp t2 ON t1.col1=t2.col1
                     AND t1.col2=t2.col2
AND t1.col3 = 1
AND t2.col3 = 2
AND t1.col7 <> t2.col7)

Et avec les bon index… :
X1 ON T1 (COL3, COL1, COL2, COL4)
X2 ON T1 (COL3, COL1, COL2, COL5)
X3 ON T1 (COL3, COL1, COL2, COL6)
X4 ON T1 (COL3, COL1, COL2, COL7)

A +

Merci pour la solution que vous proposez.

Cependant, je n'ai pas compris pourquoi vous parlez d'index. La requête retourne le même nombre de lignes que ma requête de départ. J'aurai voulu afficher si possible toutes les lignes dont col3 = 1 et 2 et afficher dans une colonne supplémentaire la valeur 'N' pour dire qu'elle n'est pas modifiée et 'M' pour dire que c'est modifié (ex: select *, 'M')

Citation:

Envoyé par SQLpro

Testez :

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
SELECT *
FROM   emp t1 
      JOIN emp t2 ON t1.col1=t2.col1
                     AND t1.col2=t2.col2
AND t1.col3 = 1
AND t2.col3 = 2
AND t1.col4 <> t2.col4
UNION ALL
SELECT *
FROM   emp t1 
      JOIN emp t2 ON t1.col1=t2.col1
                     AND t1.col2=t2.col2
AND t1.col3 = 1
AND t2.col3 = 2
AND t1.col5 <> t2.col5
UNION ALL
SELECT *
FROM   emp t1 
      JOIN emp t2 ON t1.col1=t2.col1
                     AND t1.col2=t2.col2
AND t1.col3 = 1
AND t2.col3 = 2
AND t1.col6 <> t2.col6
UNION ALL
SELECT *
FROM   emp t1 
      JOIN emp t2 ON t1.col1=t2.col1
                     AND t1.col2=t2.col2
AND t1.col3 = 1
AND t2.col3 = 2
AND t1.col7 <> t2.col7)

Et avec les bon index… :
X1 ON T1 (COL3, COL1, COL2, COL4)
X2 ON T1 (COL3, COL1, COL2, COL5)
X3 ON T1 (COL3, COL1, COL2, COL6)
X4 ON T1 (COL3, COL1, COL2, COL7)

A +

Je voudrais pouvoir rajouter 2 union all comme ceci :

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
SELECT *, 'M'
FROM   emp t1 
      JOIN emp t2 ON t1.col1=t2.col1
                     AND t1.col2=t2.col2
AND t1.col3 = 1
AND t2.col3 = 2
AND t1.col4 <> t2.col4
UNION ALL
SELECT *, 'M'
FROM   emp t1 
      JOIN emp t2 ON t1.col1=t2.col1
                     AND t1.col2=t2.col2
AND t1.col3 = 1
AND t2.col3 = 2
AND t1.col5 <> t2.col5
UNION ALL
SELECT *, 'M'
FROM   emp t1 
      JOIN emp t2 ON t1.col1=t2.col1
                     AND t1.col2=t2.col2
AND t1.col3 = 1
AND t2.col3 = 2
AND t1.col6 <> t2.col6
UNION ALL
SELECT *, 'M'
FROM   emp t1 
      JOIN emp t2 ON t1.col1=t2.col1
                     AND t1.col2=t2.col2
AND t1.col3 = 1
AND t2.col3 = 2
AND t1.col7 <> t2.col7)
UNION ALL
SELECT *, 'N'
FROM   emp
WHERE col3 = 1
UNION ALL
SELECT *, 'N'
FROM   emp
WHERE col3 = 2

Bonjour,

J'ai fait comme ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT a.*,
    CASE WHEN a.col4 <> b.col4 or a.col5 <> b.col5 or a.col6 <> b.col6 or a.col7 <> b.col7 THEN 'TRUE' ELSE 'FALSE' END AS ETAT
FROM emp a
INNER JOIN emp b ON a.col1 = b.col1 and a.col2=b.col2
WHERE a.col3 = 1
AND b.col3 = 2
UNION ALL
SELECT b.*, 'FALSE'
FROM emp a
INNER JOIN emp b ON a.col1 = b.col1 and a.col2=b.col2
WHERE a.col3 = 1
AND b.col3 = 2

ça a l'air de fonctionner.

Bonjour,

Vous pouvez aussi essayer avec LEAD/LAG

dans ce genre là (pas testé), sans doute à adapter :

Code:

1
2
3
 
SELECT *, CASE WHEN (col4 <> LEAD(col4) OVER(PARTITION BY col1,col2 ORDER BY Col3) or col5 <> LEAD(col5) OVER(PARTITION BY col1,col2 ORDER BY Col3) or col6 <> LEAD(col6) OVER(PARTITION BY col1,col2 ORDER BY Col3) or col7 <> LEAD(col7) OVER(PARTITION BY col1,col2 ORDER BY Col3) THEN 'TRUE' ELSE 'FALSE' END AS ETAT
FROM emp

La solution la plus performante sera probablement celle-ci :
Code:

1 2 3 4 5 6 7 select col1, col2, col4, col5, col6, col7 , min(col3), max(col3) from emp where col3 in (1, 2) group by col1, col2, col4, col5, col6, col7 having min(col3) = max(col3) order by 1, 2, 3, 4, 5, 6, 7;
En cas de donnée qui change, vous aurez deux lignes alors qu'avec des jointures vous rajoutez des colonnes donc à voir si ça vous convient.

29/07/2019, 16h02
Jinkas99

Merci à tous pour les solutions proposées.

Votre solution est intéressante mais je souhaiterai afficher toutes les colonnes de la table et afficher en plus une colonne qui va contenir la valeur true pour toutes lignes modifiées à savoir la ligne avec les valeurs initiales et la ligne contenant les valeurs modifiées et la valeur false pour toutes les lignes qui n'ont connu aucune modification.

La fonction LEAD n'est pas reconnue sur SQL Server 2008.

Cependant j'ai comme ceci et ça a l 'air de fonctionner :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT a.*,
    CASE WHEN a.col4 <> b.col4 or a.col5 <> b.col5 or a.col6 <> b.col6 or a.col7 <> b.col7 THEN 'TRUE' ELSE 'FALSE' END AS ETAT
FROM emp a
INNER JOIN emp b ON a.col1 = b.col1 and a.col2=b.col2
WHERE a.col3 = 1
AND b.col3 = 2
UNION ALL
SELECT b.*, CASE WHEN a.col4 <> b.col4 or a.col5 <> b.col5 or a.col6 <> b.col6 or a.col7 <> b.col7 THEN 'TRUE' ELSE 'FALSE' END AS ETAT
FROM emp a
INNER JOIN emp b ON a.col1 = b.col1 and a.col2=b.col2
WHERE a.col3 = 1
AND b.col3 = 2
order by order by a.col1

Une solution plus optimisée à proposer ?

01/08/2019, 17h46
aieeeuuuuu

~~2008 ou 2008 R2 ?~~, non en fait je crois que ROW_NUMER est disponible dés la version 2005

vous pouvez donc "émuler" le fonction de LEAD et LAG avec ROW_NUMBER.

J'avais donné un exemple avec dans ce post

Cela dit, vous pouvez aussi repartir de la requête de Waldar, a mettre en sous requete avec une jointure dessus pour retrouver les informations des autres colones...