ネイティブコンパイルストアド プロシージャ(SQL Server)

ITコーディネータのシュウです。

blog103_1

この写真は、住んでいるところの近くにある、関東最古の大社の一つと言われている「鷲宮神社」の鳥居が再建されている様子です。以前、ブログで人気まんが作品『らき☆すた』の舞台となった神社ということで取り上げ、この神社の鳥居の写真を載せたことがありましたが、2018年8月11日に、それまで建っていた鳥居が老朽化のために倒壊してしまいました。倒壊した鳥居は、100年以上前に建てられた木製のものでしたが、老朽化で、根本のほうがボロボロだったようです。それ以降、解体されて何もない状態でしたが、今回鳥居が再建されることになり、12月3日の祭典終了後から、通れるようになるということです。鳥居がなくて寂しい思いがしていましたが、立派な鳥居が再建されて、とても嬉しい気がします。

<本日の題材>
ネイティブコンパイルストアド プロシージャ(SQL Server

前回のブログで、In-Memory OLTP 機能のメモリ最適化テーブル変数というものを取り上げましたが、今回はそれに関連する内容として、ネイティブコンパイルストアドプロシージャについて、試してみたいと思います。これは、メモリ最適化テーブルでの利用を想定したストアドプロシージャーです。
通常のSQL文によるクエリは、実行時にコンパイルしマシン語に変換したのちに実行されますが、ネイティブコンパイルストアドプロシージャーでは、マシン語に変換した状態のストアドプロシージャーをメモリに読み込ませておくため、従来のクエリよりも素早く実行できるのが特徴です。
ネイティブコンパイルとは、プログラミングの構造をネイティブコードに変換する処理であり、追加のコンパイルまたは解釈を必要としないプロセッサ命令で構成されると説明されています。
https://docs.microsoft.com/ja-jp/sql/relational-databases/in-memory-oltp/native-compilation-of-tables-and-stored-procedures?view=sql-server-ver15

例)
今回は、前回のメモリ最適化テーブル変数を使った処理とほぼ同様の内容を、ネイティブコンパイルストアドプロシージャで実行するようにしてみたいと思います。

ネイティブコンパイルストアドプロシージャの基本的な構文は以下となります。

CREATE PROCEDURE ストアドプロシージャ名
パラメーター定義
WITH
   NATIVE_COMPILATION,
   SCHEMABINDING
AS
BEGIN ATOMIC
WITH (
   TRANSACTION ISOLATION LEVEL = SNAPSHOT,
   LANGUAGE = N'japanese')
     -- 実行したいステートメント
END

ネイティブコンパイルストアドプロシージャを作成するには、CREATE PROCEDURE 文で「WITH NATIVE_COMPILATION」、「SCHEMABINDING」を指定する必要があります。「BEGIN ATOMIC」と「END」で行いたい処理を囲むかたちになり、「TRANSACTION ISOLATION LEVEL」でトランザクションの分離レベルを指定します。LANGUAGE は、日付フォーマットやシステムメッセージをどの言語にするかを指定するもので、「japanese」とすることで、日本語の日付フォーマット、およびメッセージを表示できるようになります。

それでは、前回のブログの内容に合わせたネイティブコンパイルストアドプロシージャを作成します。まず最初に、今回は、変数ではなく、メモリ最適化テーブルを作成します。

CREATE TABLE dbo.mem_test_tab(
id numeric(8)
,名前 nvarchar(20)
,区分 nvarchar(2)
,ポイント int
,CONSTRAINT PK_mem_test_tab PRIMARY KEY
NONCLUSTERED HASH (id) WITH ( BUCKET_COUNT = 500000 )
) WITH ( MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA)

blog_103_2

ここで、プライマリーキーのところでハッシュインデックスを設定し、さらに、WITHの後に「MEMOERY_OPTIMIZES=ON」と、「DURABILITY」を設定します。「DURABILITY」には、「SCHEMA_AND_DATA」と「SCHEMA_ONLY」が指定できます。SCHEMA_AND_DATA は、テーブルに持続性があり、変更がディスクに保存され、再起動またはフェールオーバー後も存続することを示しますが、SCHEMA_ONLY は、テーブルに持続性がないことを示します。

前回は、変数でしたので、一連の処理の中でデータを変数にセットしましたが、今回は、先ほど作成したメモリ最適化テーブルに、最初に50万件のデータを登録しておきます。

