株式会社ジーワンシステムの代表取締役。 新しいものを生み出して世の中をあっといわせたい。イノベーションってやつ起こせたらいいな。

バッチ処理はSQLの一括処理で行おう!(2)

»

 前回コラムで、普通と思った人と、なるほどと思った人と、何じゃこりゃ? と思った人と、同じ業務システムを担当している技術者でも3種類に分かれたと思う。コメント欄でもそうなりました。

 読者を選ぶ内容になっていてすみません。初心者向けの解説はありませんので、今回は、もうほんの少し難しいかもです。ある程度以上のスキルの人にはかなり有益かも知れないし、知ってるよって人も多いかも知れない(サイレントマジョリティの考えていることは分からない)。

◇    ◇    ◇    ◇

 例えば、採番マスタというのがあったとする。それを利用して採番する関数を作る。

CREATE OR REPLACE FUNCTION GET_SEQ_NEXTVAL
-- (引数は要件によって適当にね。)
RETURN VARCHAR2
IS
vNo NUMBER(10,0);
vCD VARCHAR2(12);

-- 自律型トランザクションにする
PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

SELECT MAX(NO) + 1 INTO vNo FROM 採番マスタ
-- WHERE なんか条件
;

UPDATE CNT SET NO = vNo;

-- 条件によってアルファベットが付く
-- 21世紀になってもこの仕様は引きずるのね……。

vCD := 'xx' || TO_CHAR(vNo, 'FM0000000000');

COMMIT;

RETURN vCD;

END;
/

 テストしてみる。

SELECT GET_SEQ_NEXTVAL() AS キー FROM DUAL
UNION ALL
SELECT GET_SEQ_NEXTVAL() AS キー FROM DUAL
UNION ALL
SELECT GET_SEQ_NEXTVAL() AS キー FROM DUAL
UNION ALL
SELECT GET_SEQ_NEXTVAL() AS キー FROM DUAL
UNION ALL
SELECT GET_SEQ_NEXTVAL() AS キー FROM DUAL;

 実行するたびに、カウントアップされてたキーが出力されるはずです。

 もちろん、

INSERT INTO テーブル
  (カラム羅列)
VALUES
  (GET_SEQ_NEXTVAL() , カラム羅列)

のパターンもありですよ。

 Oracleのソースになっていますが、Oracleのときは採番マスタテーブルを作らなくても、オラクルシーケンスを使ったら良いのですけれどね。

◇    ◇    ◇    ◇

 それで、前回の応用。

 まぁ、単純にはイカンだろうけれど、上の採番ルールを使うトランザクションテーブル(TableA)に、15種類のA区分と、20種類のB種別があって、全パターン(15×20=300種)のテストが必要だったとする。

 ベースのデータ(A区分 = '00'、B種別 = 'AA')を数件登録する。

 テストデータを作る。

INSERT INTO TableA
  (カラムの羅列)
SELECT
  GET_SEQ_NEXTVAL() -- キーになる
  , a.区分
  , b.種別
  , 他のカラムの羅列
FROM TableA
, (SELECT * FROM A区分 WHERE 区分 <> '00') a
, (SELECT * FROM B種別 WHERE 種別 <> 'AA') b

 これで、手作業で作られたデータを300種類に倍増できました。
 (※ ファンクションは遅いから負荷テストに使うのは適当でない)

 もちろん、結合テストでシナリオに沿ったデータでテストするわけですが、バッチの単体テストはこれぐらいでできるわけです。

◇    ◇    ◇    ◇

 上のファンクションは「僕たちが理解できないから止めてください」って、外側の言語に持ってかれることが多く、現場で使えないことが多いな。しかし、こういうのをDB側から提供するだけで、どれだけ工数が減るか……。ちょっと待ち時間があったので書きましたが、30分掛からなかったよ。

 何じゃこりゃ? と思う人が多いのは分かっています。そういう人は、少なくともSQLよりOO言語の方がおそらく得意でしょう。とにかく、何じゃこりゃ? と思う人は、RDBMSは使いこなせていません。使いこなせない人が触ったり、設計したりしない方がよいのは当たり前の話。

 「できない人に合わせる」のと、「分けてそれぞれ専門性を持たせよう」と考えると、絶対に分けた方が効率的ですよね。

 OO言語が得意な人はユーザインタフェイスの設計、実装担当とする。それでイイじゃないの。

 「テーブル設計は実装の後に!」のパターンに持っていければ、担当もきれいに別れてベストなのですけれど。

