Performances des colonnes calculées dans SQL Server

La traduction de l'article a été préparée spécialement pour les étudiants du cours "MS SQL Server Developer" .










Les colonnes calculées peuvent entraßner des problÚmes de performances difficiles à diagnostiquer. Cet article décrit un certain nombre de problÚmes et des moyens de les résoudre.



Les colonnes calculées sont un moyen pratique d'incorporer des calculs dans les définitions de table. Mais ils peuvent entraßner des problÚmes de performances, d'autant plus que les expressions deviennent plus complexes, que les applications deviennent plus exigeantes et que les volumes de données continuent d'augmenter.



Une colonne calculĂ©e est une colonne virtuelle dont la valeur est calculĂ©e en fonction des valeurs des autres colonnes du tableau. Par dĂ©faut, la valeur calculĂ©e n'est pas stockĂ©e physiquement, mais SQL Server la calcule Ă  chaque demande de colonne. Cela augmente la charge sur le processeur, mais rĂ©duit la quantitĂ© de donnĂ©es qui doivent ĂȘtre conservĂ©es lorsque la table change.



Les colonnes calculĂ©es non persistantes sont souvent gourmandes en ressources processeur, ralentissent les requĂȘtes et bloquent les applications. Heureusement, SQL Server propose plusieurs mĂ©thodes pour amĂ©liorer les performances des colonnes calculĂ©es. Vous pouvez crĂ©er des colonnes calculĂ©es persistantes, les indexer ou faire les deux.



Pour la dĂ©monstration, j'ai crĂ©Ă© quatre tables similaires et les ai remplies avec des donnĂ©es identiques de la base de donnĂ©es de dĂ©monstration WideWorldImporters. Chaque table a la mĂȘme colonne calculĂ©e, mais deux tables la conservent et deux ont un index. Le rĂ©sultat est les options suivantes:



  • La table Orders1est une colonne calculĂ©e non enregistrĂ©e.
  • La table Orders2est une colonne calculĂ©e persistante.
  • La table Orders3est une colonne calculĂ©e non persistante avec un index.
  • Table Orders4est une colonne calculĂ©e persistante avec un index.


L'expression calculĂ©e est assez simple et l'ensemble de donnĂ©es est trĂšs petit. Cependant, il devrait ĂȘtre suffisant de dĂ©montrer les principes des colonnes calculĂ©es persistantes et indexĂ©es et comment cela aide Ă  rĂ©soudre les problĂšmes de performances.



Colonne calculée non enregistrée



Peut-ĂȘtre que dans votre situation, vous souhaiterez peut-ĂȘtre que des colonnes calculĂ©es non persistantes Ă©vitent de stocker des donnĂ©es, de crĂ©er des index ou de les utiliser avec une colonne non dĂ©terministe. Par exemple, SQL Server traitera une UDF scalaire comme non dĂ©terministe si WITH SCHEMABINDING est absent de la dĂ©finition de fonction. Si vous essayez de crĂ©er une colonne calculĂ©e persistante Ă  l'aide de cette fonction, vous obtiendrez une erreur indiquant que la colonne persistante ne peut pas ĂȘtre crĂ©Ă©e.



Cependant, il convient de noter que les fonctions personnalisĂ©es peuvent crĂ©er leurs propres problĂšmes de performances. Si la table contient une colonne calculĂ©e avec une fonction, le moteur de requĂȘte n'utilisera pas la concurrence (sauf si vous utilisez SQL Server 2019). MĂȘme dans une situation oĂč la colonne calculĂ©e n'est pas spĂ©cifiĂ©e dans la requĂȘte. Pour un ensemble de donnĂ©es volumineux, cela peut avoir un impact important sur les performances. Les fonctions peuvent Ă©galement ralentir l'exĂ©cution des UPDATE et affecter la façon dont l'optimiseur calcule le coĂ»t d'une requĂȘte sur une colonne calculĂ©e. Cela ne signifie pas que vous ne devez jamais utiliser de fonctions sur une colonne calculĂ©e, mais cela doit certainement ĂȘtre traitĂ© avec prudence.



Que vous utilisiez des fonctions ou non, la création d'une colonne calculée non persistante est assez simple. Instruction suivanteCREATE TABLEdéfinit une table Orders1qui comprend une colonne calculée Cost.



USE WideWorldImporters;
GO
DROP TABLE IF EXISTS Orders1;
GO
CREATE TABLE Orders1(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit));

INSERT INTO Orders1 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;


Pour définir une colonne calculée, spécifiez son nom suivi du mot clé et de l'expression AS. Dans notre exemple, nous multiplions Quantitypar Priceet soustraction Profit. AprÚs avoir créé la table, nous la remplissons avec INSERT en utilisant les données de Sales.InvoiceLinesla table de base de données WideWorldImporters. Ensuite, nous exécutons SELECT.



