カテゴリー別アーカイブ: 一時テーブル

一時テーブルのキー(SQL Server)

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

img_0509

毎朝、自転車で通っている道にコスモスが植えてあるので、ちょこっと写真を撮ってみました。

さて、dbSheetClientのユーザー事例にJR九州様が載りました。
Excelを利用して行ってきた勘定明細内訳システムを、dbSheetClientを採用してシステム化したということ。詳しくは、こちらを参照してみてください。
http://www.newcom07.jp/dbsheetclient/usrvoice/jr_kyushu.html

JR九州様と言えば、東京証券取引所に株式を上場したということで、とてもホットなニュースになっていますね。今年の大型IPO(新規株式公開)案件としては、LINEに次ぐ案件として注目を浴びていましたが、初値は公募価格を大きく上回る金額になったとか。今後の動きにも注目です!

<本日の題材>
一時テーブルのキー(SQL Server)

以前、一時テーブルについての記事をアップしたことがありました。一時テーブル一時テーブル(2)。そのときには、一時テーブルの主キーやインデックスなどのキーについては特に記事としては上げていませんでしたが、今回、実際のシステムの開発時に気付いたことがありましたので、それについて取り上げてみたいと思います。

 ストアドプロシージャなどの中で、一時テーブルを作成してそこに一旦データを登録し、それを使っていろいろな条件によって処理を実行するようなことがよくあるかと思いますが、データ量が多くなると、やはり処理速度が遅くなってきます。
(一時テーブルは、SQL Serverの場合は、「tempdb」というシステムデータベースを利用して処理が行われていますし、Oracleの場合も、テンポラリー用の表領域を使うかたちになります。)

ですので、インデックスを作成することで、処理を早めるということは可能になります。このとき、主キーを設定したいと思う場合もあるかと思いますが、そのときには注意が必要です。
実は、SQL ServerをDBとして一時テーブルを使った処理のレスポンスをアップしようと思ったときに、主キーを設定したのですが、Create table文のときに、実テーブル作成時と同じように、Constraint句で主キー制約を設定し、制約名を設定しました。すると、そのプロシージャをほぼ同時のタイミングで複数のユーザで実行したところ、制約名が同じという理由で主キーを作成できないというエラーが起きてしまいました。
一時テーブルだから、別々のユーザで行う別々のセッションの処理では問題なく動作するはずという認識でいたのに、思わぬ落とし穴に引っかかってしまったような感覚でした。
ですので、一時テーブルでパフォーマンスを上げるためにインデックスが必要であれば、主キーではなく普通のインデックスを作成するほうが無難かと思われます。処理として主キー制約が必要な場合は、Create table 文で一時テーブルを作成した後に、Alter table文で主キーを追加するようにし、そのときに制約名を指定しないかたちを取る方法がよいかと思われます。

例)
以前ブログに一時テーブルの内容を取り上げたときの例を使い、主キーを設定して試してみたいと思います。そのときに、処理がすぐに終わらないように、一時テーブル作成後に、30秒間待機させるコマンド「WAITFOR DELAY」を設定します。(「スリープ処理」という標題で以前ブログで取り上げました)

CREATE PROCEDURE dbo.顧客購買履歴情報抽出
        @顧客ID         VARCHAR(8)
AS
BEGIN
   -- 一時テーブルの作成
       CREATE TABLE #顧客初回購入(
               顧客ID                 VARCHAR(8)  NOT NULL,
               初回購入日           DATETIME,
              CONSTRAINT PK_t顧客初回購入 PRIMARY KEY (顧客ID)
       );
       CREATE TABLE #顧客購入履歴(
               顧客ID              VARCHAR(8)  NOT NULL,
               累計購入回数    decimal(7),
               累計購入金額    decimal(9),
              CONSTRAINT PK_t顧客購入履歴 PRIMARY KEY (顧客ID)
       );

       WAITFOR DELAY '00:00:30'                -- 30秒間待機

   -- 初回購入日の抽出結果を一時テーブルに登録
        INSERT INTO #
顧客初回購入
        SELECT
顧客ID, MIN(出荷日)
          FROM dbo.
売上
         WHERE
顧客ID = @顧客ID
         GROUP BY
顧客ID;

   -- 累計購入情報の抽出結果を一時テーブルに登録
        INSERT INTO #
