SQL ServerのSQLのミスリード

なんか無性に腹が立ったので記事化。
問題は、以下のページ、
http://japan.internet.com/developer/20070206/26.html
のヒント9のもの、SQLで、COALESCEで条件分岐させるもの。
この解説で、
「この方法は、クエリの対象行が数百万に及ぶ場合でも、きわめて高速に動作します。」
なんていっているけど、絶対そんなわけがない。
このSQLは数百万行ある場合には絶対に書いては駄目なSQL。この筆者は一体何を考えているのだろう。
この前のプロジェクトでもCOALECSEを積極的に使おうというものがあって、結局性能が出ずに、結合テスト終盤でSQLを直したという苦い経験があるので、こんな記事を見ると本当にすごく嫌な気分になる。

詳しくは、「インサイドSQLServer 2005 T-SQL編」の8.4.動的SQLの使用方法のP.419に書いてあるんだけど、まとめると以下の２点。

・COALESCEを使うと、引数がNULLのときも値を指定したときも、同じ実行プランとなり、引数がNULLの場合、非常に効率が悪い実行プランで実行してしまう。
・実行プランを組み立てた後、変数に値を代入するので、OPTION RECOMPILEをつけて、毎回実行プランを作り直したとしても、まったく同じ効率の悪いSQLを作ってしまう。

要は、SQL ServerではCOALESCEはアドホックな使い方か、小さなテーブルしか使っては駄目で、プログラム内に書くのは良くないっていうこと。


上のページのSQLであれば、SELECT文のところ面倒でもCOALESCEを使わずに以下の組み立てSQL文にするべきです。

DECLARE @SQL AS NVARCHAR(4000);
SET @SQL =
N'SELECT * FROM CUSTOMERS WHERE 1=1'
+ CASE WHEN @PrmFirstName IS NOT NULL THEN N' AND FirstName = @PrmFirstName' ELSE '' END
+ CASE WHEN @PrmLastName IS NOT NULL THEN N' AND LastName = @PrmLastName' ELSE '' END
+ CASE WHEN @PrmAddress IS NOT NULL THEN N' AND Address = @PrmAddress' ELSE '' END
+ CASE WHEN @PrmCity IS NOT NULL THEN N' AND City = @PrmCity' ELSE '' END
+ CASE WHEN @PrmState IS NOT NULL THEN N' AND State = @PrmState' ELSE '' END
+ CASE WHEN @PrmZip IS NOT NULL THEN N' AND Zip = @PrmZip' ELSE '' END;

EXEC sp_executesql @SQL,
N'@PrmCity AS varchar(50), @PrmState AS varchar(50), @PrmZip AS varchar(50),@PrmFirstName AS varchar(50), @PrmLastName AS varchar(50), @PrmAddress varchar(50)',
@PrmCity = @City, @PrmState = @State, @PrmZip = @Zip, @PrmFirstName = @FirstName, @PrmLastName = @LastName, @PrmAddress = @Address;


前のプロジェクトは、以下のように、さらに悪い部分一致をしていました。こちらも、リリース前に直しが発生しました。

where Address like '%'+@keyword+'%'
で、@keywordの指定が無い場合、@keywordに空文字('')を指定すること


開発効率最優先じゃなくて、効率も気を留めておかないと非機能検証で、すべてがご破算になっちゃうよぉ。

このブログのアクセス解析結果を見てみると

http://remindbook.blogspot.com/2009/05/aspnet.htmlが、このブログで一番多く見られている。
ページビューでは、他の記事をトリプルスコアで圧倒しています。
で、この記事。
平日は平均10ページビュー/日なのに対して、休日は、1ページビュー/日。仕事でしか見てないんだねぇ。
続いて検索ワード。上位３つは、「別ウインドウで開く asp.net」、「asp.net showmodaldialog」、「asp.net ポップアップ モーダル」でした。

asp.netで別ウィンドウを開く要件で皆さん苦労されているようですね。

でも、モーダルダイアログで設計したものの、お客様要求でモードレスダイアログに戻すときに、セッション管理とか、むちゃくちゃ苦労しますので、ブラウザを使うwebアプリケーションの構造上、別ウィンドウ化（ウィンドウポップアップ化）は禁止です、、、、と宣言したいものです。

でも、当然そんなことは言えなくて、お客さんの中には、Webアプリケーションの構造を無視して、処理の途中に警告ダイアログを出して、ユーザ確認を促してって言うことも。。。

Webアプリケーションなら、
1.ボタンを押す→2.ブラウザ側javascriptを動かす→3.サーバ側で処理する→4.ブラウザに返事を返す。
っていう流れなのに、3.の処理の途中でダイアログっていう無茶を言う。
設計者も断ればいいのに、実現できますって答えちゃって。。。
結局、
http://remindbook.blogspot.com/2009/05/aspnet_29.html
のようなことをしなきゃならない。この辺をフレームワーク側で吸収してくれるであろう、VisualStudio2010,.netFramework4.0/asp.net4.0に期待かな。

ソースのヘッダコメントがコピペ