SELECT ItemID, Cost FROM Orders1 WHERE Cost >= 1000;


Cette requĂȘte doit renvoyer 22 973 lignes ou toutes les lignes que vous avez dans la base de donnĂ©es WideWorldImporters. Le plan d'exĂ©cution de cette requĂȘte est illustrĂ© Ă  la figure 1.





Figure 1. Le plan d'exĂ©cution de la requĂȘte par rapport Ă  la table Orders1



La premiĂšre chose Ă  noter est l'analyse d'index en cluster, qui n'est pas un moyen efficace d'obtenir les donnĂ©es. Mais ce n'est pas le seul problĂšme. Jetons un coup d'Ɠil au nombre de lectures logiques (lectures logiques rĂ©elles) dans les propriĂ©tĂ©s de l'analyse d'index en cluster (voir Figure 2).





Figure 2. Lectures logiques pour interroger la table Orders1



Le nombre de lectures logiques (dans ce cas 1108) est le nombre de pages qui ont été lues à partir du cache de données. Le but est d'essayer de réduire ce nombre autant que possible. Par conséquent, il est utile de s'en souvenir et de le comparer avec d'autres options.



Le nombre de lectures logiques peut Ă©galement ĂȘtre obtenu en exĂ©cutant l'instruction SET STATISTICS IO ONavant d'exĂ©cuter SELECT. Pour afficher le processeur et le temps total - SET STATISTICS TIME ONou afficher les propriĂ©tĂ©s de l'instruction SELECT dans le plan d'exĂ©cution de la requĂȘte.



Un autre point Ă  noter est qu'il existe deux instructions Compute Scalar dans le plan d'exĂ©cution. Le premier (celui de droite) est le calcul de la valeur de colonne calculĂ©e pour chaque ligne renvoyĂ©e. Étant donnĂ© que les valeurs de colonne sont calculĂ©es Ă  la volĂ©e, vous ne pouvez pas Ă©viter cette Ă©tape avec des colonnes calculĂ©es non persistantes, sauf si vous crĂ©ez un index sur cette colonne.



Dans certains cas, une colonne calculée non persistante fournit les performances requises sans la stocker ni utiliser un index. Cela économise non seulement de l'espace de stockage, mais évite également la surcharge associée à la mise à jour des valeurs calculées dans une table ou un index. Cependant, le plus souvent, une colonne calculée non persistante entraßne des problÚmes de performances, et vous devriez alors commencer à chercher une alternative.



Colonne calculée persistante



Une technique souvent utilisée pour résoudre les problÚmes de performances consiste à définir une colonne calculée comme persistante. Avec cette approche, l'expression est calculée à l'avance et le résultat est stocké avec le reste des données de la table.



Pour qu'une colonne soit persistante, elle doit ĂȘtre dĂ©terministe, c'est-Ă -dire que l'expression doit toujours renvoyer le mĂȘme rĂ©sultat pour la mĂȘme entrĂ©e. Par exemple, vous ne pouvez pas utiliser la fonction GETDATE dans une expression de colonne, car la valeur de retour change toujours.



Pour créer une colonne calculée persistante, vous devez ajouter un mot-clé à la définition de colonne PERSISTED, comme illustré dans l'exemple suivant.



DROP TABLE IF EXISTS Orders2;
GO
CREATE TABLE Orders2(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit) PERSISTED);

INSERT INTO Orders2 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;


La table est Orders2presque identique Ă  la table Orders1, sauf que la colonne Costcontient le mot-clĂ© PERSISTED. SQL Server remplit automatiquement cette colonne Ă  mesure que des lignes sont ajoutĂ©es ou modifiĂ©es. Bien sĂ»r, cela signifie que la table Orders2prendra plus de place que la table Orders1. Cela peut ĂȘtre vĂ©rifiĂ© Ă  l'aide d'une procĂ©dure stockĂ©e sp_spaceused.



sp_spaceused 'Orders1';
GO
sp_spaceused 'Orders2';
GO


La figure 3 montre la sortie de cette procédure stockée. La taille des données dans le tableau Orders1est de 8 824 Ko et dans le tableau de Orders212 936 Ko. 4 112 Ko de plus pour stocker les valeurs calculées.





Figure 3. Comparaison de la taille des tables Orders1 et Orders2



Bien que ces exemples soient basĂ©s sur un ensemble de donnĂ©es assez petit, vous pouvez voir comment la quantitĂ© de donnĂ©es stockĂ©es peut augmenter rapidement. Cependant, cela peut ĂȘtre un compromis si les performances s'amĂ©liorent.



