クエリ・キャッシュの操作

クエリ・キャッシュによるパフォーマンスの改善

最初にクエリを準備するときに、SQL エンジンはこれを最適化し、クエリを実行するプログラム (1 つ以上の InterSystems IRIS® データ・プラットフォーム・ルーチンのセット) を生成します。最適化されたクエリ・テキストは、その後、クエリ・キャッシュ・クラスとして格納されます。その後で同じ (または同様の) クエリを実行しようとすると、SQL エンジンは、そのクエリ・キャッシュを見つけて、そのコードを直接実行します。これにより、最適化とコード生成の必要がなくなります。

クエリ・キャッシュには、以下の利点があります。

• その後、頻繁に使用されるクエリをより速く実行できます。さらに重要なこととして、このようなパフォーマンスの向上は自動的に実行でき、煩わしいストアド・プロシージャをコードする必要がありません。ほとんどのリレーショナル・データベース製品では、データベース・アクセスにストアド・プロシージャのみを使用することが推奨されていますが、InterSystems IRIS では、これは必要ありません。

• 単一のクエリ・キャッシュが、複数の類似したクエリ、つまり、リテラル値のみが異なる複数のクエリのために使用されます。例えば、SELECT TOP 5 Name FROM Sample.Person WHERE Name %STARTSWITH 'A' および SELECT TOP 1000 Name FROM Sample.Person WHERE Name %STARTSWITH 'Mc' は、TOP および %STARTSWITH 条件のリテラル値のみが異なります。1 つ目のクエリ用に準備されたクエリ・キャッシュは、自動的に 2 つ目のクエリにも使用されます。2 つの "同じ" クエリで個別のクエリ・キャッシュが使用される結果となる場合の考慮事項については、以下を参照してください。

• クエリ・キャッシュは、すべてのデータベース・ユーザの間で共有されます。したがって、ユーザ 1 がクエリを作成すれば、ユーザ 1023 がこれを使用できます。

• クエリ・オプティマイザでは、最初にクエリが作成されたときにのみコストが発生するため、任意のクエリの最善の実行方法を自由に時間をかけて見つけることができます。

InterSystems SQL は、すべてのクエリ・キャッシュを IRISLOCALDATA データベースという 1 つの場所に格納します。ただし、クエリ・キャッシュはネームスペース固有のものです。各クエリ・キャッシュは、それが準備された (生成された) ネームスペースで識別されます。クエリ・キャッシュは、それが準備されたネームスペース内からのみ表示および実行できます。クエリ・キャッシュの削除は、現在のネームスペースまたはすべてのネームスペースに対して可能です。

クエリ・キャッシュには、コメントは含まれません。ただし、クエリ・キャッシュのクエリ・テキストの後にコメント・オプションを含めることができます (例えば、/*#OPTIONS {"optionName":value} */)。

クエリ・キャッシュは既存のクエリ・プランを使用するので、既存のクエリの操作が連続的になります。プランが凍結された場合、インデックスの追加やテーブルの最適化統計の再定義など、基となるテーブルに対する変更は、既存のクエリ・キャッシュに影響を及ぼしません。テーブル定義を使用する際のクエリ・キャッシュの使用については、このドキュメントの “SQL 文と凍結プラン” を参照してください。

クエリ・キャッシュの作成

InterSystems IRIS がクエリを作成する場合、以下を判断します。

1. クエリが、クエリ・キャッシュに既に存在するクエリと一致しているかどうか。一致しない場合、クエリにインクリメント・カウントを割り当てます。

2. クエリが正常に準備されているかどうか。正常に準備されていない場合、クエリ・キャッシュ名にインクリメント・カウントを割り当てません。

3. 正常に準備されている場合は、クエリ・キャッシュ名にインクリメント・カウントを割り当て、クエリをキャッシュします。

ダイナミック SQL のクエリ・キャッシュ名

SQL エンジンは、以下に示す形式で、各クエリ・キャッシュに一意のクラス名を割り当てます。

%sqlcq.namespace.clsnnn

この namespace は、現在のネームスペースです (大文字表記)。nnn は、連続する整数です。例えば、%sqlcq.USER.cls16 のようになります。

クエリ・キャッシュには、ネームスペース単位で 1 から始まる連続番号が付けられます。次に使用可能な nnn 連続番号は、予約されている番号またはリリースされている番号によって異なります。

