select文が遅くなったら

selectの問い合わせがすごく時間がかかる、タイムアウトしたみたいだ、台帳テーブルの一部を削除して、追加したら、こうなった、、、と云う問題が発生して１日かけて調べてみた。



まず、遅くなるselect文はこんな感じ。

select
    count([台帳].[台帳_ID])
from
    [台帳]
left outer join
 [t00]
on
 [台帳].[台帳_被保険者証番号] = [t00].[個人_被保険者証等番号]
and
 [台帳].[台帳_性別] = [t00].[個人_性別]
and
 [台帳].[台帳_生年月日] = [t00].[個人_生年月日]
and
 [t00].年度 = [台帳_年度]
where
    [台帳_年度] = 2015

まったく意味がないSQL文だが、サンプルということで。

「t00」テーブルは1万7千行、SQLで参照されている列名にはインデックスは張ってない。（テーブルスキャンすることになるね）

「台帳」テーブルは、8万5千行から2万1千行削除して、9千行追加する。ここもインデックスは無い。

追加と削除を時間を追ってみていく。

1. 最初の8万5千行のとき：0秒で結果が返る
2. そこから2万1千行削除する
3. select文を実行すると0秒で結果が返る
4. そこに9千行を追加する
5. select文を実行すると約3分50秒かかる。

ロックでもかかっているのだろうかと、５のときのロックの状況を調べてみると（sys.dm_tran_locksテーブルを見た）、たしかにロックが発生している。

しかし、さっぱりわからない。「BULK_OPERATION」ってなに？MSDNで探すと、下記のページが見つかったが、やはりわからない。

sys.dm_tran_locks (Transact-SQL)

HOBT.BULK_OPERATION 

スナップショット、コミットされていない読み取り、および行のバージョン管理使用によるコミット読み取りのいずれかの分離レベルで実行される、同時スキャンによるヒープ最適化一括読み込み操作。

＊＊＊

（追記）SQL Sever2005 のヘルプのページの方がわかりやすい。というか、上のは機械翻訳だね。

SNAPSHOT、READ COMMITTED、および行バージョンを使用する READ COMMITTED の分離レベルでの、ヒープが最適化された一括読み込み操作と同時実行スキャン。

 うーん、テーブルスキャンに時間がかかっているということかな。

＊＊＊

resource_associated_entity_id列に数字がある。resource_type が「HOBT」のときはsys.partitions.hobt_id の値だとMSDNにあるので、下記のSQLでオブジェクト名を調べてみると、確かに「台帳」テーブルだった。

select OBJECT_NAME(object_id), * from sys.partitions
where hobt_id = 72057594246922240

面倒なのは

上の状況が面倒なのは、３のselect文を実行しないと（つまり削除と追加を続けて実行したら）、５のselect文は遅くならないというところ。なかなか、不思議。

1. 最初の8万5千行のとき：0秒で結果が返る
2. そこから2万1千行削除する
3. （なにもしない）
4. そこに9千行を追加する
5. select文を実行すると0秒

それなら、削除と追加を同時にすればよいじゃないと思われるが、削除と追加は別々の画面で行なっているので、そうもいかない。

さらに、select文も「台帳」テーブルだけを参照していると遅くならない。遅くなるのは他のテーブルをleft joinしたときなのだが、他のテーブルはその間何も変わっていないので、そちらのテーブルに原因があるとは思えない。

たとえば下記のselect文ならほぼ0秒で結果が返ってくる。

select
    count([台帳].[台帳_ID]),
    NULL [dummy]
from
    [台帳]
where
    [台帳_年度] = 2015

「台帳」テーブルに問題があるなら、このselect文でも遅くなりそうなものだけど、そう単純な話ではないらしい。

解決策

「ロックのエスカレーション」とか「SQL ブロッキング」とか検索するといろいろ出てくるが、さっぱりわからない。

とりあえず、インデックスを張ってみた。

CREATE NONCLUSTERED INDEX [IX_台帳] ON [台帳]
(
 [台帳_年度] ASC,
 [台帳_被保険者証番号] ASC,
 [台帳_性別] ASC,
 [台帳_生年月日] ASC
)

これで試してみたが、残念ながら速度は変わらず。

追加した後に、インデックスを張りなしてみたらどうだろう。

1. 最初の8万5千行のとき：0秒で結果が返る
2. そこから2万1千行削除する
3. select文を実行すると0秒で結果が返る
4. そこに9千行を追加する
5. select文を実行すると約4分40秒かかる。
6. インデックスを張りなおすと：0秒で結果が返る

うん、速くなった。

ということで、「大量のデータを追加、削除した後はインデックスを張りなおす」という昔からあるノウハウに落ち着いた。

＊

邪道な方法としては、テーブルの統計情報を更新する手もある。

UPDATE STATISTICS [台帳]

これを実行すると確かに速くなる。

ひょっとすると1時間くらい待っていたら、データベースが自動的に統計情報を更新してくれて、勝手に速くなっている、、、かもしれない。

ということで、2時間ほど放置してみたが、速くはならなかった。残念、、、

＊