Nous avons un certain nombre de machines qui enregistrent des données dans une base de données à intervalles sporadiques. Pour chaque enregistrement, j'aimerais obtenir la période comprise entre cet enregistrement em> em> enregistrement précédent em> em> je peux faire cela en utilisant Row_Number comme suit: P > WITH TempTable AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY Machine_ID ORDER BY Date_Time) AS Ordering
FROM dbo.DataTable
)
SELECT [Current].*, Previous.Date_Time AS PreviousDateTime
FROM TempTable AS [Current]
INNER JOIN TempTable AS Previous
ON [Current].Machine_ID = Previous.Machine_ID
AND Previous.Ordering = [Current].Ordering + 1
6 Réponses :
La partition et la commande Row_Number () données nécessitent un index sur index séparé sur machine_id et date_time aidera peu, le cas échéant. (machine_id, date_time) Pour satisfaire en une seule passe:
Comme je l'ai dit, j'ai également créé cet indice et cela n'a pas du tout amélioré le rendement de la requête.
En effet, votre * déclenche le point de basculement d'index. Limitez-la à seulement les colonnes nécessaires et l'utilisation incluent le couvrant l'indice non groupé. Si trop de colonnes sont nécessaires, il faut alors être remplacé par un index en cluster, avec toutes les conséquences.
Vous semblez être correct, enlever le * diminue le temps de la requête à peine quelques secondes. Je ne peux pas envisager pourquoi cela se produirait - pourriez-vous fournir des liens de quel point de basculement index est?
Eh bien maintenant, vous connaissez l'une des raisons pour lesquelles Select * ne devrait pas être dans une requête de production. De plus, vous avez des jointures afin que vous retourniez des colonnes inutiles par définition.
+1 Même si cet indice n'aurait peut-être pas aidé le scénario d'orchestre due à la structure de la requête, il améliore généralement la performance de la clause.
De plus, je m'assure que la deuxième colonne de l'indice correspond à ASC / DESC basée sur la commande par la clause Over. Donc, l'indice aurait été `(machine_id, date_time desc)` si sur (... commande par date_time desc) . Je ne sais pas si c'est important, mais je le fais, cela correspond donc au tri dans le plan de requête.
Et si vous utilisez une gâchette pour stocker le dernier horodatage, un soustraire à chaque fois pour faire la différence?
Malheureusement, ce sont des données historiques et non toujours ajoutées dans l'ordre.
J'ai eu des problèmes de performances étranges à l'aide de CTES dans SQL Server 2005. Dans de nombreux cas, le remplacement du CTE avec une table de Temps réelle a résolu le problème.
Je voudrais essayer cela avant d'aller plus loin avec l'utilisation d'un CTE.
Je n'ai trouvé jamais d'explication des problèmes de performance que j'ai vus et n'avaient vraiment pas le temps de creuser dans les causes profondes. Cependant, j'ai toujours soupçonné que le moteur ne puisse pas optimiser le CTE de la même manière qu'il peut optimiser une table Temp (qui peut être indexé si plus d'optimisation est nécessaire).
Après votre commentaire selon lequel c'est une vue, je voudrais d'abord tester la requête avec une table Temp pour voir si cela fonctionne mieux.
Si tel est le cas, et que vous utilisez un PROP stocké n'est pas une option, vous pouvez envisager de créer le CTE actuel dans une vue indexée / matérialisée. Vous voudrez lire sur le sujet avant de descendre cette route, car il s'agissait d'une bonne idée dépend de nombreux facteurs, et non le moindre dont la fréquence est la fréquence de mise à jour.
Comment ferais-je cela? Aurais-je besoin de remplacer la vue avec une SPTP (car les vues ne peuvent pas avoir de variables)?
Oui, je n'étais pas clair que c'était une vue de votre question. Voir la mise à jour de ma réponse (suivra dans quelques minutes).
Si vous avez besoin de ces données souvent, plutôt que de le calculer chaque fois que vous tirez les données, pourquoi ne pas ajouter de colonne et calculer / le remplacer chaque fois que la ligne est ajoutée?
(Index composé de Remus 'rendra la requête rapide; l'exécution qu'une seule fois devrait le rendre plus rapide encore.)
Si le nombre de lignes dans dbo.Datables est grand, il est probable que vous rencontriez le problème en raison de l'auto-adhésif CTE sur elle-même. Il y a un article de blog expliquant le problème dans certains détails ICI
occasionnellement dans de tels cas, j'ai eu recours à la création d'une table temporaire pour insérer le résultat de la requête CTE dans puis effectuer les jointures contre ce tableau temporaire (bien que cela soit généralement pour les cas où un grand nombre de jointures contre le TEMP Les table sont requises - dans le cas d'une seule jointure, la différence de performance sera moins notable)
Je seconde cette approche. Les CTES sont simplement des réécrites en ligne. Tout comme répéter votre propre code et votre auto-jointure, rien ne garantit que l'optimiseur la tire dans une table temporaire. Si vous mettez des objets dans votre propre table, vous pouvez choisir des index et / ou éviter le double travail. Cela dit, j'utilise des CTES où la maintenance du code est importante et où le schéma est susceptible de changer très rapidement (ou dans des vues, comme ce cas).
Comment se compare-t-il à cette version?:
SELECT x.*
,triang_join.PreviousDateTime
FROM dbo.DataTable AS x
INNER JOIN (
SELECT l.Machine_ID, l.Date_Time, MAX(r.Date_Time) AS PreviousDateTime
FROM dbo.DataTable AS l
LEFT JOIN dbo.DataTable AS r
ON l.Machine_ID = r.Machine_ID
AND l.Date_Time > r.Date_Time
GROUP BY l.Machine_ID, l.Date_Time
) AS triang_join
ON triang_join.Machine_ID = x.Machine_ID
AND triang_join.Date_Time = x.Date_Time
La deuxième requête se termine en quelques secondes plutôt que quelques minutes, mais la première requête s'exécute plus rapidement que possible. Parfait merci!