• 番号が予約されるのは、準備を開始したクエリが既存のクエリ・キャッシュと一致しない場合です。クエリと既存のクエリ・キャッシュは、リテラル値のみが異なる場合は一致していると見なされます。ただし、これは以下のようないくつかの追加の考慮事項に従います。リテラル置換の抑制、さまざまなコメント・オプション、または “個別のクエリ・キャッシュ” で説明している状況。

• クエリが正常に準備されていない場合、番号は予約されますが、割り当てられません。Prepare が成功したクエリのみがキャッシュされます。

• クエリが正常に準備されている場合、番号が予約され、クエリ・キャッシュに割り当てられます。このクエリ・キャッシュは、クエリ内で参照されるテーブルごとにリストされます。そのテーブルから何らかのデータにアクセスするかどうかには関係ありません。クエリでいずれのテーブルも参照しない場合、クエリ・キャッシュは作成されますが、テーブルごとのリストや削除はできません。

• 番号がリリースされるのは、クエリ・キャッシュが削除された場合です。この番号が、次の nnn 連続番号として使用可能になります。テーブルに関連付けられている個々のクエリ・キャッシュを削除する際、またはテーブルのクエリ・キャッシュをすべて削除する際、それらのクエリ・キャッシュに割り当てられていた番号はリリースされます。ネームスペース内のすべてのクエリ・キャッシュを削除すると、テーブルを参照していないクエリ・キャッシュ、および予約されていたが割り当てられていなかった番号も含め、クエリ・キャッシュに割り当てられていたすべての番号がリリースされます。

  クエリ・キャッシュを削除すると、整数 nnn はリセットされます。整数は再使用されますが、残されているクエリ・キャッシュが再番号付けされることはありません。例えば、クエリ・キャッシュの部分的な削除により、cls1、cls3、cls4、および cls7 が残されているとします。その後のクエリ・キャッシュに付けられる番号は、cls2、cls5、cls6、および cls8 になります。

CALL 文を使用すると、複数のクエリ・キャッシュが生成される場合があります。例えば、SQL 文 CALL Sample.PersonSets('A','MA') では、以下のクエリ・キャッシュが生成されます。

%sqlcq.USER.cls1: CALL Sample . PersonSets ( ? , ? )
%sqlcq.USER.cls2: SELECT name , dob , spouse FROM sample . person 
                     WHERE name %STARTSWITH ? ORDER BY 1
%sqlcq.USER.cls3: SELECT name , age , home_city , home_state 
                     FROM sample . person WHERE home_state = ? ORDER BY 4 , 1

ダイナミック SQL では、SQL クエリを準備 (%Prepare() または %PrepareClassQuery() インスタンス・メソッドを使用) した後で、%Display()Opens in a new tab インスタンス・メソッドまたは %GetImplementationDetails()Opens in a new tab インスタンス・メソッドを使用すると、クエリ・キャッシュ名を返すことができます。"正常な作成の結果" を参照してください。

クエリ・キャッシュ名は、%SQL.StatementOpens in a new tab クラス (および %CurrentResultOpens in a new tab プロパティ) の %Execute()Opens in a new tab インスタンス・メソッドによって返される結果セット OREF のコンポーネントでもあります。以下のサンプルでは、クエリ・キャッシュ名を判断する上記の両方のメソッドを示しています。

  SET randtop=$RANDOM(10)+1
  SET randage=$RANDOM(40)+1
  SET myquery = "SELECT TOP ? Name,Age FROM Sample.Person WHERE Age < ?"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
   IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET x = tStatement.%GetImplementationDetails(.class,.text,.args)
  IF x=1 { WRITE "cached query name is: ",class,! }
  SET rset = tStatement.%Execute(randtop,randage)
  WRITE "result set OREF: ",rset.%CurrentResult,!
  DO rset.%Display()
  WRITE !,"A sample of ",randtop," rows, with age < ",randage

この例では、選択された行の数 (TOP 節) および WHERE 節の述語の値は、クエリが呼び出されるたびに変更されますが、クエリ・キャッシュ名は変更されません。

%sqlcq.namespace.hash

リテラル置換

SQL エンジンは、SQL クエリをキャッシュするときにリテラル置換を実行します。クエリ・キャッシュ内のクエリは、入力パラメータを表す “?” 文字で各リテラルを表現します。そのため、リテラル値のみが異なるクエリは、単一のクエリ・キャッシュで表現されることになります。例えば、以下の 2 つのクエリがあるとします。

SELECT TOP 11 Name FROM Sample.Person WHERE Name %STARTSWITH 'A'

