SQL Server 2014 CTP2 を試してみる (3)ストアドプロシージャの Native Compile

さて、IN-MEMORY OLTP を試してみようと思う。

題材は「MAN+WOMAN=CHILD」
http://miko.org/~naruto/Artifact/MASKSQL.html

題材が「IN-MEMORY OLTP」の性能を測るのに適当かどうかは考えていない。

まずは通常のテーブルで試す。
Access 2003 (JET Database Engine)用の SQL だが、
文字列の結合がないのでそのまま SQL Server で動く。

さて、今度は MEMORY OPTIMIZED TABLE でやってみる。

まず、数のリストを持つテーブル「NumListM」を作成する。

CREATE TABLE NumListM(
num integer NOT NULL PRIMARY KEY NONCLUSTERED
HASH WITH (BUCKET_COUNT=10))
WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA );

数値に順序は関係ないので hash index にする。
今回はデータ数が 10固定なので BUCKET_COUNT として 10 を指定している。
実際には 2^n乗になるので 16 となる。
not null 制約は明示的に入れないとエラーになる。
また、PRIMARY KEY の既定は CLUSTERED だが、
MEMORY OPTIMIZED TABLE は Cluster化index はサポートしていないので
明示的に NONCLUSTERED を指定する。


作成した後は値を入れる

INSERT INTO NumListM(num) VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

すでに元となる SQL文はあるので不要だが、
例をなぞって文字のリストのテーブルを作成する。

CREATE TABLE dbo.CharListM(
c char(1) COLLATE Latin1_General_100_BIN2 NOT NULL
PRIMARY KEY
NONCLUSTERED
HASH WITH ( BUCKET_COUNT = 10))
WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA );

こっちもデータ数は固定なので BUCKET_COUNT として 10 を指定している。
実際には 2^n乗になるので 16 となる。
DB の照合順序が MEMORY OPTIMIZED TABLE でサポートされていないものの場合は
COLLATE文を用いて明示的に照合順序を指定する必要がある。
nchar型 ではなく、char型なのでコードページ1152 の照合順序である必要があり、
また index を付けるので BIN2 の照合順序である必要がある。
http://msdn.microsoft.com/ja-jp/library/dn133182(v=sql.120).aspx

同じく値を入れる

INSERT INTO CharListM(c) VALUES(’A’),(’C’),(’D’),(’H’),(’I’),(’L’),(’M’),(’N’),(’O’),(’W’);

でも、CharListM は使わず、問題を解くクエリを作成し、実行するｗ。

SELECT
(100 * M.num + 10 * A.num + N.num) AS MAN,
’+’ AS [PLUS],
(10000 * W.num + 1000 * O.num + 100 * M.num + 10 * A.num + N.num) AS WOMAN,
’=’ AS [EQUAL],
(10000 * C.num + 1000 * H.num + 100 * I.num + 10 * L.num + D.num) AS CHILD
FROM
NumListM AS A,
NumListM AS C,
NumListM AS D,
NumListM AS H,
NumListM AS I,
NumListM AS L,
NumListM AS M,
NumListM AS N,
NumListM AS O,
NumListM AS W
WHERE
((100 * M.num + 10 * A.num + N.num)
+ (10000 * W.num + 1000 * O.num + 100 * M.num + 10 * A.num + N.num)
= (10000 * C.num + 1000 * H.num + 100 * I.num + 10 * L.num + D.num))
AND (M.num>=1) AND (W.num >= 1) AND (C.num>=1)
AND (O.num W.num)
AND (M.num W.num)
AND (A.num W.num)
AND (N.num W.num)
AND (C.num W.num)
AND (H.num W.num)
AND (I.num W.num)
AND (L.num W.num)
AND (D.num W.num)
AND (M.num O.num)
AND (A.num O.num)
AND (N.num O.num)
AND (C.num O.num)
AND (H.num O.num)
AND (I.num O.num)
AND (L.num O.num)
AND (D.num O.num)
AND (A.num M.num)
AND (C.num M.num)
AND (H.num M.num)
AND (I.num M.num)
AND (L.num M.num)
AND (D.num M.num)
AND (M.num N.num)
AND (A.num N.num)
AND (C.num N.num)
AND (H.num N.num)
AND (I.num N.num)
AND (L.num N.num)
AND (D.num N.num)
AND (A.num C.num)
AND (A.num H.num)
AND (C.num H.num)
AND (D.num H.num)
AND (A.num I.num)
AND (C.num I.num)
AND (H.num I.num)
AND (D.num I.num)
AND (A.num L.num)
AND (C.num L.num)
AND (H.num L.num)
AND (I.num L.num)
AND (D.num L.num)
AND (A.num D.num)
AND (C.num D.num)