◇    ◇    ◇    ◇

 しんどいな~。東京に行ったらもっと仕事があるのだろうか……。出稼ぎに出ようかな。

Comment(16)

コメント

読み難かったので意図が違ってたらごめんなさい。
①採番ロジックはDB側に持たせると良いよ!
②その理由の一つとして
 単体テスト用のデータで、A区分と、B種別と採番部分以外は適当な
 データを作るのであれば、採番ロジックをDB側に持たせていると簡単だよ!

であってますか?

個人的には採番ロジックをDB側に持たせるのは賛成なのですが、
上記テストデータを作成するだけならば、採番ロジックはDB側で無くても
大して変わらない気がしたのですが、気のせいでしょうか。

宝春

こんばんは。

私も正直どこを伝えたいのかわかりづらかったのですが
・シーケンスオブジェクト(PrimaryKey)を作るにはこんな方法もあるよ。
・cross join で、簡単に n 倍 (n * m 倍) できるよ。
・で、結局「DB側でできることはやっちゃえば、UI側が楽になるじゃん。」
ってことなのですかね?

いつもOracleばかりなので、ぱっと見は「なんで、わざわざ???」でした。

でも「何じゃこりゃ?」になるポイントがわからない・・・。
また読み違えているのかなぁ・・・。

wona

SQLは苦手意識が強いのですが、さすがにこのくらいはわかります。。。(笑)

生島さん、こんばんは。
今回の記事は単に小休止な感じで受け取りました。

本文にあるようなプロセスは、私も当り前にやってる”作業”です。あえて作業という単語をチョイスしたのは、頭脳労働では無いと思っているからです。顧客の業務改善を実現するほうが何百倍も頭を使いますからね。

例にある内容は『実装完了』と言える段階に持っていく上で、無意識レベルで行っています。正直に言ってしまうと、単体試験の前にやることです。生島さんもそうでしょうが、30分どころかタバコ1本吸い終わる時間で出来ますよね。

現実では、本文にある簡易テストはオフィシャルな作業として報告する必要の無いもので(数百秒で済む)、実装した者にとっては当り前なことです。(あえてこれを言っとかないと、何だか生島さんだけが先走っている印象を持っている読者の方々が多そうなので・・・)

ところで東京進出の件ですが、私レベルですら『顧客⇒大手SI⇒弊社』の商流で70万/月を割ったことはこれまで一度たりともありません。今は不況の影響で70ジャストになってますが。検討してみる価値はあると思いますよ。

oumi

データの増量はSQLでも「簡単に」出来るのに、(解らない人がいる為に)別の言語で作らざるを得ない事があるのが、嘆かわしい。と言っているのでは?
今まで、この解らない人が設計するってことが問題だという事を散々言ってるし。
だから、SQL知識深めようぜとか、分担しようぜとか、いう事なんでしょう。

この記事の内容で、使いこなしている事の証明にはならないけど、使えてない事は証明できるわけですし。

でも
>上のファンクションは「僕たちが理解できないから止めてください。」って、外側の言語に持ってかれることが多く、現場で使えないことが多いな。
というのが実情だとすると、かなり自分の知っている世界とは違うなぁと思いました。
少なくとも、RDBMSを開発提供してきた大手ベンダーじゃこれは無いとおもうなぁ…っていうか今までは無かった。

皆さん、こんばんは。

できる人ばっかりが来ちゃったね。

本文にも書いたけれど、実際のプログラムには入らなくても、テストのために当たり前にやっている人もいるし、できない人もいる。できない人の中で、読んで気づく人も、読んでも分からない人もいるんです。

現場で、2:6:2ぐらいの割合なんじゃないかな。

プログラムに入らないところで、最終的に目に見えないから差がつくところだと思いますよ。

採番ロジックが外側にあって(二重に書くのはイヤだし)面倒なことになるのはよくあるのですけど……、採番ロジックがDB側にあればタバコ1本分ぐらいで標準。(私は吸わないが)