SELECT TOP 5 Name FROM Sample.Person WHERE Name %STARTSWITH 'Mc'

これらは、以下の単一のクエリ・キャッシュで表現されます。

SELECT TOP ? Name FROM Sample.Person WHERE Name %STARTSWITH ?

これにより、クエリ・キャッシュのサイズが最小化され、リテラル値のみが異なるクエリに対してクエリの最適化を実行する必要がなくなります。

入力ホスト変数 (例えば、:myvar) と ? 入力パラメータを使用して指定したリテラル値についても、対応するクエリ・キャッシュ内では “?” 文字で表現されます。このため、SELECT Name FROM t1 WHERE Name='Adam'、SELECT Name FROM t1 WHERE Name=?、および SELECT Name FROM t1 WHERE Name=:namevar はすべて一致するクエリであり、単一のクエリ・キャッシュが生成されます。

%GetImplementationDetails() メソッドを使用すると、特定の Prepare の各 “?” 文字が、どのエンティティを表しているか判断できます。

リテラル置換に適用される考慮事項は以下のとおりです。

• リテラルの一部として指定されたプラス記号とマイナス記号によって、個別のクエリ・キャッシュが生成されます。つまり、ABS(7)、ABS(-7)、および ABS(+7) によって、それぞれ個別のクエリ・キャッシュが生成されます。複数の記号でも個別のクエリ・キャッシュが生成されます (ABS(+?)、ABS(++?) など)。このため、個別のクエリ・キャッシュを生成することなく符号付きまたは符号なしの数値を指定できる符号なしの変数 ABS(?) または ABS(:num) を使用することをお勧めします。

• 有効桁数と小数桁数の値には、通常、リテラル置換が行われません。つまり、ROUND(567.89,2) は、ROUND(?,2) としてキャッシュされることになります。ただし、CURRENT_TIME(n)、CURRENT_TIMESTAMP(n)、GETDATE(n)、および GETUTCDATE(n) におけるオプションの有効桁数の値には、リテラル置換が行われます。

• ブーリアン・フラグには、リテラル置換が行われません。つまり、ROUND(567.89,2,0) は、ROUND(?,2,0) としてキャッシュされ、ROUND(567.89,2,1) は、ROUND(?,2,1) としてキャッシュされることになります。

• IS NULL または IS NOT NULL 条件で使用されるリテラルには、リテラル置換が行われません。

• ORDER BY 節で使用されるリテラルには、リテラル置換が行われません。これは、ORDER BY は整数を使用して列の位置を指定できるためです。この整数が変わると、クエリはまったく異なる結果になります。

• アルファベット・リテラルは、一重引用符で囲む必要があります。一部の関数では、引用符の有無にかかわらずアルファベット形式のコードの指定を許可しています。リテラル置換は、引用符で囲まれたアルファベット形式のコードにのみ行われます。つまり、DATENAME(MONTH,64701) と DATENAME('MONTH',64701) には、機能的な違いはありませんが、それらに対応するクエリ・キャッシュは DATENAME(MONTH,?) と DATENAME(?,?) になるということです。

• 可変個数の引数を受け取る関数は、引数の個数ごとに個別のクエリ・キャッシュを生成します。つまり、COALESCE(1,2) と COALESCE(1,2,3) には、個別のクエリ・キャッシュが生成されることになります。

リテラル置換の抑制

このリテラル置換は、抑制できます。リテラル値に対して最適化を行い、そのリテラル値を持つクエリの個別クエリ・キャッシュを作成することが必要となる状況があります。リテラル置換を抑制するには、リテラル値を二重の括弧で囲みます。詳細は、以下の例を参照してください。

SELECT TOP 11 Name FROM Sample.Person WHERE Name %STARTSWITH (('A'))

異なる %STARTSWITH の値を指定すると、個別のクエリ・キャッシュが生成されます。リテラル置換の抑制が、リテラルごとに個別に指定されていることに注意してください。上記の例では、異なる TOP の値を指定しても、個別のクエリ・キャッシュは生成されません。

符号付き数値のリテラル置換を抑制するには、ABS(-((7))) などの構文を指定します。

Note:

別の数の括弧で囲っていても、状況によっては、リテラル置換を抑制できることがあります。インターシステムズでは、これを目的とした最も明確で一貫性のある構文として、常に二重の括弧を使用することをお勧めします。

キャッシュ・クエリの結果セット

