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.10　ネイティブコンパイル SP を簡単に作成する方法

ここでは、今回のポイントカードシステムで、私たちがネイティブコンパイル SP を作成したときに利用した方法を説明します。これを利用すれば、簡単にネイティブコンパイル SP を作成することができるので、参考になると思います。

大まかな手順は次のとおりです。

アプリケーションから実行される SQL を、SQL Server Profiler でキャプチャする
キャプチャした SQL（sp_executesql）をクエリエディターへ貼り付けて、パラメーター定義や実行ステートメントからネイティブコンパイル SP を作成する
パラメーターで (var)char データ型を利用している場合は、n付きの n(var)char データ型へ変更する
ステートメント内で単一引用符（文字リテラル）を利用している場合は、Nプレフィックスを付ける
SELECT ステートメントで SELECT * を利用している場合は、列名を列挙するように変更する

◆ 実際の手順

次の .NET アプリケーション（VB.NET＋ADO.NET）を例に、ネイティブコンパイル SP を作成する方法を説明します。

00111

Using cn As New SqlConnection(cnstr)
    Using cmd As New SqlCommand()
        cn.Open()
        cmd.Connection = cn
        cmd.CommandText = "INSERT INTO t1 VALUES(@p1, @p2, @p3)"
        Dim p1 As SqlParameter = cmd.Parameters.Add("@p1", SqlDbType.Int)
        p1.Value = 1
        Dim p2 As SqlParameter = cmd.Parameters.Add("@p2", SqlDbType.NVarChar, 200)
        p2.Value = "A"
        Dim p3 As SqlParameter = cmd.Parameters.Add("@p3", SqlDbType.DateTime)
        p3.Value = DateTime.Now
        cmd.ExecuteNonQuery()
        cn.Close()
    End Using
End Using

これは、「t1」テーブル（int、nvarchar(200)、datetime データ型の 3つの列がある場合）にデータを INSERT するコードです。

◆ SQL Server Profiler ツールで SQL をキャプチャする

上記のアプリケーションを実行するときに、SQL Server Profiler ツールを利用して、内部的に実行された SQL をキャプチャ（トレース）しますが、SQL Server Profiler ツールは、次のように Management Studio の［ツール］メニューから「SQL Server プロファイラー」をクリックして、起動することができます。

00112

起動後、［サーバーへの接続］ダイアログが表示されたら、接続したい SQL Server の名前を入力して、［接続］ボタンをクリックします。

00113

接続が完了すると、次のように［トレースのプロパティ］ダイアログが表示されるので、［実行］ボタンをクリックします。

00114

これで、SQL Server に対して実行されたすべての SQL をキャプチャできるようになるので、ここでアプリケーションを実行します。

アプリケーションを実行すると、次のように「Audit Login」（ログイン成功）や「RPC:Completed」（リモートプロシージャコールの完了）が表示されます。

00115

「RPC:Completed」をクリックすると、アプリケーションから内部的に実行された SQL を確認することができます。次のように sp_executesql でパラメーター化されたものになっていることを確認できます。

exec sp_executesql N'INSERT INTO t1 VALUES(@p1, @p2, @p3)'
,N'@p1 int, @p2 nvarchar(200), @p3 datetime'
,@p1=1, @p2=N'A', @p3='2014-06-24 16:38:06.853'

次に、この SQL をクエリエディターへ貼り付けて、これをもとにネイティブコンパイル SP を作成していきます。

◆ ネイティブコンパイル SP の作成

ネイティブコンパイル SP を作成するときは、次の基本構文を利用します。

-- ネイティブコンパイル SP の雛形
CREATE PROC プロシージャ名
パラメーター定義
WITH
   NATIVE_COMPILATION, EXECUTE AS OWNER, SCHEMABINDING
AS
BEGIN ATOMIC
WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT,
        LANGUAGE = N'japanese')
     -- ここに SQL ステートメントを貼り付ける
END

この構文の中へ、プロファイラーでキャプチャした sp_executesql の第2引数のパラメーター定義をプロシージャ名の後ろのパラメーター定義へ追加して、第1引数の SQL ステートメントを BEGIN .. END ブロック内へ貼り付けます。

00116

◆ オブジェクト名にスキーマ名を付与する

次に、オブジェクト名（画面は t1）へスキーマ名を付与して、「dbo.t1」のように変更しますが、もしスキーマ名を省略している場合は、次のように「スキーマバインドできません」エラーが表示されます。

00117

スキーマ名を付与した後、プロシージャ名へ任意の名前を付ければ、ネイティブコンパイル SP の作成が完了です。

00118

◆ アプリケーションの修正（ADO.NET の場合）

ネイティブコンパイル SP を作成した後は、アプリケーション側を変更しますが、次のように、SqlCommand オブジェクトの CommandText をネイティブコンパイル SP の名前へ変更して、CommandType を「CommandType.StoredProcedure」へ指定するだけで完了です。

