SQL Server パフォーマンスチューニング、コンサルティング、アドバイス、相談、定期診断、トレーニング

ホーム＞技術情報＞ SQL Server 2014 実践 No.1 インメモリ OLTP 機能の実践的な利用方法

SQL Server 2014 実践シリーズ（HTML 版）
「No.1 インメモリ OLTP 機能の実践的な利用方法」

松本美穂と松本崇博が執筆した SQL Server 2014 実践シリーズの「No.1 インメモリ OLTP 機能の実践的な利用方法」の HTML 版です。日本マイクロソフトさんの Web サイトで Word または PDF 形式でダウンロードできますが、今回、HTML 版として公開する許可をいただきましたので、ここに掲載いたします。[2015年12月29日]

目次へ | 前のページへ | 次のページへ

2.6　メモリ最適化テーブル変数の利用（IN、OR、UNION ALL の代替）

顧客マスターの検索は、前項で説明したように、次のように (カードID, カード種別) の複合主キー（PRIMARY KEY）を利用したものでした。

SELECT * FROM 顧客マスター
WHERE カードID = '12345'
AND カード種別 IN ('A1', 'A2', 'A3')
ORDER BY カード種別

しかし、これでは性能向上を実現できなかったので、次のように UNION ALL へ変更しました。

SELECT * FROM (
  SELECT * FROM 顧客マスター
   WHERE カードID = '12345' AND カード種別 = 'A1'
  UNION ALL
  SELECT * FROM 顧客マスター
   WHERE カードID = '12345' AND カード種別 = 'A2'
  UNION ALL
  SELECT * FROM 顧客マスター
   WHERE カードID = '12345' AND カード種別 = 'A3' ) t1
ORDER BY カード種別

◆ ネイティブコンパイル SP 内では IN、OR、UNION が利用できない

ネイティブコンパイル SP は、性能が向上する一方で、制限事項もあります（詳しくは後述します）。そのうちの 1つが、ネイティブコンパイル SP 内では、IN や OR、UNION を利用できない、というものです。例えば、IN を含めたものを作成しようとすると、次のようにエラーが返されます。

00103

◆ メモリ最適化テーブル変数の利用

IN や OR、UNION は、メモリ最適化テーブル変数を利用することで、代用できる場合があります。これは、従来ながらのテーブル変数（table データ型の変数）をメモリ最適化テーブルのように扱えるようにしたものです。使い方は、table データ型と同様、CREATE TYPE を利用して作成し、違いは、最後に「MEMORY_OPTIMIZED = ON」を付けることと、ハッシュインデックスを付与する点です。

これは、次のように作成することができます。

CREATE TYPE 顧客マスター型 AS TABLE
(    カードID nchar(16) COLLATE Japanese_BIN2 NOT NULL,
    カード種別 nvarchar(2) COLLATE Japanese_BIN2 NOT NULL,
    col3 datetime NULL,
    col4 int NOT NULL,
    col5 datetime NOT NULL,
    col6 datetime NULL,
    col7 nchar(1) NOT NULL DEFAULT ('0')
    ,INDEX idx1 NONCLUSTERED HASH (カードID) WITH ( BUCKET_COUNT = 10 )
) WITH ( MEMORY_OPTIMIZED = ON )

顧客マスターの検索では、テーブルの全ての列を取得するので、顧客マスターテーブルの列定義と全く同じものを定義しています（ハッシュインデックスはカードID へ設定）。このように、CREATE TYPE で AS TABLE を指定して、「MEMORY_OPTIMIZED = ON」を付けたものは、メモリ最適化テーブル変数用の tableデータ型として利用することができます（DURABILITY の指定はありませんが、メモリ最適化テーブル変数の場合は SCHEMA_ONLY で作成されます）。

このように定義した tableデータ型には、次のように INSERT ステートメントでデータを格納することができます。

-- メモリ最適化テーブル変数の宣言
DECLARE @retValue dbo.顧客マスター型
-- メモリ最適化テーブル変数へデータの INSERT
INSERT INTO @retValue
  SELECT カードID, カード種別, col3, col4, col5, col6, col7
   FROM dbo.顧客マスター
    WHERE カードID = N'12345' AND カード種別 = N'A1'

これを利用することで、UNION ALL で連結しているそれぞれの SELECT ステートメントの結果をメモリ最適化テーブル変数へ格納して、その結果を返すようにすれば、ネイティブコンパイル SP を作成することができます。これは、次のように実装できます。

CREATE PROC p_顧客マスター検索
@p1 nchar(16)
WITH NATIVE_COMPILATION, EXECUTE AS OWNER, SCHEMABINDING
AS
BEGIN ATOMIC
WITH (
        TRANSACTION ISOLATION LEVEL = SNAPSHOT,
        LANGUAGE = N'japanese')
-- メモリ最適化テーブル変数の宣言
DECLARE @retValue dbo.顧客マスター型
-- カード種別 A1 の結果を格納
INSERT INTO @retValue
    SELECT カードID, カード種別, col3, col4, col5, col6, col7
     FROM dbo.顧客マスター
      WHERE カードID = @p1 AND カード種別 = N'A1'
-- カード種別 A2 の結果を格納
INSERT INTO @retValue
    SELECT カードID, カード種別, col3, col4, col5, col6, col7
     FROM dbo.顧客マスター
      WHERE カードID = @p1 AND カード種別 = N'A2'
-- カード種別 A3 の結果を格納
INSERT INTO @retValue
    SELECT カードID, カード種別, col3, col4, col5, col6, col7
     FROM dbo.顧客マスター
      WHERE カードID = @p1 AND カード種別 = N'A3'
-- メモリ最適化テーブル変数に格納した結果を SELECT。ORDER BY でソート
SELECT カードID, カード種別, col3, col4, col5, col6, col7
  FROM @retValue
  ORDER BY カード種別
END
go

このネイティブコンパイル SP を作成することで、顧客マスターの検索は、46.2%の性能向上（1.9倍の性能向上）を実現することができました。

このように、UNION ALL は、メモリ最適化テーブル変数で代用できる場合があるので、OR や IN 演算子の場合も、この方法を利用することができます。ただし、値が可変の場合への対応が難しいところがありますが、今回のシステムでは、カード種別が 3種類と決定している状況でしたので、この方法を採用することができました。

目次へ | 前のページへ | 次のページへ