ITコーディネータのシュウです。
毎朝、自転車で通っている道にコスモスが植えてあるので、ちょこっと写真を撮ってみました。
さて、dbSheetClientのユーザー事例にＪＲ九州様が載りました。
Excelを利用して行ってきた勘定明細内訳システムを、dbSheetClientを採用してシステム化したということ。詳しくは、こちらを参照してみてください。
http://www.newcom07.jp/dbsheetclient/usrvoice/jr_kyushu.html
ＪＲ九州様と言えば、東京証券取引所に株式を上場したということで、とてもホットなニュースになっていますね。今年の大型IPO（新規株式公開）案件としては、LINEに次ぐ案件として注目を浴びていましたが、初値は公募価格を大きく上回る金額になったとか。今後の動きにも注目です！
＜本日の題材＞
一時テーブルのキー（SQL Server）
以前、一時テーブルについての記事をアップしたことがありました。一時テーブル、一時テーブル（２）。そのときには、一時テーブルの主キーやインデックスなどのキーについては特に記事としては上げていませんでしたが、今回、実際のシステムの開発時に気付いたことがありましたので、それについて取り上げてみたいと思います。
ストアドプロシージャなどの中で、一時テーブルを作成してそこに一旦データを登録し、それを使っていろいろな条件によって処理を実行するようなことがよくあるかと思いますが、データ量が多くなると、やはり処理速度が遅くなってきます。
（一時テーブルは、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
このプロシージャの実行を、２つのセッションで同時に行ってみます。
１つめのセッションで以下を実行：
DECLARE @顧客ID varchar(8)='K0000054'
EXEC dbo.顧客購買履歴情報抽出
@顧客ID
すると、30秒後に以下のような結果が返ってきます。
上記とほぼ同じタイミングで、別のセッションで、顧客番号を変えてプロシージャを実行します。
DECLARE @顧客ID varchar(8)='K0000011'
EXEC dbo.顧客購買履歴情報抽出
@顧客ID
すると、下記のようにエラーが返ってきます。
１つめのセッションのプロシージャで一時テーブルを作成し、それを使った処理が終わってないうちに、２つめのセッションの処理を行おうとすると、一時テーブル作成時に、主キーに設定した制約名が既に存在しているものと同じということでエラーになったということです。
ということなので、一時テーブルの作成時に主キーを設定する場合には、制約名を固定で指定しないかたちにしたほうがいいと思われます。
具体的には、以下のようにします。
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
上記のように修正したプロシージャをコンパイルし、その実行を、再度２つのセッションで同時に行ってみます。
１つめのセッションで実行：
すると、30秒後に以下のような結果が返ってきます。
上記とほぼ同じタイミングで、別のセッションで、顧客番号を変えてプロシージャを実行します。
今度は、こちらもエラーは出ず、30秒後には上記のような結果が返ってきました。
プロシージャの実行時に、SQL Server Management Studio のほうで一時テーブルを確認してみると、下記のように、２つずつ一時テーブルが作成されているのがわかります。
また、主キーである必要がなく、インデックスの作成でよければ、インデックスを追加するかたちで行うこともできます。
この場合は、ALTER TABLE … ADD PRIMARY KEY の部分を、CREATE INDEX xxx …に変更します。具体的には、
CREATE INDEX IX_t顧客初回購入 ON #顧客初回購入(顧客ID);
CREATE INDEX IX_t顧客購入履歴 ON #顧客購入履歴(顧客ID);
上記に変更したプロシージャをコンパイルして実行しても、先ほどと同様にエラーなく処理は実行されました。
今日は以上まで
にほんブログ村
