[2005] Astuce : rapidité query avec constante

Version imprimable

Voir 40 message(s) de cette discussion en une page

Bonjour,

J'avais déjà remarqué (mais je viens de m'y reconfronter) que malheureusement Sql Server 2005 n'avait pas un comportement très intelligent vis à vis des conditions de jointure/filtre impliquant des constantes.

Exemple (à lire en diagonale, sa complexité importe nullement) :
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 declare @lastName varchar(200) declare @firstName varchar(200) declare @dateTime datetime declare @ClubId int declare @pageIndex int declare @pageSize int set @clubid = 1023 set @lastName = null set @firstName = null set @dateTime = 0 set @pageIndex = 1 set @pageSize = 5 SELECT Id , FederalNumber , Lname , Fname , Birthdate , HomeClub , ShortName , DENSE_RANK() OVER (ORDER BY LnameOK, FnameOK, BirthdateOK) AS GROUPID , ExpirationDate FROM ( SELECT M1.Id , M1.FederalNumber , M1.Lname , M1.Fname , M1.Birthdate , M1.HomeClub , M1.ShortName , M1.ExpirationDate , LnameOK , FnameOK , BirthdateOK , COUNT(*) OVER (PARTITION BY LnameOK, FnameOK, BirthdateOK) AS CNT_All , SUM(CASE WHEN @ClubId IS NULL OR M1.HomeClub = @ClubId THEN 1 ELSE 0 END) OVER (PARTITION BY LnameOK, FnameOK, BirthdateOK) AS CNT_Club FROM dbo.VCentral_MemberDetails AS M1 CROSS APPLY ( SELECT CASE WHEN @lastName IS NULL THEN NULL ELSE M1.LName END AS LnameOK , CASE WHEN @firstName IS NULL THEN NULL ELSE M1.Fname END AS FnameOK , CASE WHEN @dateTime IS NULL THEN NULL ELSE M1.Birthdate END AS BirthdateOK ) AS Part WHERE (@lastName IS NULL OR M1.LName LIKE @lastName + '%') AND (@firstName IS NULL OR M1.Fname LIKE @firstName + '%') AND (@dateTime IS NULL OR @dateTime = 0 OR M1.Birthdate = @dateTime) ) EXT WHERE CNT_All > 1 AND CNT_Club > 0
On repère deux conditions dont la résolution devrait être immédiate, les deux constante validant ces conditions.
Pourtant Sql Server va quand même faire des traitements pour vérifier la condition en entier ce qui peut s'avérer très lourd (des données supplémentaires à récupérer).

J'ai trouvé (mais je ne prétendrai pas être le seul) un moyen à prioris très efficace pour parer à cela : rendre conditionnel la récupération même de ces données.
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 SELECT Id , FederalNumber , Lname , Fname , Birthdate , HomeClub , ShortName , DENSE_RANK() OVER (ORDER BY LnameOK, FnameOK, BirthdateOK) AS GROUPID , ExpirationDate FROM ( SELECT M1.Id , M1.FederalNumber , M1.Lname , M1.Fname , M1.Birthdate , M1.HomeClub , M1.ShortName , M1.ExpirationDate , LnameOK , FnameOK , BirthdateOK , COUNT(*) OVER (PARTITION BY LnameOK, FnameOK, BirthdateOK) AS CNT_All , SUM(CASE WHEN @ClubId IS NULL OR M1.HomeClub = @ClubId THEN 1 ELSE 0 END) OVER (PARTITION BY LnameOK, FnameOK, BirthdateOK) AS CNT_Club FROM dbo.VCentral_MemberDetails AS M1 CROSS APPLY ( SELECT CASE WHEN @lastName IS NULL THEN NULL ELSE M1.LName END AS LnameOK , CASE WHEN @firstName IS NULL THEN NULL ELSE M1.Fname END AS FnameOK , CASE WHEN @dateTime IS NULL THEN NULL ELSE M1.Birthdate END AS BirthdateOK ) AS Part WHERE (@lastName IS NULL OR (SELECT M1.LName WHERE @lastName IS NOT NULL) LIKE @lastName + '%') AND (@firstName IS NULL OR (SELECT M1.Fname WHERE @firstName IS NOT NULL) LIKE @firstName + '%') AND (@dateTime IS NULL OR @dateTime = 0 OR (SELECT M1.Birthdate WHERE @firstName IS NOT NULL) = @dateTime) ) EXT WHERE CNT_All > 1 AND CNT_Club > 0
Et là ça va beaucoups plus vite, surtout si on a quelques indexes qui font mouche.

J'espère que ça pourra en aider certains, car je sais que bcp de dev se retrouve à devoir faire des queries dont les types de condition varient et qui doivent alors choisir entre un seul query avec plusieurs constantes (variables) ou un query dynamique.