キャッシュ・クエリを実行すると、結果セットが作成されます。キャッシュ・クエリの結果セットはオブジェクト・インスタンスです。つまり、リテラル置換の入力パラメータに指定した値はオブジェクト・プロパティとして格納されます。これらのオブジェクト・プロパティは i%PropName 構文を使用して参照されます。

クエリ・キャッシュのリスト

現在のネームスペースの既存のクエリ・キャッシュをカウントおよびリストできます。

• クエリ・キャッシュのカウント

• InterSystems IRIS 管理ポータルを使用したクエリ・キャッシュのリスト

• InterSystems IRIS 管理ポータルを使用したクエリ・キャッシュまたはストアド・プロシージャにより参照されるテーブルのリスト

クエリ・キャッシュのカウント

%Library.SQLCatalogOpens in a new tab クラスの GetCachedQueryTableCount()Opens in a new tab メソッドを呼び出すことで、テーブルの現在のクエリ・キャッシュ数を特定できます。詳細は、以下の例を参照してください。

  SET tbl="Sample.Person"
  SET num=##class(%Library.SQLCatalog).GetCachedQueryTableCount(tbl)
  IF num=0 {WRITE "There are no cached queries for ",tbl }
  ELSE {WRITE tbl," is associated with ",num," cached queries" }

複数のテーブルを参照するクエリでは 1 つのクエリ・キャッシュが作成されます。ただし、それらのテーブルはそれぞれ、そのクエリ・キャッシュを個別にカウントします。したがって、テーブルによってカウントされたクエリ・キャッシュの数が実際のクエリ・キャッシュの数より多くなることがあります。

クエリ・キャッシュの実行

• ダイナミック SQL から：%SQL.StatementOpens in a new tab の Prepare 操作 (%Prepare()、%PrepareClassQuery()、または %ExecDirect()) によって、クエリ・キャッシュが作成されます。同じインスタンスを使用するダイナミック SQL の %Execute() メソッドでは、直前に作成されたクエリ・キャッシュが実行されます。

• 管理ポータルの [SQL] インタフェースから：上記の “クエリ・キャッシュのリスト” の手順に従います。選択したクエリ・キャッシュの [カタログの詳細] タブから、[実行] リンクをクリックします。

クエリ・キャッシュのロック

Prepare 文または Purge 文を発行すると、システム全体の排他的なロックが自動的に要求され、同時にクエリ・キャッシュのメタデータが更新されます。SQL は、$SYSTEM.SQL.Util.SetOption()Opens in a new tab メソッドのシステム全体用オプション CachedQueryLockTimeout をサポートしています。このオプションは、クエリ・キャッシュのメタデータに対するロックの取得を試行するときに、ロック・タイムアウトを制御します。デフォルトは 120 秒です。これは、標準の SQL のロック・タイムアウト (既定値は 10 秒) よりも、大幅に長い時間になります。大量の Prepare 操作と Purge 操作が同時に行われるシステムでは、システム管理者は、このクエリ・キャッシュのロック・タイムアウトを変更することが必要になる可能性があります。特に、大量の (数千の) クエリ・キャッシュが関与する一括削除を実行するシステムでは、この必要性が高くなります。

SET status=$SYSTEM.SQL.Util.SetOption("CachedQueryLockTimeout",seconds,.oldval) メソッドは、システム全体のタイムアウト値を設定します。

SetCQTimeout
   SET status=$SYSTEM.SQL.Util.SetOption("CachedQueryLockTimeout",150,.oldval)
   WRITE oldval," initial value cached query seconds",!!
SetCQTimeoutAgain
   SET status=$SYSTEM.SQL.Util.SetOption("CachedQueryLockTimeout",180,.oldval2)
   WRITE oldval2," prior value cached query seconds",!!
ResetCQTimeoutToDefault
   SET status=$SYSTEM.SQL.Util.SetOption("CachedQueryLockTimeout",oldval,.oldval3)

CachedQueryLockTimeout は、システム全体のすべての新しいプロセスに対するクエリ・キャッシュのロック・タイムアウトを設定します。既存のプロセスに対するクエリ・キャッシュのロック・タイムアウトは変更されません。

クエリ・キャッシュの削除

テーブル定義を変更 (または削除) すると、このテーブルを基にしたクエリは、自動的にローカル・システム上のクエリ・キャッシュから削除されます。永続クラスをリコンパイルすると、そのクラスを使用するすべてのクエリは、自動的にローカル・システム上のクエリ・キャッシュから削除されます。