ソースファイルに、ヘッダコメントを残すルールになっていてリリースファイルの際バージョンがチェックされるんだけど、今回これが不正だった。
で、見てみると、
/*
Ver.01.00 20090701 担当者A 新規作成
Ver.01.01 20090805 担当者B ○○対応
Ver.01.02 20090813 私の名前 ○×対応
Ver.01.03 20090908 私の名前 ○×対応
Ver.01.03 20090913 私の名前 ○×対応
*/
で、今回01.03でバージョンがあがっていない変更ソースファイルがあるというエラー。
そもそも、私は、01.02しか触っていないのに・・・。他の担当者がヘッダをコピペして内容を変えいないままとなってる。
名前も修正内容もコピペ。さらに今回のはバージョンも変更していないためエラーになっちゃった。
修正担当者がいなくなったので01.03と(01.04)の修正は何だったのかははわからず、リリースまで時間がなかったためソースコメントはそのままでリリース。

間違ったコメントよりもコメントが少ないほうがまだマシなので、バージョンと日付だけ残してあとは消してくれればよかったのに。。。

リンクサーバのトランザクション設定有効化ってどうやるんだったっけ？

SQL Server 2005でリモートサーバへ、DBリンクを張って、
select * from [リモートサーバ名].[データベース名].[スキーマ名].[テーブル名]
で、アクセスすることはできた。また、insert文

insert [テーブル名]
select * from [リモートサーバ名].[データベース名].[スキーマ名].[テーブル名]
で、リモートサーバにある同名のテーブル名をローカルにコピーすることもできた。

ただ、

begin tran
insert [テーブル名]
select * from [リモートサーバ名].[データベース名].[スキーマ名].[テーブル名]

とやると、エラーとなった。（エラーメッセージは覚えていないが、nliが、とかトランザクションがなんとかとかいうメッセージだった。）
たぶん、リモートサーバのMSDTCの設定の問題だと思うのだけれど、なんだろう？
まさかリンクサーバアクセスではトランザクション処理ができないってことはないとと思うんだけど。

バッチの作り

月末までに引き継がなきゃいけないものの１つに、C#で作られたバッチがあります。（バッチの部分については、実際に携わったのは1日ぐらいですが、他に知っている人がいないもので。。）
で、このバッチプログラムはメイン処理では、テンプレートメソッドっぽい作り、機能群では、ストラテジっぽい作りでした。テンプレートメソッドはis-a,継承とオーバーライドによる処理の変更、ストラテジはhas-a,インターフェースとコンポジションによる処理の変更の意味で話していますが、デザインパターンは使ったことがないのでデザインパターンの理解が間違っているかも。。。
で、後任がオブジェクト指向の言語に慣れていないとちょっと戸惑うかも。
非オブジェクト指向の言語では、静的解析でも、充分に追跡可能だけども、オブジェクト指向言語だと、静的解析では追跡不可能になってしまう。
具体的に言うと、非オブジェクト指向言語では、問題のプログラムの最初から、呼び出し先に移動→呼び出し元に戻るという操作を繰り返していけば、最後まで到達可能だけれど、オブジェクト指向言語で作られている場合、呼び出し先に移動したところでベースクラスのメソッドやインターフェースのメソッド宣言に移動してしまい、ベースクラスの処理の中で(サブクラスでオーバーライドする処理でも)、ベースクラスのメソッドに移動してしまう。静的解析の場合は、これが限界のような気がします。
トラブルが起きたときに、普通にピンポイントで見たり、流れを順に追っていくとつらいことになりそう。
スーパークラスの役割を理解しておかないと何をやっているかさっぱり分からなくなるからね。

だから、デザインパターンは私にとって使うメリットがさっぱり理解できないです。私はアンチオブジェクト指向派です。
さらにこのスーパークラスで、サブクラスから呼び出すメソッドがあったりして、間違ったデザインパターンの作りになっています。
（テンプレートメソッドでは、スーパークラスに処理の流れが書いてあって、処理の流れの一部、他と違う処理をする機能のメソッドだけをサブクラスでオーバーライドする形で作りこむんだけど、今回のはスーパークラスに何故かユーティリティクラスみたいな機能を追加しています。ユーティリティの機能については、継承関係は全くないので、保守時の混乱を取り除くために別途ユーティリティクラスを起こすべきなのに。。。）

・・・わたくし、コピペ万歳の人なので。。。でも、コピペの作法を間違っているプログラムの作りをしてあって、さらにコピペ処理を失敗しているのを見るともっといらいらしてしまうのだけれど。。。


コピペの作法について、
コピペって、極力変更しないで使うもので、コピー元のプログラムの作りに工夫しておかなきゃいけないのに、その辺の工夫を全くしていないと酷いことになってしまう。（さらに変更しなきゃいけなくなった部分で変更していなかったりしてね。）

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

今日上司に聞いたらそういうことらしい。
てっきり１０月以降もいるものだと思っていろいろなものを引き継いだのに、すぐに引き継ぎをしなきゃいけないみたい。
でも、プロパーでそれなりに.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の受け渡しパラメータを変更して、対応完了。

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