SQLServerでの計算列のパフォーマンス

記事の翻訳は、コース「MS SQLServerDeveloper」の学生のために特別に作成されました。

---

計算された列は、診断が難しいパフォーマンスの問題を引き起こす可能性があります。この記事では、いくつかの問題といくつかの解決策について説明します。



計算列は、テーブル定義に計算を埋め込むための便利な方法です。ただし、特に式が複雑になり、アプリケーションの要求が厳しくなり、データ量が増え続けると、パフォーマンスの問題が発生する可能性があります。



計算列は、テーブル内の他の列の値に基づいて値が計算される仮想列です。デフォルトでは、計算された値は物理的に保存されませんが、代わりにSQLServerがすべての列要求で計算します。これにより、プロセッサの負荷が増加しますが、テーブルが変更されたときに保持する必要のあるデータの量が減少します。



多くの場合、非永続的な計算列はCPUを集中的に使用し、クエリを遅くし、アプリケーションをフリーズさせます。幸い、SQL Serverには、計算列のパフォーマンスを向上させるいくつかの方法があります。永続化された計算列を作成するか、それらにインデックスを付けるか、またはその両方を行うことができます。



デモンストレーションの目的で、4つの同様のテーブルを作成し、WideWorldImportersデモデータベースからの同一のデータをそれらに入力しました。各テーブルには同じ計算列がありますが、2つのテーブルには永続性があり、2つのテーブルにはインデックスがあります。結果は次のオプションです。

• テーブルOrders1は保存されていない計算列です。
• テーブルOrders2は永続的な計算列です。
• テーブルOrders3は、インデックス付きの非永続的な計算列です。
• テーブルOrders4は、インデックス付きの永続的な計算列です。

計算された式は非常に単純で、データセットは非常に小さいです。ただし、永続的でインデックス付きの計算列の原則と、これがパフォーマンスの問題の解決にどのように役立つかを示すだけで十分です。

保存されていない計算列

おそらくあなたの状況では、データの保存、インデックスの作成を回避するため、または非決定論的な列で使用するために、非永続的な計算列が必要になる場合があります。たとえば、SQL Serverは、WITH SCHEMABINDINGが関数定義にない場合、スカラーUDFを非決定論として扱います。この関数を使用して永続的な計算列を作成しようとすると、永続化された列を作成できないというエラーが発生します。



ただし、カスタム関数は独自のパフォーマンスの問題を引き起こす可能性があることに注意してください。テーブルに関数を含む計算列が含まれている場合、クエリエンジンは同時実行を使用しません（SQL Server 2019を使用している場合を除く）。計算列がクエリで指定されていない場合でも。大規模なデータセットの場合、これはパフォーマンスに大きな影響を与える可能性があります。関数は、UPDATEの実行を遅くし、オプティマイザが計算された列のクエリのコストを計算する方法に影響を与える可能性もあります。これは、計算列で関数を使用してはならないという意味ではありませんが、慎重に扱う必要があります。



関数を使用するかどうかに関係なく、非永続的な計算列の作成は非常に簡単です。次の指示CREATE TABLEOrders1計算列を含むテーブルを定義します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;

計算列を定義するには、その名前の後にASキーワードと式を指定します。この例では、乗算されているQuantityことにより、Priceおよび減算しますProfit。テーブルを作成した後Sales.InvoiceLines、WideWorldImportersデータベーステーブルのデータを使用してINSERTを入力します。次に、SELECTを実行します。

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

このクエリは、22,973行、またはWideWorldImportersデータベースにあるすべての行を返す必要があります。このクエリの実行計画を図1に示します。





図1.Orders1テーブルに対するクエリの実行計画



最初に注意するのは、データを取得する効率的な方法ではないクラスター化インデックススキャンです。しかし、これだけが問題ではありません。 Clustered Index Scanのプロパティで論理読み取り（実際の論理読み取り）の数を見てみましょう（図2を参照）。





