J'ai une table de base de données avec environ 10 colonnes, dont deux sont mois et année. La table a environ 250 000 rangées maintenant et nous nous attendons à ce qu'elle augmente d'environ 100-150 000 enregistrements par mois. Beaucoup de questions impliquent la colonne mensuelle et année (ex, tous les enregistrements de mars 2010), et nous avons donc souvent besoin d'obtenir les combinaisons de mois et d'année disponibles (c'est-à-dire que nous avons des records pour avril 2010?).
Un collègue pense que nous devrions avoir une table séparée de notre principal qui ne contient que les mois et les années que nous avons des données pour. Nous n'ajoutons que des enregistrements à notre table principale une fois par mois. Il s'agirait donc d'une petite mise à jour à la fin de nos scripts pour ajouter la nouvelle entrée à cette deuxième table. Cette deuxième table serait interrogée chaque fois que nous devions trouver les entrées de mois / an disponibles sur la première table. Cette solution me semble kilométrique et une violation du sec.
Que pensez-vous est la bonne façon de résoudre ce problème? Y a-t-il une meilleure façon que d'avoir deux tables?
5 Réponses :
Utilisation d'un simple index sur les colonnes requises (année et mois) devrait améliorer considérablement soit un distinct ou groupe par question. < / p>
Vous voudrez peut-être même envisager d'utiliser Améliorer les performances avec SQL Server 2005 Vues indexées < / a>
J'ai suggéré un indice, mais on m'a dit qu'un groupe distinct / groupe serait toujours lent sur une table avec quelques millions d'enregistrements.
D'accord. La table secondaire est une mauvaise idée - pas seulement d'un point de vue hypothétique "Ceci n'est pas normalisé", mais d'un point de vue des conséquences de maintenance inattendus. Créez un index et être fait avec elle!
Créer une vue indexée matérialisée de: Vous aurez maintenant accès aux valeurs distinctes pré-calculées sans passer au travail à chaque fois.
Cela ajoute une surcharge pour insertion / mise à jour et si la table augmente d'enregistrements de 100k-150k par mois, il s'agira d'un grand nombre de frais généraux. J'aimerais savoir que cette sélection élevée sur ces colonnes n'est pas due à la vérification de la vérification de la ligne avant l'insertion ou la mise à jour.
@Gabriel Guimarães, j'ai répondu en supposant qu'ils avaient l'indice en place et qu'il était encore lent. Cette vue rendra la sélection à peu près instantanée. Cependant, il n'y a pas de déjeuner gratuit, vous obtenez une vitesse de sélection massive pour certains frais d'insertion / mise à jour / Supprimer (150k par mois n'est pas si nombreux par seconde). OP dit qu'elles ont souvent besoin d'obtenir les combinaisons de mois et de l'année disponibles qui utiliseraient ensuite ce point de vue et libéreraient des ressources et éventuellement aider toutes les transactions à écrire sur cette table.
Faites la date à laquelle la première colonne de la clé en clustere de la table. Ceci est très typique des données historiques, car la plupart des questions, sinon toutes, sont intéressées par des plages spécifiques et un index en cluster à temps peut résoudre ce problème. Toutes les questions telles que «mois de mai» doivent être traitées comme des plages, par exemple: Bien que cela semble compliqué pour un esprit de programmeur, il est le moyen optimal d'aborder un problème de conception de base de données. où DateColkey entre '05 / 01/2010 'et '06 / 01/2001' . Répondre à une question comme «Y a-t-il des enregistrements en mai» impliquera une simple recherche dans l'indice en cluster.
Assurez-vous d'avoir un index en cluster sur ces colonnes. et partitionner votre table sur ces colonnes de date et placez les fichiers de données sur différents lecteurs de disques Je crois que garder votre fragmentation d'index faible est votre meilleur tir.
Je crois aussi avoir une vue physique avec la sélection souhaitée n'est pas une bonne idée, parce qu'il ajoute des frais généraux d'insertion / mise à jour. En moyenne, il y a 3,5 inserts par minute. ou environ 17 secondes entre chaque insert (en moyenne, veuillez me corriger si je me trompe)
La question est de choisir plus souvent que toutes les 17 secondes? C'est la pensée clé. J'espère que cela a aidé.
Utilisez une "vue matérialisée", également appelée "vue indexée avec la liaison de schéma", puis indice cette vue. Lorsque vous effectuez ce serveur SQL créera essentiellement et gérerez les données dans une table secondaire dans les coulisses et choisissez d'utiliser l'index sur cette table lorsque cela est approprié.
Ceci est similaire à ce que votre collègue a suggéré, l'avantage étant vous Ne devrez pas ajouter de logique à votre requête pour en profiter, SQL Server le fera lorsqu'il crée un plan de requête et SQL Server conservera automatiquement les données de la vue indexée. P>
voici Comment vous pourriez accomplir cela: Créez une vue qui retourne les valeurs [mois] distinctes [année], puis index [année] [mois] sur la vue. Encore une fois SQL Server utilisera l'index minime à la vue et évitera la balayage de la table sur la grande table. P>
Parce que SQL Server ne vous laissera pas indexer une vue avec le mot clé distinct, utilisez plutôt un groupe par [année]. , [mois] et utilisez big_count (*) dans la sélection. Il ressemblera à quelque chose comme ceci: p> Maintenant, lorsque vous sélectionnez Distinct [Année], [Mois] sur la grande table, l'optimiseur de requête scannera l'index minime sur la vue. au lieu de numériser des millions d'enregistrements sur la grande table. P> CREATE TABLE YourBigTable(
YourBigTableID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_YourBigTable_YourBigTableID PRIMARY KEY,
[Year] INT,
[Month] INT)
GO
CREATE VIEW dbo.vwMonthYear WITH SCHEMABINDING
AS
SELECT
[year],
[month],
COUNT_BIG(*) [MonthCount]
FROM [dbo].[YourBigTable]
GROUP BY [year],[month]
GO
CREATE UNIQUE CLUSTERED INDEX ICU_vwMonthYear_Year_Month ON [dbo].[vwMonthYear](Year,Month)
SELECT DISTINCT
[year],
[month]
FROM YourBigTable
-- Actual execution plan shows SQL server scaning ICU_vwMonthYear_Year_Month
C'est une excellente solution! Peut-être devrait-être être la réponse. Dans mon cas d'utilisation, c'était le billet d'or que l'amélioration des performances 1 000%. Merci!