しかし、やってるのは2割ぐらいしかいないような気がする。
コメント消しても絡んで来るような人もいるからね~。

コメント欄が落ち着いている様ですので、質問させてください。

 失礼ですが、生島さんが訴えてらっしゃる既得権にしがみ付く人や、上から潰すという内容は、数年前の話なのでは?と思っているのが正直な感想です。


 私がこの業界にデビューした当時から、DB側に何らかの実装を施す考えは少数派でした。DBはあくまで”箱”という位置づけで、”ロジック”と呼べるものはJAVAなりに集約したほうが保守性が高い、という考えが常識となっていました。

 当時はGROUP BY、HAVING、表結合、副問い合わせ、ストアドファンクション・プロシージャ、DBトリガなどなど、『よくわかんない手法でパートナーに実装されてもなぁ』という風潮があり、自社の先輩でも、それこそ生島さんの仰る日本を代表する様な大手SIでもそうでした。

 ですが私には『上から潰された』という経験が無いんです。SQLの有効性を知らない元請けが相手でも、都度説得するだけで済みました。

 WEBを例にしますと、画面のレスポンスに耐えられないという理由で幾つかの夜間バッチ処理があり、受発注の状態を参照するのに一日のタイムラグがありましたがSQLをちゃんと(普通に)記述することで、それらのバッチを全滅させた経験があります。その際も大手SIが相手でしたが、何も問題無かったんですよね。(数人に恥をかかせる結果になりましたけど)

 近年は、大手もDBでやるべき処理はDBで、という切り分けが出来ていると思うのです。(もちろん人によりますが)単に私の運が良かったとも考えられますが、何の後ろ盾も、無い泣かず飛ばずの零細SIを渡り歩き10年選手になろうとしている私ですから、運だけじゃないのでは?とも考えています。

 感想として、もしかしたら生島さんは数年前の苦い体験をベースに執筆なさっており、以降は大手との契約を敬遠しているのでは・・・?と勘繰ってしまうのです。

 批判ではなく、事実確認からということでどうか・・・

ちょりぽんさん、おはようございます。

まぁ、現在進行形ですよ。
大手でも自信がないところは、「好きにやってください」というところもあるし、自信があるところほどアホなことを言い出します。

ストアドプロシージャは15%以内にしてくださいとか、トリガーは使うなとか、EXISTSは読みにくいのでINで書くこととかね。

あるんですよ。

上から潰すというのは、明確に「やめてくれ」というだけではなく、スタートラインのほんの一言で潰しているということです。バッチなんて想定するパフォーマンスが10倍以上違うのですから、完全に明後日のことを言ってますから。

saki1208

生島さん、こんばんは。

ちなみに、ウチでも潰されたことはないかなぁ。
かつては変なルールがたくさんあったし、今でも変なSQL書いてるヤツはごまん
といますけどね。

逆に提案段階で実現出来るか/出来ないかを聞かれることが多いですね。

choir

>「僕たちが理解できないから止めてください」
あるあるネタですね。
「保守する人が弄れないから止めてくれ」とか
「リファレンス読んでもわかんないからスクリーンショット付きでやり方教えれ」ってのと
同じぐらい良くある。。。

そんで黙って指示通り書き直して、終業後酒に逃げる。

oumi

>あるあるネタですね。
でも、大手が云々より、開発者さん達が知らなさすぎるという現状のほうが、より数例が多いと思うんだけど・・・
INDEXの使われ方すら解らん人たちがようけSQL書いてますよね・・・
実行計画すら見たこと無い人たちもようけおりますし・・・


>ストアドプロシージャは15%以内にしてくださいとか、トリガーは使うなとか、
>EXISTSは読みにくいのでINで書くこととかね。
こういうのも、一部の知っている人に対してではなくて、もっと全体感で見ると、そのようにせざるをえない現実があると思うんですけど・・・
全体品質を安定させるため、涙を飲んで(TT)という現状が。

仮に、世の中の技術者さんたちが、総じてOracle Gold持っています。と言ってくれるなら、問題無いのかもしれませんがね。

何でもかんでも大手ベンダーやSIerがっていう、言いっぷりがなぁ・・・
まったく逆に見える事の方が多いなやっぱ・・・

Cypher

oumiさん