図2.Orders1テーブルを照会するための論理読み取り



論理読み取りの数（この場合は1108）は、データキャッシュから読み取られたページの数です。目標は、この数を可能な限り減らすことです。したがって、覚えて他のオプションと比較すると便利です。



論理読み取りの数はSET STATISTICS IO ON、SELECTを実行する前にステートメントを実行することによっても取得できます。 CPUと合計時間を表示するには、SET STATISTICS TIME ONまたはクエリ実行プランのSELECTステートメントのプロパティを表示します。



注目に値するもう1つのポイントは、実行計画に2つのComputeScalarステートメントがあることです。1つ目（右側）は、返された各行の計算された列値の計算です。列の値はその場で計算されるため、その列にインデックスを作成しない限り、非永続的な計算列でこの手順を回避することはできません。



場合によっては、非永続的な計算列は、それを保存したり、インデックスを使用したりせずに必要なパフォーマンスを提供します。これにより、ストレージスペースが節約されるだけでなく、テーブルまたはインデックスの計算値を更新するオーバーヘッドも回避されます。ただし、多くの場合、非永続的な計算列はパフォーマンスの問題につながるため、代替手段を探し始める必要があります。

永続的な計算列

パフォーマンスの問題を解決するためによく使用される手法の1つは、計算列を永続化として定義することです。このアプローチでは、式が事前に計算され、結果が残りのテーブルデータとともに保存されます。



列が永続的であるためには、それが決定論的である必要があります。つまり、式は同じ入力に対して常に同じ結果を返す必要があります。たとえば、戻り値は常に変化するため、列式でGETDATE関数を使用することはできません。



永続的な計算列を作成するにPERSISTEDは、次の例に示すように、列定義にキーワードを追加する必要があります。

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;

このテーブルは、列にキーワードが含まれていることを除いて、テーブルとOrders2ほぼ同じです。 SQL Serverは、行が追加または変更されると、この列に自動的にデータを入力します。もちろん、これはテーブルがテーブルよりも多くのスペースを占めることを意味します。これは、保存されたプロシージャを使用して確認できます。Orders1CostPERSISTEDOrders2Orders1sp_spaceused

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

図3は、この保存されたプロシージャーの出力を示しています。表のデータサイズは8,824KBOrders1で、表のデータサイズはOrders212,936KBです。計算値を保存するためにさらに4,112KB。





図3.Orders1テーブルとOrders2テーブルのサイズの比較



これらの例はかなり小さなデータセットに基づいていますが、保存されるデータの量がどのように急速に増加するかを確認できます。ただし、パフォーマンスが向上した場合、これはトレードオフになる可能性があります。



パフォーマンスの違いを確認するには、次のSELECTを実行します。

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

これは、Orders1テーブルに使用したものと同じSELECTです（名前の変更を除く）。図4に実行計画を示します。





図4.Orders2テーブルへのクエリの実行計画。



これもクラスター化インデックススキャンから始まります。ただし、今回は、計算列を実行時に計算する必要がなくなったため、ComputeScalarステートメントは1つだけです。一般に、ステップは少ないほど良いです。これは常にそうであるとは限りませんが。



2番目のクエリは1593の論理読み取りを生成しました。これは、最初のテーブルの1108の読み取りよりも485多い値です。それにもかかわらず、それは最初のものより速く走ります。わずか約100msですが、それよりはるかに短い場合もあります。プロセッサ時間も減少しましたが、それほどではありません。ほとんどの場合、ボリュームが大きく、計算が複雑になると、差ははるかに大きくなります。

非永続的な計算列のインデックス

計算列のパフォーマンスを向上させるために一般的に使用されるもう1つの手法は、索引付けです。インデックスを作成できるようにするには、列が決定論的で正確である必要があります。つまり、式は浮動小数点型と実数型を使用できません（列が永続的でない場合）。他のデータタイプやSETパラメータにも制限があります。制限の完全なリストについては、SQLServerのドキュメント「ComputedColumnsのインデックス」を参照してください。