cn.Open()
cmd.Connection = cn
cmd.CommandType = CommandType.StoredProcedure ' この行を追加
cmd.CommandText = "ネイティブ コンパイル SP の名前へ変更" ' この行を修正
Dim p1 As SqlParameter = cmd.Parameters.Add("@p1", SqlDbType.Int)
p1.Value = 1
Dim p2 As SqlParameter = cmd.Parameters.Add("@p2", SqlDbType.NVarChar, 200)
p2.Value = "A"
Dim p3 As SqlParameter = cmd.Parameters.Add("@p3", SqlDbType.DateTime)
p3.Value = DateTime.Now
cmd.ExecuteNonQuery()

パラメーター（SqlParameter）に関しては、同じものを利用しているので、そのまま利用することができます。このように記述することで、ネイティブコンパイル SP を実行することができ、内部的には次のように変換されて実行されています。

00120

EXEC で、ストアドプロシージャを呼び出して、パラメーターのデータ型が nvarchar の場合は、Nプレフィックスが付与された文字列に変換されて、実行されています。

Note： CommandType を Text にした場合は？
上の例では、SqlCommand の CommandType を「CommandType.StoredProcedure」へ変更して、CommandText へネイティブコンパイル SP の名前を指定する方法をとりましたが、CommandType の既定値である「CommandType.Text」を利用しても、次のようにネイティブコンパイル SP を実行することができます。
00121

しかし、この方法は、「CommandType.StoredProcedure」を利用するよりも性能が出ないので、利用しないことをお勧めします。詳しい性能比較については、次の項で説明します。

◆ パラメーターに char／varchar が利用できない～ n付きへ変更する～

データベースの照合順序に Japanese_CI_AS（日本語版の SQL Server の既定の照合順序）など、コードページが「1252」以外のものを利用している場合には、ネイティブコンパイル SP のパラメーターで char／varchar データ型を利用することができません。

00122

このように、ネイティブコンパイル SP では、データベースの照合順序が Japanese_CI_AS の場合には、パラメーターのデータ型に char／varchar データ型を利用することができません。代わりに、nchar／nvarchar データ型（n 付きのデータ型）へ変更する必要があります。

なお、コードページが「1252」の照合順序（Latin_～ など）を利用することで、char／varchar データ型を利用することもできますが、これについては後述します。

◆ プロシージャ内の単一引用符には Nプレフィックスが必要

データベースの照合順序に Japanese_CI_AS など、コードページが「1252」以外の照合順序を利用している場合には、ネイティブコンパイル SP 内の単一引用符（文字列リテラル）には、Nプレフィックスを付ける必要があります。もし、Nプレフィックスを付けていない場合は、次のようにエラーになります。

00123

このエラーを回避するには、N'AAA' のように、文字列を Nプレフィックスで囲むようにします。

Nプレフィックスは、nchar／nvarchar データであることを示すためのものなので、Nプレフィックスがない場合は、char／varchar データと見なされてしまって、先ほどと同じエラーが発生してしまいます。このエラーメッセージは、私自身がよく目にして、エラーメッセージだけだと理由が一瞬分からないことがあったので、覚えておくことをお勧めします。例えば、日付データを次のように単一引用符で囲んだ場合にも同様のエラーが表示されます。

00124

この場合も、日付を N'2014/06/14' のように Nプレフィックスで囲まなければなりません。

◆ SELECT * を利用できない～列名を列挙するように変更する～

SELECT ステートメントをネイティブコンパイル SP 化する場合は、「SELECT *」（* を利用して全ての列の取得）を利用することができません。ネイティブコンパイル SP では、次のように「SELECT *」を利用すると、構文エラーになります。

00125

SELECT ステートメントの選択リストは、「SELECT col1, col2, …」のように、取得したい列名をすべて列挙しなければなりません。

00126

すべての列を手書きで列挙するのは、取得したい列がたくさんある場合には面倒なので、私はこれを回避するために、オブジェクトエクスプローラーで次のように該当テーブルを右クリックして、［上位 1000件の選択］をクリックして、列名を自動生成させたものを利用しました。

00127

これを利用すれば、簡単に列名を列挙することができます。

Note：ネイティブコンパイル SP の dll が作成される場所
ネイティブコンパイル SP は、CREATE PROC でストアドプロシージャを作成したときに、コンパイルされた DLL が作成されます。この DLL は、SQL Server をインストールしたフォルダーの Data\xtp フォルダー配下に格納されています。
00128

「xtp_p_7_405576483.dll」形式のファイルが、コンパイルされた DLL です。_7 の部分はデータベース ID、_405576483 はオブジェクトID で、オブジェクトIDは、次のように sysobjects システムテーブルを参照して確認することもできます。
00129