20/04/2010, 13h12
SQLpro

As tu essayé le :

Code:

M1.LName LIKE COALESCE(@lastName + '%', M1.LName)

???

Parce que c'est le OR qui le gène !

A +
20/04/2010, 13h56
elsuket

Bonjour,

En apparté ce que demande SergeJack, je remarque ce que je pense être une erreur au sens SQL, et qui devient de plus en plus courante.
Malheureusement le moteur de base de données les laisse passer :(
L'opérateur CROSS APPLY est censé être utilisé avec des fonctions de table, et donc pas avec une requête.
Une jointure de type CROSS JOIN me semble plus appropriée.
Qu'en pensez-vous ?

@++ ;)
20/04/2010, 22h59
SQLpro

Oh, je l'avais pas vu celle là.. Elle est catastrophique !

A +
20/04/2010, 23h15
mikedavem

Ah oui je viens de tester .. effectivement ça passe ...
22/04/2010, 12h18
Sergejack

Citation:

Envoyé par SQLpro

As tu essayé le :

Code:

M1.LName LIKE COALESCE(@lastName + '%', M1.LName)

???

Parce que c'est le OR qui le gène !

A +

C'est tout aussi lent quz l'original et (@dateTime IS NULL OR @dateTime = 0 OR M1.Birthdate = @dateTime) n'est pas couvert.

Par ailleurs, je fais un bon usage de CROSS APPLY, je ne sais pas où vous avez été cherchés que cette forme ne devrait pas être couverte (sous requête) mais pas dans la documentation de Sql Server en tout cas.

Citation:

left_table_source { CROSS | OUTER } APPLY right_table_source
Specifies that the right_table_source of the APPLY operator is evaluated against every row of the left_table_source. This functionality is useful when the right_table_source contains a table-valued function that takes column values from the left_table_source as one of its arguments.
22/04/2010, 13h22
SQLpro

Citation:

Envoyé par Sergejack

C'est tout aussi lent quz l'original et (@dateTime IS NULL OR @dateTime = 0 OR M1.Birthdate = @dateTime) n'est pas couvert.

Par ailleurs, je fais un bon usage de CROSS APPLY, je ne sais pas où vous avez été cherchés que cette forme ne devrait pas être couverte (sous requête) mais pas dans la documentation de Sql Server en tout cas.

Tu as mal compris ce qui est dit dans la doc : Table Valued Fonction = Function qui renvoie une table et non pas sous requête.
Le CROSS APPLY est extrêmement couteux autrement.

A +
22/04/2010, 13h42
elsuket

Citation:

Envoyé par SergeJack

Par ailleurs, je fais un bon usage de CROSS APPLY, je ne sais pas où vous avez été cherchés que cette forme ne devrait pas être couverte (sous requête)

Dans ce cas pourquoi MS se serait amusé à ajouter un opérateur ?

Citation:

Envoyé par SergeJack

This functionality is useful when the right_table_source contains a table-valued function that takes column values from the left_table_source as one of its arguments.

Et ça ne vous a pas mis la puce à l'oreille ? :)

@++ ;)

Ca éviterait ce genre d'absurdité .. fort coûteuse ... (Avec l'opérateur OUTER je précise) ... donc je ne parle pas du cas de SergeJack :)

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
CREATE TABLE test
(
 id INT IDENTITY PRIMARY KEY,
 col1 VARCHAR(10)
)
CREATE TABLE test2
(
 id INT IDENTITY PRIMARY KEY,
 col1 VARCHAR(10)
)
 
DECLARE @i INT;
SET @i = 0;
 
WHILE @i < 100
BEGIN 
 INSERT INTO test (col1) VALUES ('TEST_' + CAST(@i AS VARCHAR(5)));
 INSERT INTO test2 (col1) VALUES ('TEST_' + CAST(@i AS VARCHAR(5)));
 SET @i = @i + 1;
END
 
SET STATISTICS IO ON;
 
SELECT 
	test.id,
	test.col1,
	test2.col1 AS col2
FROM test
OUTER APPLY 
(
 SELECT col1 
 FROM test2 
 WHERE test2.col1 = test.col1
) AS test2
GO
 
SELECT 
	test.id,
	test.col1,
	test2.col1 AS col2
FROM test
LEFT JOIN test2
ON test.id = test2.id;
 
/*
(100*ligne(s) affectée(s))
Table 'test2'. Nombre d'analyses 1, lectures logiques 201, 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 'test'. Nombre d'analyses 1, lectures logiques 2, 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. 
*/
 
