La meilleure syntaxe

[The eye]

Bonjour à tous,

Ci-dessous 3 syntaxes différentes pour réaliser la même opération.

Code SQL :

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
/* REQUETE 1 */
SELECT p.id_produit,p.nom,pcb.code_barre 
FROM produit p 
LEFT OUTER JOIN (
      SELECT id_produit, max(ISNULL(code_barre, 0)) AS code_barre 
      FROM Produit_code_barre
      GROUP BY id_produit
) pcb ON pcb.id_produit = p.id_produit
WHERE  p.id_type_produit = 1
	AND p.id_produit < 5000
 
/* REQUETE 2 */
SELECT p.id_produit,p.nom,pcb.code_barre FROM 
produit p 
OUTER apply (
		SELECT top 1 sub_pcb.id_produit, sub_pcb.code_barre
		FROM Produit_code_barre sub_pcb 
		WHERE p.id_produit=sub_pcb.id_produit
		ORDER BY sub_pcb.id_produit, sub_pcb.code_barre
) pcb
WHERE  p.id_type_produit = 1
	AND p.id_produit < 5000
 
/* REQUETE 3 */
SELECT id_produit,nom,code_barre 
FROM (
	SELECT p.id_produit,p.nom,pcb.code_barre, RANK() OVER (PARTITION BY p.id_produit ORDER BY pcb.code_barre DESC) AS RANK
	FROM produit p 
	LEFT OUTER JOIN Produit_code_barre pcb
		ON pcb.id_produit = p.id_produit
	WHERE p.id_type_produit = 1
		AND p.id_produit < 5000
) T
WHERE RANK = 1

Après une rapide analyse, quand je lance les 3 requêtes dans l'estimateur de plan d'exécution, il m'annonce les consommations prévisionnelles suivantes :

- Requête 1 : 15 %
- Requête 2 : 67 %
- Requête 3 : 18 %

J'aurai pourtant dit, comme ça à l'instinct que la 3 ème requête serait la meilleure...

Qu'en est-il en terme de lecture/écriture/cpu ? Cette répartition est-elle fiable avec 10 000 exécutions simultanées par exemples ?

Pourquoi cet ordre là finalement ?

A quoi sert cette 2ème syntaxe (et plus particulièrement l'instruction OUTER APPLY) si elle peut être remplacée par au moins 2 syntaxes plus performantes ?

Merci d'avance pour vos suggestions/analyses

[mikedavem]

Que donne l'exécution de ces 3 requêtes avec le plan d'exécution réel ?

Donner nous aussi les informations relatives à l'activation des options de statistiques suivantes :

Code :

1
2
3
4
5
6
7
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
 
<requete>
 
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

++

[aieeeuuuuu]

Bonjour,

sans connaitre les index sur vos tables, ni avoir les plans d’exécution, difficile de répondre précisément.

que donne cette requête

Code SQL :

1
2
3
4
5
6
7
8
 
SELECT p.id_produit,p.nom, ISNULL(MAX(pcb.code_barre), 0) AS code_barre
FROM produit p 
LEFT OUTER JOIN Produit_code_barre pcb
	ON pcb.id_produit = p.id_produit
WHERE  p.id_type_produit = 1
	AND p.id_produit < 5000
GROUP BY p.id_produit,p.nom

pour ce qui est des IO et cpu, vous pouvez exécuter ceci :

Code SQL :

1
2
3
 
SET STATISTICS IO ON
SET STATISTICS TIME ON

et ensuite exécuter vos requêtes. Ceci vous indiquera le temps et les IO pour chaque requête. vous pouvez poster les résultats.

[The eye]

Voici ce que donne l'analyse dans l'ordre :


(4989*ligne(s) affectée(s))
Table 'Produit'. Nombre d'analyses 1, lectures logiques 720, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'Produit_code_barre'. Nombre d'analyses 1, lectures logiques 29, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.

SQL Server \endash Temps d'exécution*:
, Temps UC = 47*ms, temps écoulé = 345*ms.

(4989*ligne(s) affectée(s))
Table 'Produit_code_barre'. Nombre d'analyses 4989, lectures logiques 15952, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'Produit'. Nombre d'analyses 1, lectures logiques 720, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.

SQL Server \endash Temps d'exécution*:
, Temps UC = 31*ms, temps écoulé = 638*ms.

(4989*ligne(s) affectée(s))
Table 'Produit_code_barre'. Nombre d'analyses 1, lectures logiques 29, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.
Table 'Produit'. Nombre d'analyses 1, lectures logiques 720, lectures physiques 0, lectures anticipées 0, lectures logiques de données d'objets volumineux 0, lectures physiques de données d'objets volumineux 0, lectures anticipées de données d'objets volumineux 0.

SQL Server \endash Temps d'exécution*:
, Temps UC = 47*ms, temps écoulé = 469*ms.

[mikedavem]

Il manque également la DDL de vos tables et de vos index présents sur celles-ci. Pouvez vous nous les mettre ?

Il faut savoir également que le comportement et la performance d'une requête peut changer avec la volumétrie.

++

[The eye]

Ce sont des table assez grosses (entre 500 000 et 1 000 000 d'enregistrements)

Pour le détail des tables, considérez simplement :

PRODUIT :

id_produit INT -> PK
nom VARCHAR(250)
id_type_produit INT


PRODUIT_CODE_BARRE :

id_produit INT , code_barre VARCHAR (50) -> PK

[mikedavem]

Pas de foreign key entre vos 2 tables ? Pas d'autres index ?

++

[iberserk]

Le plan d’exécution réel peut être différent du plan estimé...

Lancez les requêtes une a une et plusieurs fois, ensuite tirez des conclusions... sur des temps aussi faible d'une exécution à l'autres vous pouvez avoir des temps assez changeant.

Pour la solution la plus performante vous obtiendrez de meilleur résultat avec une vue indéxée sur la table des code barres en agregant le MAX par ID...