力技のクエリ(テーブル10個を Cross Join) なのでちょっと時間(でも30秒かかっていなかった)がかかるが
Memory Optimized Table 使ったほうが通常のテーブル使う場合より 1.2倍速かった。
(具体的な数値を出したら怒られるよね?)
う～ん、もっと速くなると思っていたのだがなぁ。。。

って上記を最初に試したのは 12/23 だった。

今、ふと思い立ち、
「In-Memory OLTP White Paper」
http://download.microsoft.com/download/5/F/8/5F8D223F-E08B-41CC-8CE5-95B79908A872/SQL_Server_2014_In-Memory_OLTP_TDM_White_Paper.pdf

の「Native Compilation of Tables and Stored Procedures」のところを読んでみた。
(まだこの White Paper 全部読み終わってない)

あたりまえだけど、ストアドプロシージャにしないと Native Compile されないよね～(^^;
と、いうわけでストアドプロシージャ化。

create procedure dbo.SoloveMask
with native_compilation, schemabinding, execute as owner
AS
BEGIN ATOMIC
with (transaction isolation level=snapshot, language=N’us_english’)
SELECT
(100 * M.num + 10 * A.num + N.num) AS MAN,
’+’ AS [PLUS],
(10000 * W.num + 1000 * O.num + 100 * M.num + 10 * A.num + N.num) AS WOMAN,
’=’ AS [EQUAL],
(10000 * C.num + 1000 * H.num + 100 * I.num + 10 * L.num + D.num) AS CHILD
FROM
dbo.NumListM AS A,
dbo.NumListM AS C,
dbo.NumListM AS D,
dbo.NumListM AS H,
dbo.NumListM AS I,
dbo.NumListM AS L,
dbo.NumListM AS M,
dbo.NumListM AS N,
dbo.NumListM AS O,
dbo.NumListM AS W
WHERE
((100 * M.num + 10 * A.num + N.num)
+ (10000 * W.num + 1000 * O.num + 100 * M.num + 10 * A.num + N.num)
= (10000 * C.num + 1000 * H.num + 100 * I.num + 10 * L.num + D.num))
AND (M.num>=1) AND (W.num >= 1) AND (C.num>=1)
AND (O.num W.num)
AND (M.num W.num)
AND (A.num W.num)
AND (N.num W.num)
AND (C.num W.num)
AND (H.num W.num)
AND (I.num W.num)
AND (L.num W.num)
AND (D.num W.num)
AND (M.num O.num)
AND (A.num O.num)
AND (N.num O.num)
AND (C.num O.num)
AND (H.num O.num)
AND (I.num O.num)
AND (L.num O.num)
AND (D.num O.num)
AND (A.num M.num)
AND (C.num M.num)
AND (H.num M.num)
AND (I.num M.num)
AND (L.num M.num)
AND (D.num M.num)
AND (M.num N.num)
AND (A.num N.num)
AND (C.num N.num)
AND (H.num N.num)
AND (I.num N.num)
AND (L.num N.num)
AND (D.num N.num)
AND (A.num C.num)
AND (A.num H.num)
AND (C.num H.num)
AND (D.num H.num)
AND (A.num I.num)
AND (C.num I.num)
AND (H.num I.num)
AND (D.num I.num)
AND (A.num L.num)
AND (C.num L.num)
AND (H.num L.num)
AND (I.num L.num)
AND (D.num L.num)
AND (A.num D.num)
AND (C.num D.num);
END;

schema binding するのに、Table の Schema名を明示しろと怒られたりした。
で、実行。

exec SoloveMask;

・・・(●_●)。
AdHoc で実行したときの 7倍、通常テーブルで実行したときの8倍くらいの速さ。

さすが Native Compile 機能。
これはおもしろかった。

---
2014年1月18日追記
ここから複写すると不等号が全角に成ったりするのでサンプルコードは下記から取得してください。
http://kazamai7610.wordpress.com/2014/01/03/%e3%80%8csql-server-2014-ctp2-%e3%82%92%e8%a9%a6%e3%81%97%e3%81%a6%e3%81%bf%e3%82%8b-3%e3%82%b9%e3%83%88%e3%82%a2%e3%83%89%e3%83%97%e3%83%ad%e3%82%b7%e3%83%bc%e3%82%b8%e3%83%a3%e3%81%ae-native-compi/