顧客購入履歴
        SELECT
顧客ID, COUNT(*), SUM(売上金額)
          FROM dbo.
売上
         WHERE
顧客ID = @顧客ID
         GROUP BY
顧客ID;

    -- 顧客購買履歴情報の表示
        SELECT
          A.
顧客ID
        , FORMAT(A.
初回購入日,'yyyyMMdd') AS 初回購入日
        , B.
累計購入回数
        , B.
累計購入金額
         FROM #
顧客初回購入 A
         JOIN #
顧客購入履歴 B ON A.顧客ID = B.顧客ID;
END;
GO

このプロシージャの実行を、2つのセッションで同時に行ってみます。
1つめのセッションで以下を実行:

DECLARE @顧客ID varchar(8)='K0000054'
EXEC dbo.顧客購買履歴情報抽出
 @顧客ID

すると、30秒後に以下のような結果が返ってきます。

blog71_exec_1

上記とほぼ同じタイミングで、別のセッションで、顧客番号を変えてプロシージャを実行します。

DECLARE @顧客ID varchar(8)='K0000011'
EXEC dbo.顧客購買履歴情報抽出
 @顧客ID

すると、下記のようにエラーが返ってきます。

blog71_exec_2

1つめのセッションのプロシージャで一時テーブルを作成し、それを使った処理が終わってないうちに、2つめのセッションの処理を行おうとすると、一時テーブル作成時に、主キーに設定した制約名が既に存在しているものと同じということでエラーになったということです。

ということなので、一時テーブルの作成時に主キーを設定する場合には、制約名を固定で指定しないかたちにしたほうがいいと思われます。
具体的には、以下のようにします。

CREATE PROCEDURE dbo.顧客購買履歴情報抽出
        @顧客ID         VARCHAR(8)
AS
BEGIN
   -- 一時テーブルの作成
       CREATE TABLE #顧客初回購入(
               顧客ID                 VARCHAR(8)  NOT NULL,
               初回購入日           DATETIME
       );
       CREATE TABLE #顧客購入履歴(
               顧客ID              VARCHAR(8)  NOT NULL,
               累計購入回数    decimal(7),
               累計購入金額    decimal(9)
       );

    ALTER TABLE #顧客初回購入 ADD PRIMARY KEY (顧客ID);
    ALTER TABLE #顧客購入履歴 ADD PRIMARY KEY (顧客ID);

       WAITFOR DELAY '00:00:30'                -- 30秒間待機

   -- 初回購入日の抽出結果を一時テーブルに登録
        INSERT INTO #
顧客初回購入
        SELECT 
顧客ID, MIN(出荷日)
          FROM dbo.
売上
         WHERE 
顧客ID = @顧客ID
         GROUP BY 
顧客ID;

   -- 累計購入情報の抽出結果を一時テーブルに登録
        INSERT INTO #
顧客購入履歴
        SELECT 
顧客ID, COUNT(*), SUM(売上金額)
          FROM dbo.
売上
         WHERE 
顧客ID = @顧客ID
         GROUP BY 
顧客ID;

    -- 顧客購買履歴情報の表示
        SELECT
          A.
顧客ID
        , FORMAT(A.
初回購入日,'yyyyMMdd') AS 初回購入日
        , B.
累計購入回数
        , B.
累計購入金額
         FROM #
顧客初回購入 A
         JOIN #
顧客購入履歴 B ON A.顧客ID = B.顧客ID;
END;
GO

上記のように修正したプロシージャをコンパイルし、その実行を、再度2つのセッションで同時に行ってみます。

1つめのセッションで実行:
すると、30秒後に以下のような結果が返ってきます。

blog71_exec_1

上記とほぼ同じタイミングで、別のセッションで、顧客番号を変えてプロシージャを実行します。

blog71_exec_2ok

今度は、こちらもエラーは出ず、30秒後には上記のような結果が返ってきました。

プロシージャの実行時に、SQL Server Management Studio のほうで一時テーブルを確認してみると、下記のように、2つずつ一時テーブルが作成されているのがわかります。

blog71_%e4%b8%80%e6%99%82%e3%83%86%e3%83%bc%e3%83%96%e3%83%ab

