Quelle est la meilleure façon de stocker l'historique des données dans SQL Server 2005/2008?
Mon simplifié et artificiel exemple est le suivant:-
Permet de dire que je veux mesurer et stocker la température (et d'autres valeurs) de tous les mondes " villes sur une base quotidienne. Je suis à la recherche d'une meilleure façon de stocker les données de sorte qu'il est tout aussi facile d'obtenir la température actuelle dans toutes les villes, comme il est d'obtenir tous les température historiquement dans une ville.
Il est assez facile de problème à résoudre, mais je suis à la recherche de la meilleure solution.
Les 2 principales options que je peux penser sont comme suit:-
Option 1 - de la Même table stocke les dossiers actuels et antérieurs
Stocker toutes les actuels et les documents d'archives dans la même table.
c'est à dire
CREATE TABLE [dbo].[WeatherMeasurement](
MeasurementID [int] Identity(1,1) NOT Null,
TownID [int] Not Null,
Temp [int] NOT Null,
Date [datetime] NOT Null,
)Cela permettrait de garder tout simple, mais ce qui serait le plus efficace de requête pour obtenir une liste des villes et il y en température actuelle? Serait-ce à l'échelle une fois que la table a des millions de lignes? Il n'y a rien à gagner en ayant une sorte de IsCurrent drapeau dans le tableau?
Option 2 - Stocker toutes les archive des enregistrements dans une table séparée
Il y aurait une table pour stocker le courant en direct des mesures dans
CREATE TABLE [dbo].[WeatherMeasurement](
MeasurementID [int] Identity(1,1) NOT Null,
TownID [int] Not Null,
Temp [int] NOT Null,
Date [datetime] NOT Null,
)Et une table pour stocker l'historique archivé date (inséré par un déclencheur peut-être)
CREATE TABLE [dbo].[WeatherMeasurementHistory](
MeasurementID [int] Identity(1,1) NOT Null,
TownID [int] Not Null,
Temp [int] NOT Null,
Date [datetime] NOT Null,
)Cela a l'avantage de garder le principal courant de données maigre, et très efficace pour la requête, à la charge de rendre le schéma plus complexe et de l'insertion des données plus cher.
Qui est la meilleure option? Sont t-il de meilleures options que je n'ai pas mentionné?
NOTE: j'ai simplifié le schéma pour l'aider à se concentrer ma question mieux, mais à supposer qu'il y aura beaucoup de données insérées chaque jour (de 100 000 s de dossiers), et les données sont en cours pour une journée. Les données actuelles sont tout aussi susceptibles d'être interrogé comme l'historique.
- prenez vos deux options et de faire d'eux des réponses afin que nous puissions voter
Vous devez vous connecter pour publier un commentaire.
il DÉPEND des applications des modèles d'utilisation... Si l'utilisation des modèles indiquent que les données historiques va être interrogé plus souvent que les valeurs actuelles, puis de les mettre tous dans un tableau... Mais si l'Historique des requêtes sont l'exception (ou de moins de 10% des requêtes), et la performance de la plus commune de la valeur actuelle de la requête à souffrir de mettre toutes les données dans un tableau, alors il est logique de séparer les données dans une table...
Je voudrais conserver les données dans un tableau sauf vous avez un sérieux penchant pour les données courantes (en utilisation) ou de l'historique des données (en volume). Un index composé avec DATE + TOWNID (dans cet ordre) permettrait de lever le souci de performances dans la plupart des cas (bien que clairement nous n'avons pas les données pour être sûr de ce à cette époque).
La seule chose que je me demande est si personne ne souhaitez que des données provenant à la fois le courant et les données d'historique d'une ville. Si oui, vous venez de créer au moins un nouveau point de vue à vous soucier de et possible problème de performances dans cette direction.
C'est malheureusement une de ces choses où vous avez besoin de profil de vos solutions contre les données du monde réel. Personnellement, j'ai utilisé l'index composés tels que spécifiés ci-dessus, dans de nombreux cas, et pourtant, il y a quelques cas où j'ai choisi de rompre l'histoire dans une autre table. Eh bien, en réalité, un autre fichier de données, parce que le problème a été que l'histoire a été donc dense que j'ai créé un nouveau fichier de données pour le seul pour éviter les ballonnements l'ensemble du fichier de données primaire ensemble. Les problèmes de performances sont rarement résolus par la théorie.
Je vous recommande de lire sur les indicateurs de requête pour l'indice d'utilisation, et "couvrant l'index" pour plus d'informations sur les problèmes de performances.
Votre table est très étroite et il serait probablement effectuer en une seule correctement indexé tableau qui ne serait jamais dépasser la capacité de SQL Server dans un traditionnel normalisé OLTP modèle, même pour des millions et des millions de lignes. Même avec la double-modèle de table, les avantages peuvent être atténués en utilisant le partitionnement de table dans SQL Server. Donc il n'a pas à recommander sur le seul modèle de table. Ce serait un Inmon de style ou de l'Entreprise "Entrepôt de Données"- scénario.
Dans beaucoup plus de scénarios, je voudrais transférer les données vers un entrepôt de données (modélisé avec un Kimball style dimensions du modèle) sur une base régulière et simplement purger les données en direct - dans certains scénarios simples comme la vôtre, il peut effectivement être PAS des données en direct - tout va directement dans l'entrepôt. Les dimensions du modèle a beaucoup d'avantages pour le découpage de données de différentes façons et de stocker un grand nombre de faits avec une variété de dimensions. Même dans le scénario d'entrepôt de données, souvent fait les tables sont séparées par jour.
Il peut ne pas sembler comme vos données a cette (Ville et la Date sont votre seule explicite dimensions), cependant, dans la plupart des entrepôts de données, les dimensions peuvent flocon de neige ou il peut y avoir redondance, donc il y aurait d'autres dimensions sur le fait stockées au moment de la charge au lieu de snowflaking pour plus d'efficience - comme l'État, Code Postal, WasItRaining, IsStationUrban (fictive).
Cela peut paraître idiot, mais quand vous commencez à examiner les données pour les résultats dans les entrepôts de données, ce qui rend poser des questions du genre - sur une journée avec de la pluie en milieu urbain, quelle a été la température moyenne dans le Maine? - juste un peu plus facile à obtenir sans adhérer à tout un tas de tables (c'est à dire qu'il ne nécessite pas beaucoup d'expertise sur votre modèle normalisé et effectue très rapidement). Un peu comme inutiles, les statistiques de baseball, mais apparemment certains s'avérer utile.
Je suggère de garder dans la même table depuis l'historique des données est interrogée comme souvent. À moins que vous ne l'ajout de beaucoup plus de colonnes de la table.
Lorsque la taille devient un problème, vous pouvez partition par décennie, et ont une procédure stockée à l'union les lignes demandées.
Une autre alternative pourrait être d'aller pour un tableau de l'ensemble des données et avoir un affichage de la température actuelle. Cela n'aidera pas les performances, mais pourrait bien que l'aide de la lisibilité/maintenabilité. On pourrait même aller pour une vue indexée à améliorer les performances si vous avez la bonne version de sql .
Je voudrais utiliser une seule table avec des vues d'index pour me fournir les informations les plus récentes. SQL 2005 et 2008 serveur sont conçus pour l'entreposage de données devrait donc préforme bien sous cette condition.
Si vous avez un modèle de données qui nécessite l'écriture de la db souvent, le meilleur choix serait d'avoir une table active et la table d'archive que vous avez mise à jour par lot à un certain intervalle.
Si vous stockez le tout dans un tableau, comment allez-vous faire une base de données relationnelle.
Exemple:
id--------------GUID----PK
record_id-------GUID
chaque fois qu'un nouvel enregistrement sera inséré le [id] va changer, mais [record_id] restera la même. Maintenant, si vous avez un lien avec l'adresse de la table, comment allez-vous faire?