Discussion :

[Kropernic]

Hello tout le monde. Ca faisait un moment que je n'avais plus mis les pieds ici. J'espère que tout le monde va bien et que tout se passe bien pour cette nouvelle année (ça va être dur de faire pire que 2020 cela dit).

Bref, pour revenir au boulot, je rencontre des problèmes de performance sur une requête. C'est une requête assez complexe (notamment du au fait que la db n'est pas bien modélisée ). Après analyse du plan d'exécution, je vois qu'il y a un point du plan qui prends 85% du boulot. Et quand je regarde ce point, c'est le filtre spatial de la requête.


Dans le prédicat, on voit qu'il y a une expression donc voici la jointure concernée :

Code :

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

1
2
3
            LEFT JOIN    SITES S
                    ON    S.Geofence.STBuffer(50).STIntersects(P.Location) = 1
                    AND S.CompanyId = @COMPANY_ID

L'expression est dû à l'application d'un buffer avant l'opérateur STIntersects. L'alias P est pour la table dbo.Positions.
J'ai créé des index spatiaux suivants :

Code :

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

1
2
3
4
5
CREATE SPATIAL INDEX ISX_SITES_GEOFENCE ON SITES(GEOFENCE);GO
 
 
CREATE SPATIAL INDEX ISX_POSITIONS_LOCATION ON POSITIONS(LOCATION);
GO

Mais cela n'a eu aucun effet sur la requête. J'ai tenté de retirer le buffer en me disant que, comme il y a transformation, ça rend p-e l'index inutilisable... (j'ai aussi tenté d'index la colonne de la table Sites en appliquant le buffer mais SSMS n'avait pas l'air d'en vouloir). Aucun effet non plus.

Voici la requête au complet si jamais c'est nécessaire :

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
 
WITH 
TAG AS (
    SELECT    ToolId, Identifier, Enabled, ROW_NUMBER() OVER (PARTITION BY ToolId ORDER BY ValidationDate DESC) AS 'RN'
    FROM    Tools_Tags
),
POS AS (
    SELECT    TP.ToolId, MAX(P.Date) AS 'DATE', TAG.Identifier
    FROM    Tools_Positions TP
                INNER JOIN Positions P
                    ON    TP.PositionId = P.ID
                INNER JOIN TOOLS T
                    ON    TP.ToolId = T.Id
                    AND T.CompanyId = @COMPANY_ID
                    INNER JOIN TAG
                        ON    T.ID = TAG.ToolId
                        AND TAG.RN = 1
                        AND TAG.Enabled = 1
    GROUP BY
            TP.ToolId,
            Identifier
),
STA AS (
    SELECT    ToolId, Status, ROW_NUMBER() OVER (PARTITION BY ToolId ORDER BY ValidationDate DESC) AS 'RN'
    FROM    Tools_Status 
)
 
 
 
 
SELECT    TOO.ExternalIdentifier, TOO.Model, TOO.TypeId AS 'TYPE', STA.Status AS 'STATUS', POS.DATE, ROUND(P.Location.Lat,5) AS 'LATITUDE', ROUND(P.Location.Long,5) AS 'LONGITUDE', TOO.ManufacturerId,  
        CASE P.Source
            WHEN 'Phone' THEN 'Bluetooth'
            WHEN 'Sigfox' THEN 'Sigfox'
            WHEN 'Gateway' THEN 'Gateway'
            ELSE '-'
        END AS 'Source',
        (
        SELECT    TAU.UserId
        FROM    Tools_AssignedUsers TAU
                    INNER JOIN    (
                                SELECT    ToolId, MAX(ValidationDate) AS 'DATE'
                                FROM    Tools_AssignedUsers
                                WHERE    ToolId = TOO.ID
                                    AND ValidationDate <= POS.DATE
                                GROUP BY
                                        ToolId
                                ) T
                        ON    TAU.ToolId = T.ToolId
                        AND TAU.ValidationDate = T.DATE) AS 'USER_ID',
            S.Name AS 'SITE',
            Identifier
FROM    POS    
            INNER JOIN TOOLS TOO
                ON    POS.ToolId = TOO.Id
                INNER JOIN STA    
                    ON    TOO.Id = STA.ToolId
                    AND STA.RN = 1
            INNER JOIN Tools_Positions TP
                ON    POS.ToolId = TP.ToolId
                INNER JOIN Positions P
                    ON    TP.PositionId = P.Id
                    AND P.Date = POS.DATE
            LEFT JOIN    SITES S
                    ON    S.Geofence.STBuffer(50).STIntersects(P.Location) = 1
                    AND S.CompanyId = @COMPANY_ID
ORDER BY 
        POS.DATE DESC

Je sais bien que la charte dit qu'il faut donner la définition des tables mais j'ai un peu honte de poster ça... J'hérite du machin et niveau normalisation, on repassera (une nouvelle version de l'application est en cours de développement mais en attendant, faut faire avec).
Si vraiment on sait pas faire sans ça, je les mettrai...

Je vous mets le plan d'exécution complet en pièce jointe.
sql_plan.xml

Voilà, je ne vois pas trop quoi dire d'autre...

S'il faut des informations complémentaires, surtout n'hésitez pas. En attendant, je continue à chercher/expérimenter.

Merci d'avance,

Kropernic.

[SQLpro]

Quelle version/edition de SQL Server ?
Quel type de données GEOGRAPHY ou GEOMETRY ?

A +

[Kropernic]

Hello.

Le datatype est geography. Je pensais l'avoir précisé, sans doute un oubli.

Pour la version, voici le résultat de

Code :

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

SELECT @@VERSION

Microsoft SQL Azure (RTM) - 12.0.2000.8 Oct 1 2020 18:48:35 Copyright (C) 2019 Microsoft Corporation

[SQLpro]

essaye avec STOverlaps

A +

[Kropernic]

Je pensais que ça allait mieux mais faux espoir (j'suis sur plusieurs queries en même temps et je n'avais pas regarder la bonne ).

Ca ne change rien au niveau du plan si j'utilises STIntersects ou STOverlaps (mais pour une raison que j'ignore, aujourd'hui la tâche que j'avais pointée du doigt n'est plus qu'à 59% du process).

Ca ne change rien non plus au niveau du temps d'exécution (40 secondes).