また、主キーである必要がなく、インデックスの作成でよければ、インデックスを追加するかたちで行うこともできます。
この場合は、ALTER TABLE … ADD PRIMARY KEY の部分を、CREATE INDEX xxx …に変更します。具体的には、

CREATE INDEX IX_t顧客初回購入 ON #顧客初回購入(顧客ID);
CREATE INDEX IX_t顧客購入履歴 ON #顧客購入履歴(顧客ID);

上記に変更したプロシージャをコンパイルして実行しても、先ほどと同様にエラーなく処理は実行されました。

今日は以上まで

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

一時テーブル(2)

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

DSC_2890

今回も、ITC関連のネタです。ITコーディネータ協会では、毎年この時期にITCカンファレンスというものを開催しており、今年が14回目です。今年は10月30日(金)~31日(土)で行われます。
今年の大会テーマは「IoT新時代におけるITコーディネータのエボリューション~クラウド・Industrie4.0の可能性~」。ドイツが取り組んでいるIndustrie4.0(第4次産業革命)というものを取り上げつつ、日本版Industrie 4.0と言われる「つながる工場」の動きやITコーディネータ協会としての取り組みを紹介するということです。
HPは以下:
http://www.itc.or.jp/activity/seminar/itc_conf2015/index.html

近年、スマートフォンに代表されるようなコンピュータを内蔵した機器がインターネットに接続され、さらには様々なモノにセンサーなどを取り付け通信ができ、それらがつながる世界というような内容でIOT(Internet of Things)「モノのインターネット」という言葉をよく耳にするようになりました。スマートハウスやスマート家電などのスマート~という名前もよく使われますね。「つながる工場」、今、ものづくりの世界で大きな変革のときを迎えつつあるようです。それらを駆使することで、大きな飛躍のチャンスの可能性があるかも知れません。

<本日の題材>
一時テーブル(2)

前回、一時テーブルの例としてOracleの場合を取り上げましたので、今回はSQL Serverについて例を上げて確認してみたいと思います。

SQL Serverの場合のローカル一時テーブル作成の構文:

CREATE TABLE #表名(
  列名  データ型
, 列名  データ型
, …
);

SQL Serverの場合のグローバル一時テーブル作成の構文:

CREATE TABLE ##表名(
  列名  データ型
, 列名  データ型
, …
);

一時テーブルの使用例として、売上テーブルから顧客の初回購入日や購買回数などの購買履歴の情報を抽出して表示するような単純な処理を、ストアド・プロシージャで実行する簡単な例を作成してみます。

CREATE PROCEDURE dbo.顧客購買履歴情報抽出
        @顧客ID         VARCHAR(8)
AS
BEGIN
        -- 一時テーブルの作成
        CREATE TABLE #顧客初回購入(
                顧客ID                  VARCHAR(8),
                初回購入日              DATETIME
                );
 
        CREATE TABLE #顧客購入履歴(
                顧客ID                  VARCHAR(8),
                累計購入回数    decimal(7),
                累計購入金額    decimal(9)
                );
 
-- 初回購入日の抽出結果を一時テーブルに登録
        INSERT INTO #顧客初回購入
        SELECT 顧客ID, MIN(出荷日)
          FROM dbo.売上
         WHERE 顧客ID = @顧客ID
         GROUP BY 顧客ID;
 
-- 累計購入情報の抽出結果を一時テーブルに登録
        INSERT INTO #顧客購入履歴
        SELECT 顧客ID, COUNT(*), SUM(売上金額)
          FROM dbo.売上
         WHERE 顧客ID = @顧客ID
         GROUP BY 顧客ID;
 
-- 顧客購買履歴情報の表示
        SELECT
          A.顧客ID
        , FORMAT(A.初回購入日,'yyyyMMdd') AS 初回購入日
        , B.累計購入回数
        , B.累計購入金額
         FROM #顧客初回購入 A
         JOIN #顧客購入履歴 B ON A.顧客ID = B.顧客ID;
END;
GO

Oracleとは違って、プロシージャの中で一時テーブルを作成し、それらをジョインして抽出結果を表示するサンプルになります。

上記のプロシージャに顧客IDのパラメータを渡して実行してみます。

DECLARE @顧客ID varchar(8)='K0000011'
EXEC dbo.顧客購買履歴情報抽出
  @顧客ID

