3
votes

Comment convertir une sous-requête à rejoindre pour un résultat rapide?

Je souhaite convertir les sous-requêtes en jointures pour améliorer les performances.

Les sous-requêtes suivantes prennent trop de temps à se charger.

SELECT c.tank_name, c.fuel_type, c.capacity, c.tank_id,
     (SELECT TOP 1 b.Level 
      from Microframe.dbo.TrackMessages b 
      where b.IMEI = a.IMEI 
            AND b.Timestamp >= @Start 
      order by b.Timestamp ) AS Level,
    (select top 1 b.Timestamp 
     from Microframe.dbo.TrackMessages b 
     where b.IMEI = a.IMEI 
           AND b.Timestamp >= @Start 
     order by b.Timestamp ) AS TimeStamp,
    (SELECT top 1 b.Temp 
     from Microframe.dbo.TrackMessages b 
     where b.IMEI = a.IMEI 
           AND b.Timestamp >= @Start 
     order by b.Timestamp ) AS Temp 
FROM GatexServerDB.dbo.device as a
JOIN GatexReportsDB.dbo.tbl_static_tank_info as c ON c.tank_id = a.owner_id
WHERE c.client_id = 65
AND a.IMEI IS NOT NULL
AND c.tank_id IN ({Tanks})

c# sql-server inner-join join database-performance

5 commentaires

Vous effectuez la même sous-requête corrélée 3 fois différentes. Utilisez un CTE pour récupérer les 3 colonnes par IMEI (ou une CROSS APPLY ). Assurez-vous également de vérifier vos index et fragmentation sur la table Microframe.dbo.TrackMessages , assurez-vous qu'il y en a au moins un par IMEI, horodatage dans cet ordre.

Vous essayez de récupérer le même enregistrement TrackMessages plusieurs fois. Ajoutez une seule sous-requête dans la clause FROM qui renvoie les champs Level, Timestamp, Temp. Si Timestamp est couvert par un index, vous n'aurez peut-être pas besoin de filtrer par celui-ci, puisque vous renvoyez le dernier enregistrement par Timestamp . Vous devrez vérifier le plan d'exécution réel

Étant donné que vous semblez traiter des données IoT, vous devez également vérifier les fonctions de classement telles que ROW_NUMBER , les fonctions de fenêtrage et les fonctions analytiques telles que LAST_VALUE . Il peut ne pas être plus rapide que TOP 1 ORDER BY dans ce cas, mais il peut être utilisé dans de nombreux autres cas

@PanagiotisKanavos, pouvez-vous décrire ici en détail ou vous pouvez écrire une requête à votre manière pour décrire mon résultat?

@EzLo CTE Retrive s'il vous plaît me guider?

4 Réponses :

0
votes

Voici une solution avec CROSS APPLY qui est comme une fonction que vous pouvez déclarer lors de vos déplacements et l'utiliser comme clause de jointure. Vous pouvez changer CROSS APPLY en OUTER APPLY si l'ensemble renvoyé peut ne pas exister, dans ce cas s'il n'y a pas d'enregistrement sur TrackMessages pour un IMEI particulier (renverra des valeurs NULL ).

CREATE NONCLUSTERED INDEX NCI_TrackMessages_IMEI_TimeStamp ON Microframe.dbo.TrackMessages (IMEI, Timestamp)

Cependant, je crois que le point clé ici serait les index sur vos tables. Si vous êtes déjà sûr que le problème est la sous-requête, assurez-vous que TrackMessages a l'index suivant:

SELECT 
    c.tank_name, 
    c.fuel_type, 
    c.capacity, 
    c.tank_id,

    T.Level,
    T.Timestamp,
    T.Temp

FROM GatexServerDB.dbo.device as a
JOIN GatexReportsDB.dbo.tbl_static_tank_info as c ON c.tank_id = a.owner_id
CROSS APPLY (

    SELECT TOP 1 -- Retrieve only the first record

        -- And return as many columns as you need
        b.Level,
        b.Timestamp,
        b.Temp
    FROM
        Microframe.dbo.TrackMessages AS b
    WHERE
        a.IMEI = b.IMEI AND -- With matching IMEI
        b.Timestamp >= @Start
    ORDER BY
        b.Timestamp) T -- Ordered by Timestamp

WHERE c.client_id = 65
AND a.IMEI IS NOT NULL
AND c.tank_id IN ({Tanks})

Les index ont des avantages et des inconvénients, assurez-vous de les vérifier avant d'en créer ou d'en supprimer un.

0 commentaires

0
votes

N'ayant pas les structures, mes suggestions pour la solution sont:

WITH CTE AS 
    (SELECT B.IMEI,
            b.Level, 
            b.Timetamp,
            b.Temp,
            ROW_NUMBER() OVER (PARTITION BY b.IMEI ORDER BY Timestamp) AS Row
      FROM Microframe.dbo.TrackMessages b 
      WHERE b.Timestamp >= @Start 
    )
SELECT c.tank_name, c.fuel_type, c.capacity, c.tank_id,
    CTE.Level, CTE.Timestamp, CTE.Temp
FROM GatexServerDB.dbo.device as a
INNER JOIN GatexReportsDB.dbo.tbl_static_tank_info as c ON c.tank_id = a.owner_id
INNER JOIN CTE ON  CTE.IMEI = a.IMEI 
WHERE c.client_id = 65
  AND a.IMEI IS NOT NULL
  AND c.tank_id IN ({Tanks})
  AND CTE.Row = 1;

Je ne peux pas la tester mais elle devrait être très proche de la solution. Veuillez confirmer si cela fonctionne.

1 commentaires

Vous devez ajouter un filtre sur CTE.Row = 1 sur WHERE pour vous assurer que vous obtenez juste 1 ligne et pas tous les messages de suivi. Vous devez également supprimer le ORDER BY sur CTE.