Pour voir la différence de performances, procédez comme suit SELECT.



SELECT ItemID, Cost FROM Orders2 WHERE Cost >= 1000;


C'est le mĂȘme SELECT que j'ai utilisĂ© pour la table Orders1 (sauf pour le changement de nom). La figure 4 montre le plan d'exĂ©cution.





Figure 4. Plan d'exĂ©cution d'une requĂȘte vers la table Orders2



Cela commence Ă©galement par l'analyse d'index en cluster. Mais cette fois, il n'y a qu'une seule instruction Compute Scalar car les colonnes calculĂ©es n'ont plus besoin d'ĂȘtre calculĂ©es au moment de l'exĂ©cution. En gĂ©nĂ©ral, moins il y a d'Ă©tapes, mieux c'est. Bien que ce ne soit pas toujours le cas.



La deuxiĂšme requĂȘte a gĂ©nĂ©rĂ© 1593 lectures logiques, soit 485 lectures de plus que 1108 lectures pour la premiĂšre table. MalgrĂ© cela, il tourne plus vite que le premier. Bien que seulement environ 100 ms, et parfois beaucoup moins. Le temps du processeur a Ă©galement diminuĂ©, mais pas de beaucoup. TrĂšs probablement, la diffĂ©rence serait beaucoup plus grande sur des volumes plus importants et des calculs plus complexes.



Index sur une colonne calculée non persistante



L'indexation est une autre technique couramment utilisĂ©e pour amĂ©liorer les performances d'une colonne calculĂ©e. Pour pouvoir crĂ©er un index, la colonne doit ĂȘtre dĂ©terministe et prĂ©cise, ce qui signifie que l'expression ne peut pas utiliser les types float et real (si la colonne n'est pas persistante). Il existe Ă©galement des restrictions sur d'autres types de donnĂ©es ainsi que sur les paramĂštres SET. Pour obtenir la liste complĂšte des restrictions, consultez la documentation de SQL Server, Indexes on Computed Columns .



Vous pouvez vérifier si une colonne calculée non persistante convient à l'indexation via ses propriétés. Utilisons la fonction pour afficher les propriétés COLUMNPROPERTY. Les propriétés IsDeterministic, IsIndexable et IsPrecise sont importantes pour nous.



DECLARE @id int = OBJECT_ID('dbo.Orders1')
SELECT
  COLUMNPROPERTY(@id,'Cost','IsDeterministic') AS 'Deterministic',
  COLUMNPROPERTY(@id,'Cost','IsIndexable') AS 'Indexable',
  COLUMNPROPERTY(@id,'Cost','IsPrecise') AS 'Precise';


L'instruction SELECT doit renvoyer 1 pour chaque propriĂ©tĂ© afin que la colonne calculĂ©e puisse ĂȘtre indexĂ©e (voir la figure 5).





Figure 5. VĂ©rification de la



création de l'index AprÚs vérification, vous pouvez créer un index non clusterisé. Au lieu de modifier la table, Orders1j'ai créé une troisiÚme table ( Orders3) et inclus l'index dans la définition de la table.



DROP TABLE IF EXISTS Orders3;
GO
CREATE TABLE Orders3(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit),
  INDEX ix_cost3 NONCLUSTERED (Cost, ItemID));

INSERT INTO Orders3 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;


J'ai crĂ©Ă© un index de couverture non groupĂ© qui comprend Ă  la fois les colonnes d'une requĂȘte SELECT ItemIDet Costd'une requĂȘte SELECT. AprĂšs avoir crĂ©Ă© et rempli la table et l'index, vous pouvez exĂ©cuter l'instruction SELECT suivante similaire aux exemples prĂ©cĂ©dents.



SELECT ItemID, Cost FROM Orders3 WHERE Cost >= 1000;


La figure 6 montre le plan d'exĂ©cution de cette requĂȘte, qui utilise dĂ©sormais l'index non clusterisĂ© ix_cost3 (Index Seek) au lieu d'effectuer une analyse d'index cluster.





Figure 6. Plan d'exĂ©cution d'une requĂȘte sur la table Orders3



Si vous examinez les propriĂ©tĂ©s de l'instruction Index Seek, vous constaterez que la requĂȘte n'effectue dĂ©sormais que 92 lectures logiques, et dans les propriĂ©tĂ©s de l'instruction SELECT, vous verrez que l'UC et le temps total ont diminuĂ©. La diffĂ©rence n'est pas significative, mais encore une fois, il s'agit d'un petit ensemble de donnĂ©es.