DECLARE @i int = 0
WHILE @i < 500000
BEGIN
   SET @i = @i + 1
   INSERT INTO dbo.mem_test_tab(id, 名前, 区分, ポイント)
   VALUES(@i, '顧客名_'+ RIGHT('000000'+CAST(@i as nvarchar),6), CAST((1+ABS(CHECKSUM(NewId())) % 9)*10 AS NVARCHAR), ABS(CHECKSUM(NewId())) % 100)
END

blog_103_3

データを確認してみます。
SELECT * FROM dbo.mem_test_tab
ORDER BY id;

blog_103_5

それでは、ネイティブコンパイルストアドプロシージャを作成します。

CREATE PROCEDURE dbo.USP_inmemory_test
  @区分1 nvarchar(2),
  @区分2 nvarchar(2),
  @区分3 nvarchar(2),
  @区分4 nvarchar(2),
  @区分5 nvarchar(2)
WITH
   NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC
WITH ( TRANSACTION ISOLATION LEVEL = SNAPSHOT,
        LANGUAGE = N'japanese')
 
-- メモリ最適化テーブル変数に格納した結果を集計
      SELECT 区分, COUNT(*) 件数
         FROM dbo.mem_test_tab
      WHERE 区分 = @区分1
                 OR 区分 = @区分2
                 OR 区分 = @区分3
                 OR 区分 = @区分4
                 OR 区分 = @区分5
          GROUP BY 区分
          ORDER BY 区分;
END
GO

blog_103_4

ネイティブコンパイルストアドプロシージャを実行します。

DECLARE @区分1 nvarchar(2) = '20',
       @区分2 nvarchar(2) = '40',
       @区分3 nvarchar(2) = '50',
       @区分4 nvarchar(2) = '70',
       @区分5 nvarchar(2) = '90';
     
EXEC dbo.USP_inmemory_test @区分1, @区分2, @区分3, @区分4, @区分5

blog_103_6

こちらも、ほとんど時間はかからずに結果が表示されました。
今回は、ネイティブコンパイルストアドプロシージャを作成して、動作することを確認しました。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

メモリ最適化テーブル変数(SQL Server)

ITコーディネータのシュウです。

IMG_4486

この写真は、前回のブログで写真を載せた、大根島にある「由志園」に行ったときに、ついでに訪れた、島根半島の先のほうにある「美保神社」で撮ったものです。私も高校生の時に一度兄に連れて行ってもらったことがあったというかすかな記憶がありましたが、それ以来なので、ほとんど初めてという感じでした。この神社の祭神は、えびす様(事代主神)と大国主神の后の三穂津姫で、商売繁盛、海上安全、五穀豊穣、夫婦和合などの守護神として祀られています。実家が、毎年、正月には美保神社のお札を送ってもらうようにしているということを、道すがら初めて聞きました。父親も数十年ぶりに参ることができたと喜んでいたので、少し遠回りにはなりましたが、行って良かったと思います。

<本日の題材>
メモリ最適化テーブル変数(SQL Server

SQL Server2014から実装されたIn-Memory OLTP 機能に、メモリ最適化テーブルというものがあります。今まであまり試したことがなかったのですが、今回は、そのメモリ最適化テーブル変数というものを試してみたいと思います。

例)
テスト用テーブルを作成し、テストデータを50万件作成します。そこから検索したいデータを抽出し、テスト用テーブルの列定義と同じ設定のメモリ最適化テーブル変数に登録して、結果を抽出してみます。

まず、メモリ最適化機能を使用するために、データベースに MEMORY_OPTIMIZED_DATA で宣言された FILEGROUPを作る必要があります。今回は、既存のデータベース「BLOG」に追加します。

ALTER DATABASE BLOG ADD FILEGROUP BLOG_mod CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE BLOG ADD FILE (name='BLOG_mod1', filename='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\iBLOG_mod1') TO FILEGROUP BLOG_mod;

blog102_2

次に、データを登録する「 mem_test」テーブルを作成します。

CREATE TABLE mem_test(
id numeric(8)
,名前 nvarchar(20)
,区分 nvarchar(2)
,ポイント int
,Constraint PK_mem_teste Primary key(id));

blog102_3

