[SQL]SQL Server パフォーマンスの向上

最近SQLServerのパフォーマンスについて調査しました。
パフォーマンスの悪い処理があって、改善をするためにパフォーマンス計測をしました、計測のの結果、ほとんどの処理がSQLということがわかりました。
調べてみると、検索対象のテーブルはデータ件数が200万件あることがわかりました。そして、インデックスを使わない検索処理があり、これが処理時間の大半を占めていました。
コレが原因となり、とある処理が3時間くらいかかっていました。

で、特定の項目にインデックスを設定すると、パフォーマンスが10強にまでアップしました。
そのときに参考にしたのが、SQL Server パフォーマンスの向上です。

結構読み応えがあります。

また「チェックリスト: SQL Server パフォーマンス」というのもあります。時々コレを見て見直しをするのもいいかもしれませんね。

[SQL]SQLServer データの圧縮

いま、SQLServer2005の仕事をしています。やっているうちにディスク容量が足りなくなりましたで、見てみると、トランザクションログファイルのサイズがでかくなっていて、何じゃこりゃ？状態になっていました。で、調べてみると圧縮するコマンドがあったので、備忘録としてメモ。

DBCC SHRINKFILE ('DB名', 0, TRUNCATEONLY);
DBCC SHRINKDATABASE ('DB名')

ちなみにSHRINKDATABASEはログではなく、本体側を圧縮するそうです。

[SQL]SQLServerのDB内に存際する全テーブルの件数取得(T-SQL)

先日、隣の人が全テーブルの件数を取得するために、全テーブル分SQLを書いていました。
「めんどくさそう」と他人事ながら思っていました。
で、その後sysobjectsというテーブルからテーブルの一覧を取得できることを知り、それを見て「できそう」と思ったのでやってみました(隣の人には内緒)。

動的SQLを順次実行することにより、結果を取得できるのですが、結果の出方がかっこ悪いので、テーブル変数(一時テーブル)に格納して結果を表示することにしました。コードはこんな感じ。

SET NOCOUNT ON
--テーブル変数(結果格納用)
DECLARE @TEMP_TABLE table(
     T_NAME varchar(128)
    ,T_CNT int
)

DECLARE @NAME nvarchar(128) --テーブル名一時領域
DECLARE @SQL nvarchar(128) --実行するSQL
DECLARE @CNT int --カウント結果

--カーソルの宣言
DECLARE cs CURSOR FOR
    SELECT NAME FROM sysobjects where type='U' ORDER BY NAME

--カーソルのオープン
OPEN cs
FETCH NEXT FROM cs
    INTO @NAME
WHILE @@FETCH_STATUS = 0
BEGIN
    --動的にSQLを組み立てる
    SET @SQL = N'SELECT @CNT=COUNT(*) FROM ' + @NAME
    --SQLを実行する
    EXECUTE sp_executesql @SQL, N'@CNT int OUTPUT', @CNT OUTPUT
    --実行した結果を一時テーブルに登録する
    INSERT INTO @TEMP_TABLE VALUES(@NAME,@CNT)
    FETCH NEXT FROM cs
        INTO @NAME
END

CLOSE cs
DEALLOCATE cs

--実行結果を表示する
SELECT * FROM @TEMP_TABLE
SET NOCOUNT OFF

[他]SQL Server Compact Edition

mdb(Access)はADO.NETでアクセスができます。そして手軽です。が、標準と思われるSQLをサポートしていないという決定的な弱点があります。
その代替なのかどうかはわかりませんが、今はSQL Server Compact Editionがあります。
このSQL Server Compact Editionは、MDB同様にサービスを常駐させる必要がありません。

ストアド プロシージャとトリガは使えないらしいのですが、MDBと比べればかなりいいと思います。
その他互換性に関しては「SQL Server Compact と SQL Server の相違点」が参考になると思います。互換性の無い部分結構ありますが、主だった機能はサポートされているのでいい気もします。
すべてじゃなくてもいいいので、T-SQLがサポートされるとテストデータ作成など楽になるんですけどねぇ。