/*
(100*ligne(s) affectée(s))
Table 'test2'. Nombre d'analyses 1, lectures logiques 2, 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 'test'. Nombre d'analyses 1, lectures logiques 2, 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.
*/

22/04/2010, 15h54
Waldar

Citation:

Envoyé par elsuket

Dans ce cas pourquoi MS se serait amusé à ajouter un opérateur ?

Pour copier Oracle ! ;)
22/04/2010, 16h47
Sergejack

(cross) apply n'a rien a voir avec (cross) join.
Apply permet d'employer par une fonction table ou une sous requête des champs provenant des lignes renvoyées par la ou les tables à la gauche de l'opérateur.

Cet opérateur n'est pas plus coûteux qu'un autre.
Dans mon exemple, le query plan indiquerait 0% dans le coût de l'opération (calcul de constantes).

Par ailleurs, le cross apply se marie très bien aux clauses TOP.
Par exemple, récupérer toutes les commandes avec l'article le plus chère de chacunne est du gâteau (pas besoin d'un GROUP BY a, b, c, d, ..., z, za, ..., zzz rebutant à maintenir).

CORSS APPLY fonctionne comme ça et pour ça (îl ne suffit pas d'être prof et MVP pour être à jamais dispensé de se tromper) et M$ ne s'est pas foulé pour rien.

Bref à tous les débutants qui auraient peur de cet opérateur : ne faite pas attention aux Don Quichotte ci-présents. (Cross) apply est là pour vous servir et en bien.
23/04/2010, 09h09
Sergejack

La documentation est très claire : l'opérateur s'applique aux tables.

Microsoft l'explique comme ça, je l'ai compris comme ça, je l'ai utilisé comme ça et vu que ça fonctionne comme ça : ce n'est plus à prouver.
23/04/2010, 09h22
elsuket

C'est bien la peine de signer :

Citation:

Si Microsoft ne brevette ses bugs alors quels brevets méritent-ils vraiment ?!

:mouarf:
23/04/2010, 12h18
Waldar

Citation:

Envoyé par Sergejack

(cross) apply n'a rien a voir avec (cross) join.
Apply permet d'employer par une fonction table ou une sous requête des champs provenant des lignes renvoyées par la ou les tables à la gauche de l'opérateur.

Je ne suis pas spécialement à l'aise avec APPLY (CROSS ou OUTER), je m'en suis déjà servi mais en réutilisant des bouts de code écrits par d'autres.

Néanmoins, si on exclue la partie fonction de table, je ne vois pas de différence entre CROSS JOIN et CROSS APPLY dans votre phrase :

Citation:

CROSS JOIN permet d'employer par une sous requête des champs provenant des lignes renvoyées par la ou les tables à la gauche de l'opérateur.

Le mieux serait d'établir une petite démonstration avec un petit jeu d'essai ! ;)
23/04/2010, 12h31
Sergejack
Sous requête
Champ provenant de la ligne renvoyée par la table à la gauche de l'opérateur
Code:

1 2 3 4 5 SELECT * FROM (SELECT 1) AS Un(un) CROSS JOIN ( SELECT Un.un + 1) AS Deux(deux)
Citation:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "Un.un" could not be bound.
Code:

1 2 3 4 5 6 SELECT * FROM (SELECT 1) AS Un(un) CROSS APPLY ( SELECT Un.un + 1) AS Deux(deux)
Citation:

1 2
23/04/2010, 14h08
elsuket

La façon dont vous avez écrit le CROSS JOIN montre que vous n'avez pas compris ce que cela produit, et par là même la différence avec CROSS APPLY.

@++ ;)
23/04/2010, 14h20
Sergejack
Citation:

Envoyé par elsuket

La façon dont vous avez écrit le CROSS JOIN montre que vous n'avez pas compris ce que cela produit, et par là même la différence avec CROSS APPLY.

@++ ;)

Et pourrais-je avoir un exemple bien illustré de ce que je devrais comprendre d'autre ?

Et si vous êtes prêts à pousser plus loin votre argumentation, pouvez-vous m'écrire le query suivant sans cross apply (pour comparer nos approches respectives du même problème).
Code:

1 2 3 4 5 6 7 8 9 10 11 12 SELECT O.* , OL.* FROM Orders AS O CROSS APPLY ( SELECT TOP(1) OL.* FROM OrderLines AS OL WHERE OL.Order_id = O.Order_id ORDER BY OL.Price DESC ) AS OL

Je pense que Sergejack a raison selon moi ... CROSS APPLY n'a pas tout à fait le même comportement que CROSS JOIN mais j'assimilerais cela plus généralement à une jointure selon le cas.

On peut l'utiliser avec des sous requêtes corrélées ou avec les clauses TOP comme ils le montrent plus haut.