このテーブルにテストデータを作成します。今回は、50万件を以下のように作成します。「id」項目には、1~500000までシーケンシャルに値を設定し、名前は「顧客名_」の後にidを頭0埋めで設定、区分は2桁で、10,20,30, ~90までをランダムに、ポイントは、1~100の整数をランダムに登録してみます。ランダムに値を設定するのは、RAND関数と、NewID関数を使用してみます。

--データの作成
DECLARE @i int = 0
WHILE @i < 500000
BEGIN
   SET @i = @i + 1
   INSERT INTO mem_test(id, 名前, 区分, ポイント)
   VALUES(@i, '顧客名_'+ RIGHT('000000'+CAST(@i as nvarchar),6), CAST(FLOOR(1 + RAND()*9)*10 AS NVARCHAR), 1+ABS(CHECKSUM(NewId())) % 100)
END

blog102_4

ランダム値についてですが、RAND関数は、0~1の間の数が生成されるので、指定範囲内の整数値乱数が必要な場合は、
SELECT FLOOR([FROM値] + (RAND() * ([TO値] - [FROM値] + 1)))
のようにすれば取得できます。今回は、10,20,..,90 という文字型の値にしたかったので、CAST(FLOOR(1 + RAND()*9)*10 AS NVARCHAR) としました。
また、NEWID関数は、uniqueidentifier データ型の値を返してきますが、データを数値にしてチェックするCHECKSUM関数と、負の数が返ってくる場合もあるため絶対値を取得するABS関数を使い、さらに求める値の範囲を考慮して、今回は1~100までの値のため、100で割った余りを使うかたちで、1+ABS(CHECKSUM(NewId())) % 100 としました。

データを確認してみます。
SELECT * FROM mem_test
ORDER BY id;

blog102_5

区分とポイントはランダムに値が登録されているのが確認できました。
それでは、メモリ最適化テーブル変数を使用するために、そのためのユーザー定義テーブル型を宣言します。通常と違うのは、「MEMORY_OPTIMIZED = ON」を付けることと、ハッシュインデックスを付与する点が異なります。

CREATE TYPE mem_test_type AS TABLE
(id numeric(8)
,名前 nvarchar(20)
,区分 nvarchar(2)
,ポイント int
,INDEX idx1 NONCLUSTERED HASH (id) WITH ( BUCKET_COUNT = 500000 )
) WITH ( MEMORY_OPTIMIZED = ON );

blog102_6

※HASHインデックスでは、BUCKET_COUNT(バケット数)を適切な値へ設定していないと、性能低下の原因に繋がるようです。(特に小さすぎると性能が大きくさがるとのこと)

ちなみに、最初に行った、データベースに MEMORY_OPTIMIZED_DATA で宣言された FILEGROUPを作成していなかった場合には、上記のタイプを作成しようとしたときに、「メモリ最適化テーブル を作成できません。メモリ最適化テーブル を作成するには、オンライン状態かつ 1 つ以上のコンテナーがある MEMORY_OPTIMIZED_FILEGROUP がデータベースに含まれている必要があります。」というエラーが出ます。

blog102_8

それでは、上記を使ってメモリ最適化テーブル変数を宣言し、指定した5つの区分についてのデータ件数を確認してみたいと思います。

-- メモリ最適化テーブル変数の宣言
DECLARE @retValue dbo.mem_test_type

-- メモリ最適化テーブルへデータの INSERT
DECLARE
       @区分1 nvarchar(2) = '20',
       @区分2 nvarchar(2) = '40',
       @区分3 nvarchar(2) = '50',
       @区分4 nvarchar(2) = '70',
       @区分5 nvarchar(2) = '90';
 
   INSERT INTO @retValue
   SELECT id, 名前, 区分, ポイント
   FROM dbo.mem_test
   WHERE 区分 = @区分1
              OR 区分 = @区分2
              OR 区分 = @区分3
              OR 区分 = @区分4
              OR 区分 = @区分5
 
-- メモリ最適化テーブル変数に格納した結果を集計
SELECT 区分, COUNT(*) 件数
FROM @retValue
GROUP BY 区分
ORDER BY 区分;

blog102_7

ほとんど時間はかからずに結果が抽出されてきました。
メモリ最適化テーブル変数を、一時テーブルのようなかたちで使用できることが確認できましたが、実際には、もっと適した使用法があるのではないかと思います。今回は、取りあえず使えることを確認してみました。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村

