コムフェア株式会社 - Technology INSERT文に "ORDER BY"

Technology へようこそ
ここは技術者の「経験」と「ノウハウ」のブログです

2010年05月17日

INSERT文に "ORDER BY"

「レガシーシステムに渡すデータに連番を振る」という処理はSQLにとって
苦手な処理のひとつかもしれません。

そもそも、COBOLなどの一般的な事務処理言語はREAD～WRITEの繰り返しによっ
て処理が構成され、READとWRITEの処理の間（項目転送とか）で採番された
連番をアウトプットとして書き出す、というのはさほど難しい処理ではあり
ません。

しかし、同様の処理をSQLを使用して連番を振る、ということは繰り返し
（ループ）が前提となり、本来、SQLの文法を拡張したカーソル処理を行う
必要がある場合が多いものです。

以前からやってみたかったテーマだったのですが、SQLServerでT-SQLを使用
してカーソルを使用することなく、連番が振れないか？

以下のような作業用テーブルを用意します。

create table [dbo].[sWorkTable_SL] (
  [TENPO]		decimal(3, 0)
, [BUMON]		decimal(2, 0)
, [JANCD]		decimal(13, 0)
, [YMD]			varchar(6)
, [SURYO]		decimal(5,1)
, primary key clustered (
   [TENPO]
 , [BUMON]
)

create table [dbo].[sWorkTable_SL_SEQ] (
  [TENPO]		decimal(3, 0)
, [BUMON]		decimal(2, 0)
, [JANCD]		decimal(13, 0)
, [YMD]			varchar(6)
, [SURYO]		decimal(5,1)
, [SEQNO]		int IDENTITY(1,1)
, primary key clustered (
	[SEQNO]
	)

[dbo].[WorkTable_BASE]から[dbo].[WorkTable_SEQ]にデータを写すとき、
[SEQNO]をIDENTITYとして定義しておき、任意の順番でINSERTすることができ
れば、連番を振れるわけですが・・・。

用意したSQLは


insert into
  [dbo].[WorkTable_SL_SEQ]
    ( [TENPO]
    , [BUMON]
    , [JANCD]
    , [YMD]
    , [SURYO]
    ) select
      [TENPO]
    , [BUMON]
    , [JANCD]
    , [YMD]
    , [SURYO]
  from
    [dbo].[WorkTable_SL]
  order by
      [TENPO]
    , [BUMON]

というもの。INSERT-SELECT文にORDER BY句が付くと、ちょっと奇異な感じが
しますが・・・。

実行プランを見てみます。(クリックで拡大）

上記のINSERT文では、ORDER BY句が[dbo].[WorkTable_SL]のインデックスと同じ
なので、少々わかりにくいですが、インデックスに掛からない（使用されない）
ような項目（例えば [SURYO]など）に変えてみると、(クリックで拡大）

おわかりですか？sortが現われます。つまり、order by句が効いているということ
になります。
今回の例は単純なので、すべての連番に応用できるものではないことも事実なの
ですが。
もうひとつ気になるのは性能です。ためしに18万件ほどでやってみました。

cursor-loop: 120秒
INSERT-Order by: 20秒

差は歴然です。

[ posted by H.K ]

10:48 | SQLServer | トラックバック (0) | コメント (1) |

<< カーソルループの高速化(1) | メイン | NC >>

この記事に対するコメント

ROW_COUNT関数あるし‥普通にできるけど。

【2010/06/18 00:33】 URL | びっくら #- [ 編集]

この記事に対するコメントの投稿

| メイン |

この記事に対するトラックバック

トラックバックURL
→http://comfair2.blog24.fc2.com/tb.php/475-3b364d00
この記事にトラックバックする(FC2ブログユーザー)