非永続的な計算列がそのプロパティを介してインデックス付けに適しているかどうかを確認できます。この関数を使用してプロパティを表示してみましょうCOLUMNPROPERTY。プロパティIsDeterministic、IsIndexable、およびIsPreciseは私たちにとって重要です。

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';

計算された列にインデックスを付けることができるように、SELECTステートメントはプロパティごとに1を返す必要があります（図5を参照）。





図5.インデックス



を作成できることの確認検証後、非クラスター化インデックスを作成できます。テーブルを変更する代わりにOrders1、3番目のテーブル（Orders3）を作成し、テーブル定義にインデックスを含めました。

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;

SELECTクエリの 列ItemIDとCostSELECTクエリの両方の列を含む非クラスター化カバーインデックスを作成しました。テーブルとインデックスを作成してデータを入力した後、前の例と同様に次のSELECTステートメントを実行できます。

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

図6は、このクエリの実行計画を示しています。このクエリでは、クラスター化インデックススキャンを実行する代わりに、ix_cost3（インデックスシーク）非クラスター化インデックスを使用しています。





図6.Orders3テーブルでのクエリの実行計画IndexSeek



ステートメントのプロパティを見ると、クエリが92の論理読み取りのみを実行し、SELECTステートメントのプロパティでCPUと合計時間が減少していることがわかります。違いは重要ではありませんが、これも小さなデータセットです。



また、実行計画には、最初のクエリのように2つではなく、1つのComputeScalarステートメントしかないことにも注意してください。計算された列にはインデックスが付けられているため、値はすでに計算されています。これにより、列が永続的であると定義されていない場合でも、実行時に値を計算する必要がなくなります。

保存された列のインデックス

保存する計算列にインデックスを作成することもできます。これにより、追加のデータとインデックスデータが保存されますが、場合によっては役立つことがあります。たとえば、floatまたは実際のデータタイプを使用している場合でも、永続的な計算列にインデックスを作成できます。このアプローチは、CLR関数を操作する場合や、関数が決定論的であるかどうかを確認できない場合にも役立ちます。



次のステートメントCREATE TABLEはテーブルを作成しますOrders4。テーブル定義には、永続列Costと非クラスター化カバーインデックス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;

テーブルとインデックスが作成され、データが入力されたら、SELECTを実行します。

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

図7に実行計画を示します。前の例と同様に、クエリは非クラスター化インデックス検索（インデックスシーク）から始まります。





図7.Orders4テーブルでのクエリの実行計画



このクエリも、前のクエリと92の論理読み取りのみを実行するため、ほぼ同じパフォーマンスが得られます。 2つの計算列の主な違い、およびインデックス付き列とインデックスなし列の主な違いは、使用されるスペースの量です。ストアドプロシージャを実行して、これを確認しましょうsp_spaceused。

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

結果を図8に示します。予想どおり、格納された計算列にはより多くのデータがあり、インデックス付き列にはより多くのインデックスがあります。





図8.4つのテーブルすべてのスペース使用率の比較



ほとんどの場合、正当な理由がない限り、格納されている計算列にインデックスを付ける必要はありません。他のデータベース関連の質問と同様に、選択は特定の状況、つまりクエリとデータの性質に基づいて行う必要があります。

SQLServerでの計算列の操作

計算列は通常のテーブル列ではないため、パフォーマンスを低下させないように注意して処理する必要があります。ほとんどのパフォーマンスの問題は、列を保存またはインデックス付けすることで解決できますが、どちらのアプローチでも、余分なディスク領域とデータの変更方法を考慮する必要があります。データが変更された場合、永続化された計算列にインデックスを付けた場合は、計算列の値をテーブルまたはインデックス、あるいはその両方で更新する必要があります。特定のケースに最適なオプションを決定することしかできません。そして、ほとんどの場合、すべてのオプションを使用する必要があります。

続きを読む

• SQL Server2017グラフデータベースの概要