久しぶりの更新。今回もSQL＿20090906追記

今日はSQLのチューニングに終始してました。
こんなテーブルがあって、
受付(受付ID,受付日時,担当者ID,ステータス),担当者マスタ(担当者ID,担当者名)

受付日時の直近5か月前から未来まで一カ月毎の各ステータスの合計を出すっていうもの。

元のSQLを1時間調べてたんだけど、解釈内容に自信が持てず、全部作り直ししました。
FROM句に副々問い合わせ、SELECT句にも副問い合わせな感じで130行OVERのSQLだったので。。。

で、作り直した最終的なSQLは、こんな感じ(他の条件を全部はしょっているのだけれど。。)。共通テーブル式を使ってて、文字列加工をした列の値を2段目のselect文でgroupbyを掛けているので、たぶん内部では一時的にテーブルを作っているはず。なのでこの部分が改良の余地がありそうだけど。。

WITH 共通テーブル式 (受付月,担当者ID,担当者名,ステータス) AS
(select
substring(convert(varchar,受付日時,111),1,7),
受付.担当者ID,
担当者名,
ステータス
from 受付 left outer join 担当者マスタ
on 受付.担当者ID=担当者マスタ.担当者ID
where 受付日時 >=convert(datetime,substring(convert(varchar,dateadd(m,-5,getdate()),111),1,7)+'/01')
)
select
担当月,
担当者ID,
担当者名,
count(case when ステータス='1' then 担当月 end) as ステータス_1合計,
count(case when ステータス='2' then 担当月 end) as ステータス_2合計,
count(case when ステータス='3' then 担当月 end) as ステータス_3合計,
count(case when ステータス='4' then 担当月 end) as ステータス_4合計
from
共通テーブル式
group by 担当月,担当者ID, 担当者名
order by ....

ま、PIVOTは使っても使わなくても変わりなさそうだし、元は10分かかってたものが2秒になったので、これ以上はいいよね？
こんなものかな。

ついでに思い出せるだけ、昔のSQLを書いてみる。
確か、範囲内の全受付に対して、いちいち担当者毎に月のカウントを取ってdistinctをしていたはずだから、、、

