FC2ブログ
 

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


--年--月--日

スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。


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 ]

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

ROW_COUNT関数あるし‥普通にできるけど。
【2010/06/18 00:33】 URL | びっくら #- [ 編集]



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














管理者にだけ表示を許可する



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











上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。