再帰SQLでの最大再帰数(SQL Server)

ITコーディネータのシュウです。

IMG_4445

IMG_4451

大変ご無沙汰しています。久しぶりにブログをアップいたします。この写真は、島根の実家に帰省したときに、父親が行ってみたいと言った、宍道湖の隣にある中海に浮かぶ大根島にある「由志園」(ゆうしえん)で撮ったものです。私も大根島に行くこと自体が初めてだったので、由志園というものを知らなかったのですが、牡丹の栽培と雲州人参と呼ばれる高麗人参で有名な大根島にある、池泉回遊式(池をめぐり回る形式)日本庭園です。大根島は日本一の牡丹苗の生産地であり、ゴールデンウィークの時期は、園内の池に三万輪の牡丹が浮かべ、歩道の両脇を牡丹で絨毯のように敷き詰めるなど、とても綺麗だということです。また、日本の高麗人参の産地は、雲州(島根)、信州(長野)、会津(福島)の三地域で、大根島については、かつて高麗人蔘が門外不出の産物であったため、島で栽培をしていることを隠すために「人蔘島」ではなく、「大根島」と呼ぶようになったという逸話があるようです。
父親が行きたいと言ったので訪ねてみましたが、自分が生まれた田舎でも、学生の時から東京に出てきたため、行ったこともなく知らないところがたくさんあることを改めて感じました。

コロナウィルス感染が少し落ち着いてきているこの頃、また第6波がいつ来るかと危惧されていますが、国内産の経口薬などが早く開発されて、以前のように気兼ねなく自然や観光地を訪ねることができるようになればと願います。

<本日の題材>
再帰SQLでの最大再帰数(SQL Server

以前、このブログで取り上げた、WITH句と共通テーブル式(CTE)という内容の中で、再帰SQLの例を上げましたが、今回は、再帰SQLの最大再帰数の設定ができることについて取り上げたいと思います。

 例)
2021年の1年間の日付と曜日の一覧(カレンダー)を、WITH句を使って表示するSQLを作成したいと思います。その際に、1年分の日数の再帰を行って抽出すると、以下のようになります。

DECLARE @StartDate date = '20210101';             -- 手抜き (;^_^A
DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 1, @StartDate));

;WITH seq(n) AS
(
SELECT 0
UNION ALL
SELECT n + 1
FROM seq
WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d, youbi) AS
(
SELECT DATEADD(DAY, n, @StartDate),
DATENAME(WEEKDAY, DATEADD(DAY, n, @StartDate))
FROM seq
)
SELECT d, youbi
FROM d
ORDER BY d;

最初のWITH句で、@ StartDate(2021/1/1)から、@CutoffDate date(@StartDateから1年後-1日、つまり2021年の年末)までの日数分の 0~364までの365件のレコードを再帰処理を使って作成しています。(DECLARE @StartDate date = '20210101'; は、本来は'2021-01-01'ですが、暗黙の型変換で日付と認識してくれています。)
その後、次のWITH句の内容で、それを使ってその期間の日付と曜日のレコードを作成し、最後にそれを抽出する処理になります。ちなみに、何曜日を表わすのに、DATENAME関数を使っています。
しかし、これを実行すると、最大再帰数に達成したというエラーが表示されます。

blog101_3

「ステートメントが終了しました。ステートメントの完了前に最大再帰数 100 に達しました。」というエラーメッセージが表示されます。再帰SQLでは、無限ループ等が発生した場合のことを考慮して、再起の出来る回数の上限値が設定されており、100行を超えるとエラーになるようです。
この上限値を変更するのが、「MAXRECURSION」というオプションになります。

設定方法は
OPTION (MAXRECURSION 上限値)
で、設定できる上限値は、0 ~ 32767 で、「0」は上限なし、デフォルトは「100」です。

先ほどのSQLに、OPTION (MAXRECURSION 0) を追加して実行してみます。

blog101_4

実行すると、エラーなく 365件分、2021/1/1~2021/12/31までの日付と曜日が表示されます。今回は、上限なしの「0」を設定しましたが、1年の日数である「365」を設定しても、同様の結果になります。

今日は以上まで

にほんブログ村 IT技術ブログへ
にほんブログ村