Discussion :

[StringBuilder]

Bonjour,

Je suis en train de suivre une formation (pas dédiée SQL) et le formateur nous dit que SQL Server (toutes versions) est pénalisé dans sa recherche du meilleur plan d'exécution par le contenu de la clause ORDER BY.

Dans le cadre d'une requête monotable.

1/ Il ne faut pas mixer les ASC et DESC sous peine d'avoir l'optimiseur qui ne prenne pas forcément le meilleur index
2/ Dans tous les cas, il faut toujours ajouter en dernier la clé primaire de la table en ASC ou DESC en fonction de l'indicateur de tri choisi pour les autres colonnes.

De prime abord, j'ai l'impression que c'est du total n'importe quoi.
Pourtant le gars à priori il s'y connaît bien, plan d'exécution à l'appui.

C'est vrai ça ?

Note: quoique le truc est biaisé, l'outil ajoute systématiquement la clé primaire à la fin du ORDER BY en ASC sauf s'il est explicitement déjà déclaré en DESC. J'ai l'impression que c'est cet ajout qui fout le bordel. Des explications ?

[SQLpro]

De toutes façons, un ORDER BY n'utilisera un index que si l'index est suffisamment couvrant pour tous les éléments de la requête. Dans le cas contraire, un tri sera effectué. Or SQL Server fonctionnant nativement de manière parallèle il battra la plupart des autres SGBDR en terme de temps d'exécution.
Oracle nécessite un module externe payant qui s'appelle parallele query, tandis que PostGreSQL ne sait pas faire du parallélisme (pas de threading) sauf pour quelques rares cas et sur 4 opérateurs seulement et il faut lui indiquer comment faire.... Quant à MariaDB et MySQmerde... pas de parallélisme du tout....
https://www.postgresql.org/message-i...163f4%40iki.fi
https://postgresql.developpez.com/ac...t-il-la-peine/


Donc je rigole par ce que comparer deux plans de requêtes de deux SGBDR différents, c'est comme comparer un moteur de voiture et celui d'un hors bord...

A +

[StringBuilder]

Euh, j'ai pas parlé d'Oracle (ou alors c'est une faute de frappe).

On est bien en full SQL Server

[StringBuilder]

En fait, il dit qu'avec la table suivante (simplifié) :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
create table personne
(
id int not null identity primary key,
nom char(20) not null,
prenom char(20) not null,
age int not null default 0
)
go
 
create index ix_nom on personne (nom, prenom)
go

Si on écrit ces requête :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select *
from personne
order by nom desc, prenom desc, id asc

C'est plus lent que :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select *
from personne
order by nom desc, prenom desc, id desc

Et que :
C'est plus lent que :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select *
from personne
order by nom desc, prenom asc, id asc

Normal ?

[SQLpro]

NON.... Pour preuve :

-- la table de test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
CREATE SCHEMA S_PRS;
GO
 
CREATE TABLE S_PRS.T_PERSONNE_PHYSIQUE_PSP
(
	"PRS_ID"              INT NOT NULL PRIMARY KEY,
	"CVT_ID"              INT,
	"PSP_NOM"             CHAR(32),
	"PSP_PRENOM"          VARCHAR(25),
	"PSP_DATE_NAISSANCE"  DATETIME2(0), --> mettre TIMESTAMP pour PostGreSQL
	"PSP_SOUNDEX"         CHAR(4)
)

La table contient 1 253 609 lignes

-- le script de test :

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
SET STATISTICS TIME ON
 
select *
from   S_PRS.T_PERSONNE_PHYSIQUE_PSP
order  by PSP_NOM desc, PSP_PRENOM desc, PRS_ID asc;
GO 10
 
select *
from   S_PRS.T_PERSONNE_PHYSIQUE_PSP
order  by PSP_NOM desc, PSP_PRENOM desc, PRS_ID desc;
GO 10
 
select *
from    S_PRS.T_PERSONNE_PHYSIQUE_PSP
order  by PSP_NOM desc, PSP_PRENOM asc, PRS_ID asc;
GO 10

CREATE INDEX X1 ON S_PRS.T_PERSONNE_PHYSIQUE_PSP (PSP_NOM, PSP_PRENOM)

Plan d'exécution des requêtes :


Les 3 plans sont identiques car cet index n'est pris par aucune requête puisqu'il n'est pas couvrant...

-- synthèse des résultats :

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
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4250*ms, temps écoulé = 1803*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4356*ms, temps écoulé = 1886*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4265*ms, temps écoulé = 1433*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 3843*ms, temps écoulé = 1416*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 3984*ms, temps écoulé = 1425*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4065*ms, temps écoulé = 1435*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4203*ms, temps écoulé = 1378*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4140*ms, temps écoulé = 1432*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4062*ms, temps écoulé = 1317*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4189*ms, temps écoulé = 1422*ms.
Exécution du lot effectuée*10*fois.
==> moyenne temps UC 4135.7
 
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4232*ms, temps écoulé = 1851*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4158*ms, temps écoulé = 1946*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4187*ms, temps écoulé = 1378*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4156*ms, temps écoulé = 1432*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4547*ms, temps écoulé = 1427*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4186*ms, temps écoulé = 1422*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4189*ms, temps écoulé = 1392*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 3985*ms, temps écoulé = 1424*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 3923*ms, temps écoulé = 1444*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4313*ms, temps écoulé = 1366*ms.
Exécution du lot effectuée*10*fois.
==> moyenne temps UC 4187.6
 
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4263*ms, temps écoulé = 2002*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4032*ms, temps écoulé = 1872*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4013*ms, temps écoulé = 1425*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4173*ms, temps écoulé = 1301*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 3984*ms, temps écoulé = 1417*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4204*ms, temps écoulé = 1379*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4127*ms, temps écoulé = 1422*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4188*ms, temps écoulé = 1422*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4266*ms, temps écoulé = 1415*ms.
(1253609*lignes affectées)
 SQL Server Temps d'exécution*: Temps UC = 4032*ms, temps écoulé = 1417*ms.
Exécution du lot effectuée*10*fois.
==> moyenne temps UC 4128.2

Bref,

• requête 1, moyenne 4135.7
• requête 2, moyenne 4187.6
• requête 3, moyenne 4128.2

Écart type : 32,34 ms (soit moins de 1%...)

De toute façon c'est bidon car le SELECT * ne prendra jamais l'index !

A +