>でも、大手が云々より、開発者さん達が知らなさすぎるという現状のほうが、より>数例が多いと思うんだけど・・・

大手SIerのコバンザメです。
私はどちらにも遭遇していますし、上流->下流な開発工程であれば、必然的に
大手側の人間の数は少ないはず。
となれば、開発者側の数例が多くなるのは当たり前のような気もします。

まあ、不勉強な人間はどこにでもいる、ということでいいんではないでしょうか?

生島さん、お疲れ様です。

> まぁ、現在進行形ですよ。
> 大手でも自信がないところは、「好きにやってください」というところもあるし、自信があるところほどアホなことを言い出します。

前者は丸投げした挙句に責任だけは追及してきますから、20代後半から保険を掛けるためのネゴシエーションを覚えたものです(こんなこと、誰も教えてくれなかった)。我ながら嫌らしい人間になった気がします(笑)

両者を7:3の割合で経験してのですが、自信がないから任せるというのは良くあるでしょうね。過去を振り返ってみても、『私の方がちょびっと知ってますよ?』的なオーラを放ちつつ、説得なり説明なりにあたってた気がします。

私はカナリやりたい放題やってこれた(自爆もありましたが)クチなので、「好きにやってください」系の得意先に偏っていたと思いました。

私は金融系は未経験ですので想像になりますが、かの分野ほど生島さんの仰る傾向はあるのかなと思いました。外しているかもしれませんけど、ノウハウの浅いテクノロジー(不得手とする技術)を採用して、運用段階で失敗したら致命傷ですから、実績や経験を他の分野より重んじる気がします。

追記です。

> バッチなんて想定するパフォーマンスが10倍以上違うのですから、完全に明後日のことを言ってますから。

これ、何度も体験しました。実装着手前のレビューにて、先方から『おいおい、そんなん作っても』と集中攻撃されたりしました。

こちらは当然の如く方針を発表してるのですけどね。前提としてる処理時間が違い過ぎるってことは良くありました。ですので、2~3時間で成果物に近いレベルのサンプルを作り込んで、『ホラ、こうなるっすよ』と現物を提示してました。

説得する上で、現物に勝るものはないですね。

ここまで書いて思ったのですが、チャッチャと作ったにも関わらず『サンプル』と『成果物』との差が少ないことが、SQLの利点な様に思えてきました。

SQL(ストアド含む)を、モックアップから本番用に移植するために大改修した、という経験は余りないので。

リリースするつもりの無かった、サクっと作ったDB周りの実装が成果物とニアリーだったことが多かったです。

EarlGrey

EarlGreyです、お久しぶりです。
SQL的にはマジョリティレベル(サイレントではない(苦笑)
にいると勝手に思ってます。

今回のコラムそのものとは関係なくて恐縮ですが、
『一括処理』という意味で仲間と捉えていただけると。

アクセスログのバッチ処理(日次100万件くらい)を、
スクリプト言語で実装というステキプログラムがありまして。
DBは使ってますが、ループで回すタイプの仕様です。

日次処理でだいたい1時間~1時間半くらいかかるんですが、
これの速度改善をしたい、という話になったため、
DBに全部突っ込んでSQLベースで処理してみました。
データ投入に1分、処理に30秒(5秒くらいのSQL6つ)でした。

生島さま

> バッチなんて想定するパフォーマンスが10倍以上違うのですから、完全に明後日のことを言ってますから。

「あれ?これバッチじゃなくてよくね?」と言われますね。
実際にバッチ系はほぼ仕様再検討になりそうです。
ある意味泣きが入りそうですが。

ちょりぽんさま

> チャッチャと作ったにも関わらず『サンプル』と『成果物』との差が少ないことが、SQLの利点な様に思えてきました

 本当にその通りですね。
 SQL実効結果をCSVに吐いてExcelで見せるだけ。
 これでほぼイメージを掴んでもらえたので、
 以降の話が非常にスムーズになります。

EarlGreyさん、こんにちは。

結局、工数、パフォーマンスとも、最初からSQLでやってたらよかったってことですよね。でも、ほとんどの場合、最初から明後日の方向を向いてそちらに進んでしまっています。

明後日を向いてしまってからは、私は指をくわえて見ているしかできない。
やっぱり、上流が変わるしかないと……。

コメントを投稿する