Il convient Ă©galement de noter qu'il n'y a qu'une seule instruction Compute Scalar dans le plan d'exĂ©cution, et non deux comme dans la premiĂšre requĂȘte. La colonne calculĂ©e Ă©tant indexĂ©e, les valeurs ont dĂ©jĂ  Ă©tĂ© calculĂ©es. Cela Ă©limine le besoin de calculer les valeurs au moment de l'exĂ©cution, mĂȘme si la colonne n'a pas Ă©tĂ© dĂ©finie pour ĂȘtre persistante.



Index sur la colonne stockée



Vous pouvez Ă©galement crĂ©er un index sur la colonne calculĂ©e que vous enregistrez. Bien que cela entraĂźne le stockage de donnĂ©es supplĂ©mentaires et d'index, cela peut ĂȘtre utile dans certains cas. Par exemple, vous pouvez crĂ©er un index sur une colonne calculĂ©e persistante, mĂȘme si elle utilise les types de donnĂ©es float ou real. Cette approche peut Ă©galement ĂȘtre utile lorsque vous travaillez avec des fonctions CLR et lorsque vous ne pouvez pas vĂ©rifier si les fonctions sont dĂ©terministes.



L'instruction suivante CREATE TABLEcrée une table Orders4. La définition de table comprend à la fois une colonne persistante Costet un index de couverture non clusterisé ix_cost4.



DROP TABLE IF EXISTS Orders4;
GO
CREATE TABLE Orders4(
  LineID int IDENTITY PRIMARY KEY,
  ItemID int NOT NULL,
  Quantity int NOT NULL,
  Price decimal(18, 2) NOT NULL,
  Profit decimal(18, 2) NOT NULL,
  Cost AS (Quantity * Price - Profit) PERSISTED,
  INDEX ix_cost4 NONCLUSTERED (Cost, ItemID));

INSERT INTO Orders4 (ItemID, Quantity, Price, Profit)
SELECT StockItemID, Quantity, UnitPrice, LineProfit
FROM Sales.InvoiceLines
WHERE UnitPrice IS NOT NULL
ORDER BY InvoiceLineID;


Une fois la table et l'index créés et remplis, exécutez SELECT.



SELECT ItemID, Cost FROM Orders4 WHERE Cost >= 1000;


La figure 7 montre le plan d'exĂ©cution. Comme dans l'exemple prĂ©cĂ©dent, la requĂȘte commence par une recherche d'index non clusterisĂ©e (recherche d'index).





Figure 7. Plan d'exĂ©cution d'une requĂȘte sur la table Orders4



Cette requĂȘte n'effectue Ă©galement que 92 lectures logiques que la prĂ©cĂ©dente, ce qui donne Ă  peu prĂšs les mĂȘmes performances. La principale diffĂ©rence entre les deux colonnes calculĂ©es et entre les colonnes indexĂ©es et non indexĂ©es est la quantitĂ© d'espace utilisĂ©e. VĂ©rifions cela en exĂ©cutant la procĂ©dure stockĂ©e sp_spaceused.



sp_spaceused 'Orders1';
GO
sp_spaceused 'Orders2';
GO
sp_spaceused 'Orders3';
GO
sp_spaceused 'Orders4';
GO


Les résultats sont présentés dans la figure 8. Comme prévu, les colonnes calculées stockées ont plus de données et les colonnes indexées ont plus d'index.





Figure 8. Comparaison de l'utilisation de l'espace pour les quatre tables



TrĂšs probablement, vous n'aurez pas besoin d'indexer les colonnes calculĂ©es stockĂ©es sans raison valable. Comme pour les autres questions liĂ©es aux bases de donnĂ©es, votre choix doit ĂȘtre basĂ© sur votre situation spĂ©cifique: vos requĂȘtes et la nature de vos donnĂ©es.



Utilisation des colonnes calculées dans SQL Server



La colonne calculĂ©e n'est pas une colonne de table rĂ©guliĂšre et doit ĂȘtre manipulĂ©e avec soin pour Ă©viter de dĂ©grader les performances. La plupart des problĂšmes de performances peuvent ĂȘtre rĂ©solus en stockant ou en indexant la colonne, mais les deux approches doivent prendre en compte l'espace disque supplĂ©mentaire et la maniĂšre dont les donnĂ©es changent. Lorsque les donnĂ©es changent, les valeurs de colonne calculĂ©es doivent ĂȘtre mises Ă  jour dans la table ou l'index, ou les deux, si vous avez indexĂ© la colonne calculĂ©e persistante. Vous pouvez uniquement dĂ©cider laquelle des options est la mieux adaptĂ©e Ă  votre cas spĂ©cifique. Et, trĂšs probablement, vous devrez utiliser toutes les options.





Lire la suite






All Articles