MDBの代わりにというのもありますが、たとえば、DBサーバにつながらない場合に、クライアントアプリでデータを一時保存するためのストレージにするなど、用途は考えるだけでいろいろ思いつきますね。

ちなみにコレってJDBC接続はできるんだろうか？などいろいろ興味があります。
今度、詳細な調査をしてみたいと思います。

[他]ReDim神話？

最近、VB6.0の修正案件があり、コードを見ているとReDimをしている箇所がいくつかありました。それを見て、遠い昔に「VBのReDimは非常に遅いのでReDimが1回ですむように、先に必要な要素を確保するのが定石。ループ内でReDimするなどもってのほか。」と聞いたことを思い出しました。
パフォーマンス改善も案件の1つであったため、ついでに直しておきました。で、計測してみたんですが、変わったのは誤差レベルでした。
はて？と思い、VBSで以下のようなコードを試しに実行してみました。

Dim data()
Dim idx

Dim st
st = Timer

For idx=0 to 100000
    ReDim Preserve data(idx)
    data(idx) = "Data" & idx
Next

MsgBox "処理時間=" & (Timer - st) * 1000 & " ms"

で、ループ回数を変えて実行してみました(C2D 2GHz環境)

1,000 → 処理時間= 0.0 ms
10,000 → 処理時間= 14.9 ms
100,000 → 処理時間= 530.0 ms
1,000,000→ 処理時間=46,939.9 ms


指数的にパフォーマンスは悪化しますが、数万のレベルであれば数十ミリ秒のレベル。
いまどきのハードであれば、処理のわかりやすさ重視でいいのかもしれませんね。

[.NET]無償のチャートコントロール(ASP.NET)

先日、@itを見ていると無償で使用できるASP.NETのチャート・コントロールを見つけました。

Excelで見たことがあるようなグラフが表示ができるみたいです。
今度グラフっぽいものを表示する必要がある時に使ってみようと思います。

[.NET]配列のコピー

ある配列からある配列へデータをコピーする場合、同じ型であれば単に配列をループしながらコピーすればよいですが、型の異なる配列に値をコピーするのはちょっとメンドクサイ。のですが、簡単にコピーするインタフェースがあったので、使ってみました。コードはこんな感じ。

char[] aaa = { '1', '2', '3', 'あ' };
byte[] bbb = new byte[aaa.Length * 2];

//ある配列からある配列へコピー
Buffer.BlockCopy(aaa, 0, bbb, 0, bbb.Length);

String ccc = BitConverter.ToString(bbb);
// cccは「31-00-32-00-33-00-42-30」となる

尚、charの配列のばあい、コンピュータのエンディアンに依存するので、他の機種(OS)と連携する場合などに利用する場合は、注意が必要です。

[.NET]HTTPハンドラ(.ashx)

以前、ASP.NETを使って特定のXMLを出力するという仕事をしたことがあります。
WebでXMLといえば、Webサービス(.asmx)ということで調べてみましたが、先方の決めたXMLスキーマに従ったXMLを出力する必要がありました。いろいろ調べてみたものの、Webサービスではその仕様に準拠するXMLを出力できなさそうというこがわかりました。で、あきらめてWebフォーム(.aspx)を使って実装しました。

仕様的にはDBから値を取り出し、その値を適当なクラスに設定し、XmlSerializerを使ってXMLを出力するというものでした。

最近、ASP.NETには「HTTPハンドラ(.ashx)」というのがあるのを発見しました。HTTPハンドラは、画面などを意識しない構成になっており、好きな情報をレスポンスできます(たとえばイメージ出力など)。aspxはどちらかというとコンテンツという側面が強く、いまさらながらashxのほうがあっていると思いました。

今度、機会があったら使ってみようと思います。