select distinct
substring(convert(varchar,受付_O.受付日時,111),1,7) as 担当月,
受付_O.担当者ID,
担当者名,
(select count(受付_I.受付日時) from 受付 AS 受付_I
where 受付_I.受付日時 >=convert(datetime,substring(convert(varchar,dateadd(m,-5,getdate(),111),1,7)+'/01') and
受付_I.担当者ID=受付_O.担当者ID and 受付_I.ステータス='1' and
受付_I.受付日時 >=convert(datetime,substring(convert(varchar,受付_O.受付日時,111),1,7)+'/01') and
受付_I.受付日時 < convert(datetime,substring(convert(varchar,dateadd(m,1,受付_O.受付日時),111),1,7)+'/01') and
受付_I.ステータス= 受付_O.ステータス
) as ステータス_1合計,
(select count(受付_I.受付日時) from 受付 AS 受付_I
where 受付_I.受付日時 >= convert(datetime,substring(convert(varchar,dateadd(m,-5,getdate(),111),1,7)+'/01') and
受付_I.担当者ID=受付_O.担当者ID and 受付_I.ステータス='2' and
受付_I.受付日時 >=convert(datetime,substring(convert(varchar,受付_O.受付日時,111),1,7)+'/01') and
受付_I.受付日時 < convert(datetime,substring(convert(varchar,dateadd(m,1,受付_O.受付日時),111),1,7)+'/01') and
受付_I.ステータス= 受付_O.ステータス ) as ステータス_2合計,
(select count(受付_I.受付日時) from 受付 AS 受付_I
where 受付_I.受付日時 >= convert(datetime,substring(convert(varchar,dateadd(m,-5,getdate(),111),1,7)+'/01') and
受付_I.担当者ID=受付_O.担当者ID and 受付_I.ステータス='3' and
受付_I.受付日時 >= convert(datetime,substring(convert(varchar,受付_O.受付日時,111),1,7)+'/01') and
受付_I.受付日時 < convert(datetime,substring(convert(varchar,dateadd(m,1,受付_O.受付日時),111),1,7)+'/01') and
受付_I.ステータス= 受付_O.ステータス ) as ステータス_3合計,
(select count(受付_I.受付日時) from 受付 AS 受付_I
where 受付_I.受付日時 >= convert(datetime,substring(convert(varchar,dateadd(m,-5,getdate(),111),1,7)+'/01') and
受付_I.担当者ID=受付_O.担当者ID and 受付_I.ステータス='4' and
受付_I.受付日時 >= convert(datetime,substring(convert(varchar,受付_O.受付日時,111),1,7)+'/01') and
受付_I.受付日時 < convert(datetime,substring(convert(varchar,dateadd(m,1,受付_O.受付日時),111),1,7)+'/01') and
受付_I.ステータス= 受付_O.ステータス ) as ステータス_4合計
from 受付 as 受付_O left outer join 担当者マスタ
on 受付_O.担当者ID = 担当者マスタ.担当者ID and
受付_O.受付日時 >= convert(datetime,substring(convert(varchar,dateadd(m,-5,getdate(),111),1,7)+'/01')


集計するつもりのない、group byとか、distinctとか、union allと動きの異なるunionって、最終手段であって、他の方法が無いかを考えなくちゃ、だね。上の場合は、その月に同じ担当者で、ステータス1のデータが1000件あったら、1000回、同じカウントを行って、出力された同一行をdistinct処理してるってことですよね。なので、残り999回のカウントは不要ってことかい、ってなるはずなんだけど。。。

SQL Server Developpers Edition

アマゾンでみると、2005、2008とも5千円～6千円で買えるのか。。。
Evaluation Editionをダウンロードするのが億劫なので、勉強用に買ってみてもいいのかな？

ExcelのTOCリストの記入

仕事で、
/aaa/bbb/ccc/ddd.txt
/aab/ccd/eef/ggh/iii.txt
：
というデータをExcelに
/aaa /bbb/ccc ddd.txt
/aab /ccd/eefggh iii.txt

のように張り付ける申請書があった時に、１個ずつ手で打ち込んでいる人がいたので、テキストエディタの正規表現の置換を使って
^(/[^/]*)(/.*)/(.*)$
を
$1\t$2\t$3
に変換した後にExcelの申請書に張り付ければいいんじゃないの？と提案してみました。
わかってくれるかなぁ？

その後追記、上の人は７月末までの契約でした。何やってんだか。。。


ちなみにこの記事では、TOCとはTable of Contentsの意味でトックリストと読んだときには一覧表という意味で使っています。

SQL Serverのトランザクション分離レベル

ADO.NETで自動トランザクションを使って開発するとデフォルトで、トランザクション分離レベルが'Serializable'となります。
なので、
以下の左のような、タスクごとに一番最後に報告した勤務時間のユーザIDを取得するというSQLを実行した場合、トランザクションを閉じるまで、右のような過去の実績報告を追加するということができません（追加だけでなく、勤務時間表のすべての更新作業(追加、更新、削除)ができないはずです）。



これは、Serializableの動きのためです。
この時点のロック状況は、こんな感じ。


Serializableの特徴であるファントム回避のためキーをレンジでロックしています。
報告日時で適切なインデックスがないためオールレンジロックと同様な結果となります。
なので、insert文のロックは、Waitになっています。


このSQLでは場合は、レンジロックが有効に働くインデックスは掛けるのは不可能だと思います。
なので、Select文を速く終わらせるように工夫するか、nolockを付ける(=ダーティリードを許す)かトランザクション分離レベルをSerializableでなくする(=ファントムを許す)ことをしなければなりません。



これが、Serializableの動きとしては正しいのですが、Oracle開発から来た人にはなかなか分かってもらえない。


ちなみに、分離レベルがRepeatable readであれば右のトランザクション中に左の更新ができました。





オラクル開発から来た人曰く、トランザクション分離レベルをリードコミットスナップショットを使うとよいというが、この変更は、テーブル単位ではなくDB単位に指定するものだし、ADO.NETのデフォルトはSerializableなので、プログラムをいじらなければ何も変わらない気がするし、ADO.NETでREADCOMMITTEDを使っているとDBを変えた瞬間から動きが変わる（ロックしなくなる）のでそれでよいかを検証しなければならないと思う。あんまり都合よくいかないような気がするんだけどなぁ。

パラメタライズドクエリの善し悪し

select文をパラメタライズドクエリで引数を指定して実行するプログラムがあったのだけれど、どうも遅い。プログラム内から動かした場合と、SQLトレースを掛けて抽出したSQL文を手で流した場合で、応答時間が全く違う。

で、いろいろ調べてみたところ、手で流した場合と、プログラムから動かした場合で、SQLの実行計画が違っていた。プログラムから動かすものは、SQLを実行したときに実行計画を作り直さず、前回の実行計画を使うみたい。確かに通常の場合は、実行計画を作る分を端折ることができてヨカッタとなるところだけど、、、
今回パラメタライズドクエリに渡す引数は、開始日、終了日の２つで範囲指定しているのだけれど、範囲が１日と１０年では実行計画を変えてほしいのだけれど、変わっていないような動きをする。

結局SQLの末尾にOPTION(RECOMPILE)を付けて毎回SQLをコンパイルさせるようにした。
それと、遅くなった場合に、他の更新処理をブロックしないように、SELECT文で指定されているテーブルに対して、WITH(NOLOCK)ヒントを付けました。

テクシンのメモ

今年の報告論文は、、、内容をVSSと自動ビルドとリリース連動と宣言したので、書くときのいろいろメモ。


本書ではバージョン管理システムを示しプロジェクトでの有用性を示す。

Visual Studio .net親和性

どのようなプロジェクトでも発生するリリースの管理。

他のものとの連携を除き構成管理のみ取り上げる。
プロジェクトのみでなく個人的な運用ツールとしても活用可能。

１．はじめに

２．環境および対象範囲

３．構成管理の概要

４．リリース管理の概要

５．多重管理の回避と改修の競合対応
　・リリース時期、頻度の違い
　・要望対応と不具合対応の競合
　・対象モジュール分け
　
６．リリースミス防止策
　・リリースミス
　　　→リリースファイルの更新漏れ
　　　→ソースファイルのバージョン違い（古すぎる/新しすぎるソース）
　・防止策
　　　→ダブルチェック：リリース依頼書(Excel)とソースファイル(VSS)とのマッチングチェック
　　　　VSSの履歴・ソースファイルのバージョン抽出・excelとの申請チェック

７．今後の課題
　改修が競合した時の改修内容の横展。
　同一レーンに入れる粒度。
　レーンを増設、撤去するタイミングの取り決め
　
８．おわりに




あーーーーーどうしても書きたくないーーーーー、構成のイメージが全く思い浮かばないーーーー。めんどいーーーーーーー。

データベースのNULLの扱い

データベースには、決まっていない値という意味でNULLというものがあります。
で、このNULLについては、コッドさん曰く２種類の意味があるとのこと。

例えば、

A.自分の父親の生年月日をNULLとしたとき、
１．自分は孤児で父親がいない場合。
２．父親はいるが、生年月日を思い出せない場合。

B.自分の派遣元会社をNULLとしたとき、
１．そもそも自分は正社員で派遣元会社はナンセンスな場合。
２．派遣元を教える筋合いはないと考えている場合。


でも、現在のRDMSでは２のNULLは扱えるものはありません。すべて１の考え方でしか有りません。
ま、かろうじて安全側に倒れているような気がします。

というのは、例えばA－２．の場合、確かに父親の生年月日があるのでこれを扱おうとした場合、大変なことが起こりそう。こういう状態で、父親の誕生月を月ごとに人数を集計しようとした場合、１件でも「父親の誕生日がわからない」というデータがあると、すべての月の人数集計はUNKNOWNになるべきなのだけれど、そんなことになったら、なにもできなくなりそうだから。

というわけで、２を入れるという考えは全力で避けるようにしなければいけない。さらに１のものも避けることができれば、さらにシンプルに考えられるのだけれど。。。