IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Développement SQL Server Discussion :

Optimisation de requête : filtre spatial


Sujet :

Développement SQL Server

  1. #1
    Expert confirmé
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    juillet 2006
    Messages
    3 925
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 38
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : juillet 2006
    Messages : 3 925
    Points : 4 221
    Points
    4 221
    Par défaut Optimisation de requête : filtre spatial
    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.
    Nom : sql_plan.png
Affichages : 36
Taille : 27,5 Ko

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

  2. #2
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 473
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 473
    Points : 48 328
    Points
    48 328
    Par défaut
    Quelle version/edition de SQL Server ?
    Quel type de données GEOGRAPHY ou GEOMETRY ?

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  3. #3
    Expert confirmé
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    juillet 2006
    Messages
    3 925
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 38
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : juillet 2006
    Messages : 3 925
    Points : 4 221
    Points
    4 221
    Par défaut
    Hello.

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

    Pour la version, voici le résultat de
    Microsoft SQL Azure (RTM) - 12.0.2000.8 Oct 1 2020 18:48:35 Copyright (C) 2019 Microsoft Corporation
    Kropernic

  4. #4
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    mai 2002
    Messages
    20 473
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Paris (Île de France)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 20 473
    Points : 48 328
    Points
    48 328
    Par défaut
    essaye avec STOverlaps

    A +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  5. #5
    Expert confirmé
    Avatar de Kropernic
    Homme Profil pro
    Analyste / Programmeur / DBA
    Inscrit en
    juillet 2006
    Messages
    3 925
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 38
    Localisation : Belgique

    Informations professionnelles :
    Activité : Analyste / Programmeur / DBA
    Secteur : Distribution

    Informations forums :
    Inscription : juillet 2006
    Messages : 3 925
    Points : 4 221
    Points
    4 221
    Par défaut
    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).
    Kropernic

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [Access] Optimisation performance requête - Index
    Par fdraven dans le forum Access
    Réponses: 11
    Dernier message: 12/08/2005, 15h30
  2. Optimisation de requête avec Tkprof
    Par stingrayjo dans le forum Oracle
    Réponses: 3
    Dernier message: 04/07/2005, 10h50
  3. Optimiser une requête SQL d'un moteur de recherche
    Par kibodio dans le forum Langage SQL
    Réponses: 2
    Dernier message: 06/03/2005, 21h55
  4. optimisation des requêtes
    Par yech dans le forum PostgreSQL
    Réponses: 1
    Dernier message: 21/09/2004, 20h03
  5. Optimisation de requête
    Par olivierN dans le forum SQL
    Réponses: 10
    Dernier message: 16/12/2003, 11h09

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo