- PR -

ADO.net経由でPRIMARY KEYのインデックスを参照しない？

1
投稿者投稿内容
赤いんげん
会議室デビュー日: 2006/08/29
投稿数: 10
お住まい・勤務地: 東京都
投稿日時: 2006-10-14 17:26
お世話になっております。ADO.NETでSQLserver2000を検索する処理を行っていますが、
件数が増加するにつれ、Select文の処理時間が肥大化する事象が起こっています。

1回目に120件を検索すると0.06秒で完了するのに、2回目で220件を検索すると0.25秒、
18回目に680件の検索を行うと17.11秒掛かってしまいます。
（計測したのは、　oTmp = cmd.ExecuteScalar()　の前後です。）

件数が増えるにつれ、処理時間が肥大化していることから、検索時にPrimaryKeyの
インデックスを参照していないように思うのですが、恥ずかしながら
どこで誤っているのかわかりません。。。

どなたかよろしくお願いいたします。


'テーブル作成
CREATE TABLE HORUHORU (Makedate VARCHAR(08), Year VARCHAR(04), Monthday VARCHAR(04),
Item1 VARCHAR(02), Item2 VARCHAR(02), Item3 VARCHAR(02), Item4 VARCHAR(02),
Item5 VARCHAR(06), Item6 VARCHAR(11), Item7 VARCHAR(03),
PRIMARY KEY (Year,MonthDay,Item1,Item2,Item3,Item4,Item5))

'SQL文
sql = "Select makeDate from HORUHORU where year = @002 AND monthDay = @003 AND
Item1 = @004 AND Item2 = @005 AND Item3 = @006 AND Item4 = @007 AND Item5 = @008"

'Infoの数だけSelectを発行
For i As Integer = 0 To info.Length -1

　cmd = con.CreateCommand()
　cmd.CommandText = sql
　
　cmd.Parameters.Add(SqlParameter(@002 , info(i).Year ))
　cmd.Parameters.Add(SqlParameter(@003 , info(i).Monthday ))
　cmd.Parameters.Add(SqlParameter(@004 , info(i).Item1 ))
　cmd.Parameters.Add(SqlParameter(@005 , info(i).Item2 ))
　cmd.Parameters.Add(SqlParameter(@006 , info(i).Item3 ))
　cmd.Parameters.Add(SqlParameter(@007 , info(i).Item4 ))
　cmd.Parameters.Add(SqlParameter(@008 , info(i).Item5 ))

　'Select文を発行
　oTmp = cmd.ExecuteScalar()

　'makeDateが古ければUpdate、DbnullならばInsert
　UpdateOrInsert()

Next i

'各処理時間
1回目：120件の処理　0.06秒
2回目：220件の処理　0.25秒
3回目：364件の処理　1.02秒
4回目：286件の処理　1.17秒
5回目：286件の処理　1.40秒

9回目：364件の処理　3.46秒
12回目：455件の処理　7.74秒
18回目：680件の処理 17.11秒

[ メッセージ編集済み 編集者: 赤いんげん 編集日時 2006-10-14 17:28 ]

[ メッセージ編集済み 編集者: 赤いんげん 編集日時 2006-10-14 17:30 ]
ちゃっぴ
ぬし
会議室デビュー日: 2004/12/10
投稿数: 873
投稿日時: 2006-10-14 20:31
とりあえず、SQL server 側の問題でしょうから、query analyzer で execution plan をとってみましょう。件数を変化させて。

[ メッセージ編集済み 編集者: ちゃっぴ 編集日時 2006-10-14 20:32 ]
囚人
ぬし
会議室デビュー日: 2005/08/13
投稿数: 1019
投稿日時: 2006-10-14 21:25
本当に、Primary Key を定義するのにそんなに多数の列が必要なんですか？
クラスタ化インデックスキーになる列がかなり多くなりませんか？

SQL Server にそれ程詳しくないのでよくわかりませんが、かなり適当にプライマリーキーを定義しているように見受けられるのですが。
_________________
囚人のジレンマな日々
七味唐辛子
ぬし
会議室デビュー日: 2001/12/25
投稿数: 660
投稿日時: 2006-10-14 22:36
データの増え方と処理時間のかかり方がおかしいような
レコードが１００件と２００件なんてデータベース的にはほとんど差が無いように
思えるのだが、各処理時間の計測あってます？
がんふぃーるど
ベテラン
会議室デビュー日: 2006/06/05
投稿数: 58
お住まい・勤務地: さいたま
投稿日時: 2006-10-14 22:41
がんふぃーるどです。お世話様です。

ちょっと調べていなくて、ほとんど憶測に近いのですが…
SqlParameterのオブジェクトを生成する際に、コンストラクタの引数として
SqlDBTypeとsizeを指定するようにしてみてはどうでしょうか？
SqlDBTypeとsizeを指定しない場合、string型だとパラメータの定義を
varchar(4000)と.NET側が勝手に定義してパラメタライズドクエリを
生成していたと記憶しています。
この部分をしっかり定義すれば、もしかしたらうまくいくかもしれません。
ちなみに、charの場合sizeが違うだけでインデックスが確実に効かなくなります。
ただ、varcharだと効きそうなものですが…
赤いんげん
会議室デビュー日: 2006/08/29
投稿数: 10
お住まい・勤務地: 東京都
投稿日時: 2006-10-15 10:58
＞ちゃっぴ様
execution planの存在を知りませんでした…ありがとうございます。
結果は、query analyzer で手動で1件、10件、100件、1000件の
Select文を調査してみましたが、いずれの場合もクラスタ化インデックスを
参照するという結果になりました。
query analyzer経由のSQLと、ADO.NET経由のSQLで異なる内容が発行してしまっているようです。

＞囚人様
ご指摘のように、各項目を繋げて１項目の主キーを設定すべきだと思うのですが、
残念ながらデータを提供しているサービスがこういう仕様で
データを定義してしまっています・・・某国営団体

＞七味唐辛子様
私の書き方が不味かったです。申し訳ないです。
2回目以降はそれ以前の件数も含めてSELECT対象となります。
ですので、1回目のループではゼロ件のデータベースを走査対象とし、
2回目のループでは1回目の120件も走査対象とし、3回目のループでは、
1回目の120件と2回目の220件を併せた340件を走査対象とします。
これらを考慮すると、ほぼ走査対象件数に比例した時間が掛かっています。

＞がんふぃーるど様
ご指摘のとおりでした！！DBTYPEとLenghを指定してParameterを作成したところ、
毎回0.5秒以内で処理が完了するようになり、劇的に性能が改善されました。
コーディング量を減らすために上記のような作り方をしてしまったのですが、
裏ではそういうカラクリが動いてしまっていたのですね…VarCharの件は分かりせんが。
大変助かりました。本当にありがとうございます。

皆様、いろいろと勉強になりました。
ありがとうございました。
ちゃっぴ
ぬし
会議室デビュー日: 2004/12/10
投稿数: 873
投稿日時: 2006-10-15 11:22
解決したようでなによりです。

ただ、なんか方針もなく調査しているようなので･･･
今後のために

Profiler で execution plan つきの trace をとる方法をおぼえておくとよいでしょう。

あとここら辺は読んでおいたほうがいいでしょうね。

SQL Server 7.0 以降で実行に時間のかかるクエリのトラブルシューティング
[HOWTO] アドホック クエリのパフォーマンスのトラブルシューティングを行う方法
[INF] SQL Server 7.0 または SQL Server 2000 のブロッキング問題と解決策
1

スキルアップ／キャリアアップ（JOB@IT）