管理ポータルでいずれかの [クエリキャッシュ削除] オプションを使用して、クエリ・キャッシュを明示的に削除することができます。SQL コマンド PURGE CACHED QUERIES を使用して、明示的にクエリ・キャッシュを削除することができます。SQL シェルの PURGE コマンドを使用して、明示的にクエリ・キャッシュを削除することもできます。

$SYSTEM.SQL.Purge(n)Opens in a new tab メソッドを使用して、最近使用されていないクエリ・キャッシュを明示的に削除できます。日数 n を指定することで、現在のネームスペース内のクエリ・キャッシュの中で、過去 n 日以内に使用 (準備) されていないものをすべて削除できます。n の値に 0 または "" を指定すると、現在のネームスペースのすべてのクエリ・キャッシュを削除します。例えば、2018 年 5 月 11 日に $SYSTEM.SQL.Purge(30) メソッドを発行した場合は、このメソッドによって、最終使用日が 2018 年 4 月 11 日より前のクエリ・キャッシュのみが削除されます。最終使用日が正確に 30 日前 (この例では、4 月 11 日) のクエリ・キャッシュは削除されません。

また、以下のメソッドを使用してもクエリ・キャッシュを削除できます。

• $SYSTEM.SQL.PurgeCQClass()Opens in a new tab は、現在のネームスペース内の 1 つまたは複数のクエリ・キャッシュを名前で削除します。クエリ・キャッシュ名は、コンマ区切りのリストで指定できます。クエリ・キャッシュ名では、大文字と小文字が区別されます。ネームスペース名は、すべて大文字で指定する必要があります。指定したクエリ・キャッシュ名またはクエリ・キャッシュ名リストは、引用符で囲む必要があります。

• $SYSTEM.SQL.PurgeForTable()Opens in a new tab は、指定されたテーブルを参照する、現在のネームスペース内のクエリ・キャッシュをすべて削除します。スキーマ名とテーブル名では、大文字と小文字は区別されません。

• $SYSTEM.SQL.PurgeAllNamespaces()Opens in a new tab は、現在のシステム上にあるすべてのネームスペース内のすべてのクエリ・キャッシュを削除します。ネームスペースを削除しても、そのネームスペースに関連付けられたクエリ・キャッシュは削除されません。PurgeAllNamespaces() を実行して、既に存在していないネームスペースに関連付けられたクエリ・キャッシュがあるかどうかを確認します。該当するクエリ・キャッシュは削除されます。

  現在のネームスペースのクエリ・キャッシュをすべて削除するには、管理ポータルに用意されているそのネームスペースのクエリをすべて削除するオプションを使用します。

クエリ・キャッシュを削除すると、関連するクエリ・パフォーマンス統計も削除されます。

クエリ・キャッシュを削除すると、関連する SQL 文リストのエントリも削除されます。管理ポータルに表示される SQL 文がすぐには削除されないことがあります。その場合、[SQLステートメント] リストからエントリを削除するには、[古いものをクリーン] ボタンをクリックする必要があります。

キャッシュされない SQL コマンド

以下の非クエリ SQL コマンドはキャッシュされません。これらは使用した直後に削除されます。

• データ定義言語 (DDL)：CREATE TABLE、ALTER TABLE、DROP TABLE、CREATE VIEW、ALTER VIEW、DROP VIEW、CREATE INDEX、DROP INDEX、CREATE FUNCTION、CREATE METHOD、CREATE PROCEDURE、CREATE QUERY、DROP FUNCTION、DROP METHOD、DROP PROCEDURE、DROP QUERY、CREATE TRIGGER、DROP TRIGGER、CREATE DATABASE、USE DATABASE、DROP DATABASE

• ユーザ､ロール、および特権：CREATE USER、ALTER USER、DROP USER、CREATE ROLE、DROP ROLE、GRANT、REVOKE、%CHECKPRIV

• ロック：LOCK TABLE、UNLOCK TABLE

• その他：SAVEPOINT、SET OPTION

管理ポータルの [クエリ実行] インタフェースからこれらの SQL コマンドのいずれかを発行する場合は、[パフォーマンス] 情報に Cached Query: %sqlcq.USER.cls16 のようなテキストが含まれます。これは、クエリ・キャッシュ名が割り当てられたことを示すために表示されます。ただし、このクエリ・キャッシュ名はリンクではありません。クエリ・キャッシュは作成されず、クエリ・キャッシュの増分番号 .cls16 は確保されません。InterSystems SQL は、次に発行された SQL コマンドにこのクエリ・キャッシュ番号を割り当てます。