sqlsv_一時表prc_実行1

別の顧客IDをパラメータで渡して、再度実行結果を確認すると、

DECLARE @顧客ID varchar(8)=' K0000054'
EXEC dbo.顧客購買履歴情報抽出
  @顧客ID

sqlsv_一時表prc_実行2

さて、SQLServerの場合のグローバル一時テーブルについては、あまり使ってみたことがないですが、どういうときに使うかを調べてみたところ、排他処理で使うケースがあるようです。

悲観的排他制御を行いたいときに、アプリケーションでコントロールする場合には、最初に排他用のフラグを立てて、そのフラグが立っている場合は、他のユーザは更新などの処理が実施できないというようなことを行うことがあると思います。
ただ、このフラグの制御を実テーブルで行うと、予期せぬかたちで処理が強制的に終了してしまった場合に、フラグが立ったままになってしまい、排他の状態が続いてしまって更新ができなくなってしまうなどというケースが出てきてしまうため、何らかのかたちで解除する方法を検討しなければなりません。

このときに、このフラグの制御を実テーブルではなく、グローバル一時テーブルを作成するというかたちで行うと、強制的に終了した場合でも、グローバルテーブルが自動的に削除されることで排他状態ではないと容易に判断できるということになるということです。

簡単に試してみたいと思います。

★排他をかけたいとき

CREATE TABLE ##排他チェック(
        USERID          VARCHAR(20),
        処理時刻                DATETIME
        );

INSERT INTO ##排他チェック
 SELECT '00001', GETDATE();

sqlsv_globaltemp1

同一のセッションで、このグローバル一時テーブルの中身を確認してみます。

SELECT * FROM ##排他チェック;

sqlsv_globaltemp_select1

これを、全く別のセッションで異なるUSERIDのメンバーが抽出してみます。

SELECT * FROM ##排他チェック;

sqlsv_globaltemp_select2

 ローカル一時テーブルとは異なり、他のセッションからもデータを確認できます。

ここで、元のグローバル一時テーブルを作成したセッションの接続を解除、もしくは実行していたウィンドウの右上の×の箇所をクリックして閉じてから、再度先ほど確認できた別のセッションからデータを確認してみます。

sqlsv_globaltemp_解除後select3

グローバル一時テーブルが接続の解除とともに自動的に削除されたために、データの抽出ができないことが確認できます。

非常に簡単なテストではありますが、このグローバル一時テーブルの存在チェックを行って、存在していれば排他中のため更新不可であり、存在しなければ排他中ではないため、処理を実行してよいという制御をある程度行うことができそうです。

例えば
IF OBJECT_ID(N'tempdb..##排他チェック', N'U') IS NOT NULL
  print '他のユーザが使用中のため、実行できません'
ELSE
  print '処理を実行します'

上記の処理を、先ほどと同様のタイミングで実行すると、最初に他のセッションでグローバル一時テーブルを作成したタイミングでは、

sqlsv_globaltemp_test1

グローバル一時テーブルを作成したセッションの接続を解除した状態では

sqlsv_globaltemp_test2

実際には、後から接続して処理を実行する側でも、処理の開始時点で同様に排他用のグローバル一時テーブルを作成するという処理を行うかたちになると思います。

今回は、SQL Serverについて、ローカル一時テーブルだけでなく、グローバル一時テーブルについても試してみました。

今日は以上まで

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

一時テーブル

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

DSC_2888

ラグビーのワールドカップでは、日本が24年ぶりの勝利、それも世界ランク3位の南アフリカを倒すという歴史的大金星を上げて、世界中が驚いたということがありました。開催地イギリスの地元の新聞も、「UNBELIEVABLE」」という題で大きく日本の勝利を取り上げ、会場やその近辺では日本人というだけで、「素晴らしい試合だった。おめでとう!」という祝福を受けたということです。
本当にスポーツは素晴らしいですね。

さて、マイナンバー制度の施行もいよいよ間近に迫ってきました。会社などでもいろいろと教育を受けられた方も多いと思います。ITコーディネータ協会でも、ITコーディネータ協会が進めるマイナンバーへの取組みという内容でHPで取り上げています。
http://www.itc.or.jp/mynumber/
興味のある方は、是非ご確認ください。

<本日の題材>
一時テーブル

