[2005] Astuce : rapidité query avec constante

**Sergejack** · 20/04/2010, 10h11

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 :

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
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 :

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
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.

**SQLpro** · 20/04/2010, 13h12

As tu essayé le :

Code :

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

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

???

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

A +

**elsuket** · 20/04/2010, 13h56

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 ?

@++

**SQLpro** · 20/04/2010, 22h59

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

A +

**mikedavem** · 20/04/2010, 23h15

Ah oui je viens de tester .. effectivement ça passe ...

**Sergejack** · 22/04/2010, 12h18

Envoyé par SQLpro

As tu essayé le :

Code :

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

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.

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.

**SQLpro** · 22/04/2010, 13h22

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 +

**elsuket** · 22/04/2010, 13h42

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 ?

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 ?

@++

**mikedavem** · 22/04/2010, 14h26

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 :

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
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.
*/

++