１０月から別プロジェクトへ異動

今日上司に聞いたらそういうことらしい。
てっきり１０月以降もいるものだと思っていろいろなものを引き継いだのに、すぐに引き継ぎをしなきゃいけないみたい。
でも、プロパーでそれなりに.netを知っている人がいなくなってしまうんだけどいいのかなぁ。。。

なんてこったい

昨日リリースしたバッチプログラムがおかしくなった。
ログからは、int64型の値をint32型の変数に格納できないとのメッセージが。
で、前回のソースファイルを見比べたら、～Designer.csの型が1箇所おかしい。前回リリースしたバージョンの～Designer.csでは、BigInt,Int64型のパラメータ引渡しになってるのに、今回のものはInt,Int32型になってた。
で、元となる～.xsdファイルでは、パラメタライズドクエリのパラメータの1つが、、、、Int32型になってた。
考えられるのは、～Designer.csファイルを手で修正したこと。

～Desinger.csは、VisualStudio2005が自動生成するファイルで、手で触っちゃいけないファイルなのです。また、～.xsdを更新すると～Designer.csが再生成されるのです。
で、今回～.xsdでSQL文をちょっと直したら、別のSQL文の方でで問題が発覚。しかもビルド時エラーじゃなく実行時エラーなので困ったものです。

あ～あ、前任者のトラップに引っかかっちゃった。

で、今回は、～.xsdの受け渡しパラメータを変更して、対応完了。

でも、なんだったんでしょうねぇ？

久しぶりの更新。今回も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)ヒントを付けました。