ストアドプロシージャなどで複雑な処理を行うような場合に、1つのSQLで一度に結果を導き出すことは難しいため、第一段階としての抽出の結果を一時的に登録する一時テーブルを用意し、その結果を元に次のステップとしての必要な処理を行い、最終的に目的のデータを登録するとか、結果を抽出するということがよくあると思います。

この一時テーブルですが、Oracleの場合とSQL Serverの場合で多少使い方が異なります。
Oracleの場合は、セッション(デフォルトではトランザクション)が終了するとセッションの中で作成したデータは消えますが、テーブル自体は永続的なものであり、セッションが切れてもテーブルは残ります。そのため、プロシージャなどとは別に、あらかじめテーブルを作成しておく必要があります。
※デフォルト(ON COMMIT DELETE ROWS)ではトランザクションがコミットされると一時表のデータは削除されますが、設定によって(ON COMMIT PRESERVE ROWS)は、トランザクションが終了後もデータは残り、セッション終了時にデータが切り捨てられます。

これに対して、SQL Serverの場合、プロシージャなどの中で一時テーブル自体も作成し、データを登録して使うかたちで、セッションが終了するとテーブルも自動的に削除されます。
ただし、SQL Serverでは、ローカル一時テーブル(テーブル名が#で始まる)とグローバル一時テーブル(テーブル名が##で始まる)があり、一時テーブルを作成したセッションのみから参照できるローカル一時テーブルと、別セッションからも参照でき、全ての参照がなくなったときに自動的に削除されるグローバル一時テーブルという違いがあります。

Oracleの場合の一時表作成の構文:

CREATE GLOBAL TEMPORARY TABLE 表名(
  列名  データ型
, 列名  データ型
, …
[ON COMMIT DELETE ROWS | ON COMMIT PRESERVE ROWS]
);

一時テーブルの作成例:
CREATE GLOBAL TEMPORARY TABLE temp_sample1(
 CD VARCHAR2(10) PRIMARY KEY
,NAME VARCHAR2(20)
,SURYO NUMBER(7)
);
cre_temptb

以前、ブログで使用した商品マスタ、及び売上テーブルから、2014年11月に購入した商品と数量について、一時テーブルに登録します。

INSERT INTO temp_sample1
SELECT U.商品CD, S.商品名, SUM(U.商品数量)
  FROM 売上テーブル U
  JOIN 商品マスタ S ON U.商品CD = S.商品CD
 WHERE TO_CHAR(受注日,'YYYYMM') = '201411'
 GROUP BY U.商品CD, S.商品名;

insert_temptb

そのままデータを確認します。

SELECT * FROM temp_sample1
 ORDER BY CD;
select_temp

このとき、別のユーザが異なるセッションでこの一時テーブルのデータを確認すると、

SELECT * FROM temp_sample1
 ORDER BY CD;
select_temp2

一時テーブルのデータは、他のセッションからは参照できないことがわかります。

また、データを登録したほうのセッションで、COMMITを実行してからSELECTしてみます。

COMMIT;

SELECT * FROM temp_sample1
 ORDER BY CD;

commit_sel_temp

COMITすると、一時テーブルのデータは削除されることがわかります。

それでは、トランザクション終了後もデータが残る設定(ON COMMIT PRESERVE ROWS)の設定で一時テーブルを作成してみます。

CREATE GLOBAL TEMPORARY TABLE temp_sample2(
 CD VARCHAR2(10) PRIMARY KEY
,NAME VARCHAR2(20)
,SURYO NUMBER(7))
ON COMMIT PRESERVE ROWS
;
cre_temptb2

同様にデータを登録し、COMMITします。

INSERT INTO temp_sample2
SELECT U.商品CD, S.商品名, SUM(U.商品数量)
  FROM 売上テーブル U
  JOIN 商品マスタ S ON U.商品CD = S.商品CD
 WHERE TO_CHAR(受注日,'YYYYMM') = '201411'
 GROUP BY U.商品CD, S.商品名;

COMMIT;

insert_temptb2

データを確認してみます。

SELECT * FROM temp_sample2
 ORDER BY CD;
select_temp3

今度は、COMMITしてもデータは消えず、SELECTで確認することができました。
以上、まずOracleについての一時テーブルの例について取り上げてみました。

今日は以上まで

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