Attention aux performances .. si quelques fois CROSS APPLY est plus pratique et plus rapide ... il y a bien des exemples ou l'opérateur est moins performant ... Les maladresses peuvent vite être rencontrés si l'on ne fait pas gaffe ...

Cependant SergerJack je pense qu'il est possible d'écrire votre requête sans CROSS APPLY : (je n'ai pas pris de jeux de données pour vérifier)
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 SELECT O.*, OL.* FROM Orders AS O INNER JOIN ( SELECT ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY price DESC) AS num, * FROM OrderLines ) AS OL ON O.Order_id = OL.Order.id WHERE OL.num = 1
++

23/04/2010, 14h53
Sergejack

Citation:

Envoyé par mikedavem

Cependant SergerJack je pense qu'il est possible d'écrire votre requête sans CROSS APPLY : (je n'ai pas pris de jeux de données pour vérifier)

Oui, c'est tout à fait possible, d'ailleurs vous l'avez démontré.
On peut aussi trouver une ligne contenant un maximal parmi d'autre en se servant de clauses EXISTS mais toutes ces approches (jusqu'à preuve du contraire) sont moins performante que l'approche avec CROSS APPLY.

Citation:

Envoyé par Sergejack

Oui, c'est tout à fait possible, d'ailleurs vous l'avez démontré.
On peut aussi trouver une ligne contenant un maximal parmi d'autre en se servant de clauses EXISTS mais toutes ces approches (jusqu'à preuve du contraire) sont moins performante que l'approche avec CROSS APPLY.

Je ne suis pas d'accord avec vous sur ce point. Pour preuve et dans le même esprit de la requête que vous avez proposé :

La il s'agit d'un cas classique avec des employés et des ventes.

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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
-- Employees
CREATE TABLE Employees
(
  emp_id int NOT NULL PRIMARY KEY,
  empname varchar(25) NOT NULL,
);
GO

-- Ventes
CREATE TABLE ventes
(
 vente_id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
 emp_id int NOT NULL REFERENCES Employees,
 montant MONEY NOT NULL,
 date DATETIME NOT NULL
);
GO

-- Index sur foreign key emp_id de la table vente
CREATE INDEX idx_emp_id_ventes
ON dbo.ventes
(
 emp_id
);
GO


-- Jeu de données pour les tables Employees et Ventes
INSERT INTO Employees VALUES(1,'Mikedavem');
INSERT INTO Employees VALUES(2,'SergeJack');
INSERT INTO Employees VALUES(3, 'SQLPro');
INSERT INTO Employees VALUES(4,'Elsuket');

INSERT INTO ventes (emp_id, montant, date)
SELECT 1, RAND() * 10.0, DATEADD(dd, -1 * RAND() * 10, GETDATE())
GO 10000

INSERT INTO ventes (emp_id, montant, date)
SELECT 2, RAND() * 10.0, DATEADD(dd, -1 * RAND() * 10, GETDATE())
GO 10000

INSERT INTO ventes (emp_id, montant, date)
SELECT 3, RAND() * 10.0, DATEADD(dd, -1 * RAND() * 10, GETDATE())
GO 10000

INSERT INTO ventes (emp_id, montant, date)
SELECT 4, RAND() * 10.0, DATEADD(dd, -1 * RAND() * 10, GETDATE())
GO 10000

-- Récupération statistiques IO des requêtes
SET STATISTICS IO ON;
GO

-- Test avec CROSS APPLY
SELECT 
	e.empname,
	v.vente_id,
	v.montant,
	v.date
FROM Employees AS e
CROSS APPLY
(
	SELECT TOP(2) vente_id, montant, emp_id, date
	FROM ventes
	WHERE emp_id = e.emp_id
	ORDER BY date DESC
) AS v
GO

-- Test avec ROW_NUMBER() et INNER JOIN
SELECT 
	e.empname,
	v.vente_id,
	v.montant,
	v.date
FROM Employees AS e
INNER JOIN 
(
	SELECT 
		ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY date DESC) AS num,
		vente_id,
		emp_id,
		montant,
		date
	FROM Ventes
) AS v
ON v.emp_id = e.emp_id
WHERE v.num <=2

/* Résultat avec CROSS APPLY
(8 ligne(s) affectée(s))
Table 'ventes'. Nombre d'analyses 4, lectures logiques 664, 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 'Employees'. Nombre d'analyses 1, lectures logiques 2, 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.
*/

/* Résultat avec ROW_NUMBER() ET INNER JOIN
(8 ligne(s) affectée(s))
Table 'ventes'. Nombre d'analyses 1, lectures logiques 166, 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 'Employees'. Nombre d'analyses 1, lectures logiques 2, 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.
*/

Voir 40 message(s) de cette discussion en une page