0
votes

Vous pouvez comparer et utiliser l'une des solutions ci-dessous

La méthode JOIN où la commande est effectuée via la fonction de fenêtrage des numéros de ligne

SELECT * FROM 
(
    SELECT 
        c.tank_name, c.fuel_type, c.capacity, c.tank_id
    FROM GatexServerDB.dbo.device as a
        JOIN GatexReportsDB.dbo.tbl_static_tank_info as c 
            ON c.tank_id = a.owner_id
            AND c.client_id = 65
            AND a.IMEI IS NOT NULL
            AND c.tank_id IN ({Tanks})
) A
CROSS APPLY 
(
    SELECT 
        TOP 1 
        b.Level, b.Timestamp,b.Temp 
    FROM Microframe.dbo.TrackMessages b
    WHERE b.IMEI = a.IMEI 
        AND b.Timestamp >= @Start 
    ORDER BY b.Timestamp 
)D

ou la méthode CROSS APPLY comme ci-dessous

SELECT * FROM 
(
    SELECT 
        c.tank_name, 
        c.fuel_type, 
        c.capacity, 
        c.tank_id,
        Level=b.Level,
        TimeStamp=b.Timestamp,
        Temp=b.Temp,
        r=Row_number() over ( order by b.timestamp)
    FROM GatexServerDB.dbo.device as a
        JOIN GatexReportsDB.dbo.tbl_static_tank_info as c 
            ON c.tank_id = a.owner_id
        JOIN Microframe.dbo.TrackMessages as b 
            ON b.IMEI = a.IMEI AND b.Timestamp >= @Start 
    WHERE c.client_id = 65
    AND a.IMEI IS NOT NULL
    AND c.tank_id IN ({Tanks})
)T
where r=1

0 commentaires

1
votes

Vous pouvez déplacer la sous-requête vers la clause FROM et utiliser CROSS APPLY . Puisque vous semblez avoir affaire à des données IoT, vous devriez étudier les fonctions de classement, de fenêtrage et d'analyse de T-SQL. Les performances dépendront fortement des index de la table.

Compte tenu de ces tables:

SELECT c.tank_name, c.fuel_type, c.capacity, c.tank_id,
        first_value(Temp) over (partition by b.imei order by timestamp) as StartTemp,
        first_value(Level) over (partition by b.imei order by timestamp) as StartLevel,
        min(timestamp)  over (partition by b.imei) as StartTime,
        last_value(Temp) over (partition by b.imei order by timestamp) as EndTemp,
        lastt_value(Level) over (partition by b.imei order by timestamp) as EndLevel,
        max(timestamp)  over (partition by b.imei) as EndTime   
from #TrackMessages b 
    inner join #device as a on b.IMEI = a.imei
    inner JOIN #tbl_static_tank_info as c ON c.tank_id = a.owner_id
WHERE c.client_id = 65
AND a.IMEI IS NOT NULL
AND c.tank_id IN (1,5,7)

Et des index:

SELECT c.tank_name, c.fuel_type, c.capacity, c.tank_id,
        first_value(Temp) over (partition by b.imei order by timestamp) as temp,
        first_value(Level) over (partition by b.imei order by timestamp) as level,
        min(timestamp)  over (partition by b.imei) as timestamp
from #TrackMessages b 
    inner join #device as a on b.IMEI = a.imei
    inner JOIN #tbl_static_tank_info as c ON c.tank_id = a.owner_id
WHERE c.client_id = 65
AND a.IMEI IS NOT NULL
AND c.tank_id IN (1,5,7)

Le La requête TOP 1 ressemblerait à ceci:

SELECT c.tank_name, c.fuel_type, c.capacity, c.tank_id,
     Level,
    TimeStamp,
    Temp 
FROM #device as a
inner JOIN #tbl_static_tank_info as c ON c.tank_id = a.owner_id
cross apply (SELECT top 1 imei,Temp,Level,timestamp 
            from #TrackMessages b 
            where b.IMEI = a.imei
           AND b.Timestamp >= @start 
     order by b.Timestamp ) msg 
WHERE c.client_id = 65
AND a.IMEI IS NOT NULL
AND c.tank_id IN (1,5,7)

S'il existe une relation 1-M entre les réservoirs, les appareils et les messages, le FIRST_VALUE a > la fonction analytique peut être utilisée pour renvoyer le premier périphérique d'enregistrement, sans utiliser de sous-requête:

create nonclustered index IX_MSG_IMEI_Time on #TrackMessages (imei,timestamp) include(level,temp)       ;
create INDEX IX_Device_OwnerID on #device (Owner_ID)
create INDEX IX_Tank_Client on #tbl_static_tank_info (Client_ID);
create INDEX IX_Tank_Owner  on #tbl_static_tank_info (Owner_ID);

Les performances dépendront fortement des index, des statistiques de la table et si l'index et La commande OVER correspond.

Cette requête peut être modifiée pour renvoyer à la fois la première et la dernière valeur par appareil en utilisant LAST_VALUE :

create table #TrackMessages (
    Message_ID bigint primary key,
    imei nvarchar(50) ,
    [timestamp] datetime2,
    Level int,
    temp numeric(5,2)
);

create table #device (
    imei nvarchar(50) primary key,
    owner_id int        
);


create table #tbl_static_tank_info (
    tank_id int not null primary key,
    tank_name nvarchar(20),
    fuel_type nvarchar(20),
    capacity numeric(9,2),
    owner_id int,
    client_id int
 )

le serveur devrait trier les mesures à la fois par ordre d'horodatage croissant (c'est ce que fait déjà l'index IX_MSG_IMEI_Time) et par ordre décroissant.

0 commentaires