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})
4 Réponses :
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.
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.
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.
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
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.
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 uneCROSS APPLY
). Assurez-vous également de vérifier vos index et fragmentation sur la tableMicroframe.dbo.TrackMessages
, assurez-vous qu'il y en a au moins un parIMEI, 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. SiTimestamp
est couvert par un index, vous n'aurez peut-être pas besoin de filtrer par celui-ci, puisque vous renvoyez le dernier enregistrement parTimestamp
. 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 queLAST_VALUE
. Il peut ne pas être plus rapide queTOP 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?