こんちは

久しぶりに質問させていただきます。

過去ログビューワーを自作して、使用しています。何年も立つ内にデータが数十MBに
なってしまい、検索時間が、我慢の限界を超える程長く掛かるようになってしまいました。

環境Win98　VS6.0SP5　Office2K
ログビューワーはVBで作成、DBはmdb 、DAO
現在は、１テーブルのみでフィールドは以下の通りです。
ID     　　：自動No　(Index)
質問ID　　：文字列
Title　　　　：文字列
質問　　　：Memo型
回答　　　：Memo型


【教えて欲しい事】
当たり前ですが、検索時、　WHERE 回答 like '*ListBox*' 　とした場合、
上述のように検索に非常に時間が掛かります。現在１分以上掛かっているのですが、
今より早くするにはどのような方法を取ればよいのでしょうか？
(前提は全文検索できる事としたい。）
MSDNの全文検索ってどうやっているんでしょうか？？？できればあの程度まで早くしたい

なんか漠然としてますが、宜しくご教授お願いします。

こんちは

Resが付かないようなので、引っ越します。

今後のために教えてもらいたいのですが、このような場合削除すべきでしょうか？

> こんちは
>
> Resが付かないようなので、引っ越します。
>
> 今後のために教えてもらいたいのですが、このような場合削除すべきでしょうか？

レスがついていない場合は、ご自由に削除して頂いて結構です。
（削除機能がついていますので）

今後、この記事に対してレスがあった場合、返信できないなら削除して下さい。
回答があったら、速やかに返信ができるなら、このままでも私はいいと思います。

マルチポストの判断は次に利用される掲示板のルールに従って下さい。
あとは、貴方が一般常識としてどのようにご利用されるかだと思います。

了解しました。もうしばらくこのままにさせてもらいます。

こんにちは、質問に対する質問です。

ログは全て残しておかなければいけない仕様なのでしょうか？
期限を切って削除してはいけないのですか？

最適化は行っていますか？

Resありがとうございます。

> こんにちは、質問に対する質問です。
>
> ログは全て残しておかなければいけない仕様なのでしょうか？
はい、文書、ソースなど全て残しておくようにしてます。

> 期限を切って削除してはいけないのですか？
古い、資料も参考になりますので残してます。
> 最適化は行っていますか？
あ、そういえばこれは忘れてました。ちょっと試してみます。
この後の書き込みは夜になります。

全文検索には幾つかの方式がありますが、いずれにしても、検索速度を高速化するには
適切な「インデックス」が必要となります。

例えば、キーワードの一覧テーブルを作成し、

  [Keyword]表 ―― [Key]は主キー、[Word]はユニークキー
  Key    Word
----    ----------
    1    LISTBOX
    2    TEXTBOX
    3    COMBOBOX
    ：      ：

それを、過去ログテーブルに対するインデックスのようにする方法が考えられます。

  [LogIndex]表 ―― [Key]はKeyword.Keyと結合、[ID]は過去ログの主キーと結合
  Key       ID
----    ------
    1      2374
    1      2375
    1      2376
    2      3375
    2      3377
    ：      ：

このようにしておけば、『WHERE 回答 LIKE '*ListBox*'』とするかわりに、
『WHERE Keyword = 'LISTBOX'』のような検索条件とする事ができます。
これならばインデックスを使って検索できるので、検索時間は短縮されます。


ただし、データ量は格段に増えるため、データベース自体は肥大化します。
巨大なmdbは、破損率・読み込み速度面などで不利になりますから、
どの程度の規模のインデックス表を、どのようなアルゴリズムで生成するかが、
プログラマの腕の見せ所になりますね。

キーワードの抽出には、茶筅などを使うと便利かもしれません。
http://member.nifty.ne.jp/hippo2000/ComCha/ComCha.htm

なお、単語ベースの検索を行う場合は、表記ゆれも解決しておくと良いでしょう。
http://magazine.fujitsu.com/vol48-2/4-3zu3.html


ちなみに、全文検索におけるインデックステーブルの作り方としては、
幾つかの方式が研究されています。下記なども参照してください。
http://www.jpo.go.jp/shiryou/s_sonota/hyoujun_gijutsu/search_engine/c/c32.htm


あるいはmdbではなく、既存の全文検索システムを併用するという手もあります。
ファイルベースの全文検索が可能な "Namazu for Win32" などが有名ですね。
また、Windows 2000以上ならば Index Serviceなども利用できます。
たとえば、Index Serviceを使った、以下のようなソフトもあります。
http://www.forest.impress.co.jp/article/2003/07/15/satori.html
# Index Serviceは、ローカルファイル(テキスト、HTML、Office文書等)向けの
# システムであり、VBからは ADO経由で検索する事ができます。

また、Indexing Service/Index Server以外で、全文検索可能な
Microsoft製品としては、
  Microsoft IIS Index Server
  Microsoft SharePoint Portal Server
  Microsoft SQL Server
  Microsoft Site Server
などもあります。まぁ、今回の要件ではオーバースペックかもしれませんが。


そこまでの仕組みを導入したくはない、という事であれば、
絞り込み条件範囲を増やすのも有効です。例えば、
  WHERE (ID BETWEEN 1 AND 500) AND (回答 LIKE '*ListBox*')
のように条件を絞る事で、検索対象のレコード数が減らすわけです。

さらに、間にDoEventsなどをはさむなどして、
        :
  WHERE (ID BETWEEN 501 AND 1000) AND (回答 LIKE '*ListBox*')
        :
  WHERE (ID BETWEEN 1001 AND 1500) AND (回答 LIKE '*ListBox*')
        :
  WHERE (ID BETWEEN 1501 AND 2000) AND (回答 LIKE '*ListBox*')
        :
のように、次々と検索を行うようなコードにすると、途中で検索を打ち切ったりする事が
可能になるので、見た目の使い勝手は向上します。（トータルの処理時間は延びますが）


> MSDNの全文検索ってどうやっているんでしょうか？？？できればあの程度まで早くしたい
あれは、検索用のインデックスファイルを、別途用意しているのです。
# MSDNライブラリのバージョンによっては、インデックスファイルのインストールを
# 行うかどうかを、インストール時に選択できるものもあります。

内容が逐次書き換わるようなデータベースの場合は、インデックスの追加生成を行うために
パフォーマンスが低下する可能性がありますが、MSDNライブラリのように、データが更新されない
静的なデータの場合は、インデックスファイルが有効な手段となります。

こんちは
わたしきさん>

最適化をする事により約25％程度早くなりました。
２つあります。
ファイルサイズ約60MB　99Sec　⇒　77Sec
ファイルサイズ約10MB　計測忘れ⇒22Sec
ご指摘ありがとうございました。

魔界の仮面弁士さん>
長文でのご指導ありがとうございます。
理解、参照に時間が掛かりそうですが、研究します。
この件で判らない時は改めて質問させていただきますので
宜しくお願いします。

みなさんありがとうございました。

V友の方へ引越ししていたんですが、削除してきました。
報告まで。

> V友の方へ引越ししていたんですが、削除してきました。
> 報告まで。

V友(DB掲示板)の#156…ですよね。
今回は既に、#157にて回答が付いていましたよね。

差し出がましいようですが、『回答がついた後に削除する』のは、折角回答していただいた方
(そして、無投稿ながらも、回答を考えてくれた方々)に対して、礼を欠く行為になる事がありますので、
削除するよりは、「○○という掲示板で、△△という回答を戴き、□□という結果になりました」
などといった、何らかの結果報告(あるいは中間報告)の形にされた方が良かったかと。

ご指摘ありがとうございます
> V友(DB掲示板)の#156…ですよね。
> 今回は既に、#157にて回答が付いていましたよね。
と思って、昨晩V友へいったんですが、何故か、#157が無かったんです？？？
それで、削除されたかなっと思って、削除しました。

このご指摘を、見て、早速行ってきましたが、今朝はありました。？？？

早速お詫びと報告をしてきました。

おかげで大変な失礼をせずにすみました。
本当にありがとうございました。

・・・しかし、どうして昨晩は無かったんだろ？？？

本題ですが、MSDEへの変換⇒全文検索機能の利用の方向
を研究してみようかと思っています。が、大変、先は長そうです。^ ^;;

お世話になりました。m(_ _)m

こんにちは、なかむぅと申します。
もう、御覧になってないですかねぇ、、、


魔界の仮面弁士さんのおっしゃる通り、インデックスを張るのがよいと思います。
【回答】にインデックスを張って、テストしてみて下さい。


業務での経験なんですが。(mdb)
どうせ処理が遅いし、夜間の週次バッチだから、ということで。
インデックスはいらないかな、って張らなかったことがありました。
実行時間が４時間半ほどかかっていました。
お客さんから日次バッチにして欲しい、と要望があって
なんとかレスポンスを上げるため、試しにインデックスを張ってみたら。
なんと７分で終わったんですよ。

４時間半　→　７分

さすがに怒られましたけどね＾＾；


# 参考までに〜

> 魔界の仮面弁士さんのおっしゃる通り、インデックスを張るのがよいと思います。
> 【回答】にインデックスを張って、テストしてみて下さい。

ところがJETの場合、MEMO型フィールドにIndexは利用できなかったりするのです。
……正確には、張れない事もないのですが、完全な効果は得られません。
(無理に張っても、文字列の後半部分にはインデックスが働きません)

そもそも、『WHERE 回答 like '*ListBox*'』のような部分検索においては、
JETの"RushMore Technology"によるクエリー最適化が働きませんので、ほとんど意味はありません。
http://support.microsoft.com/default.aspx?scid=kb;ja;172199
http://support.microsoft.com/default.aspx?scid=kb;ja;240434

ですから高速化するなら、どうにかして、『回答 LIKE 'LISTBOX*'』か、もしくは
『回答= 'LISTBOX'』の形になるように設計する必要があります。(先のNo.5579はその一例です)
あるいは、全文検索可能な(JET以外の)DBに移行するか…ですね。


> どうせ処理が遅いし、夜間の週次バッチだから、ということで。
> インデックスはいらないかな、って張らなかったことがありました。
インデックスの無い検索は、ホントに遅いですね。。。(^_^;)
詳細は書けませんが、ある日次処理でインデックスを全て外したら、
15分→20時間(泣)という結果になった事さえありますし。

もっとも、インデックスが有効に利用されるような設計でなければ、
意味はないですけれどね。フィールドに無意味にインデックスを張ったせいで、
今度は更新速度が低下してしまった、という話も聞きますし。


# 複雑なSQLでは、SQLが正しく最適化されているかどうかを検証するために、
# レジストリの実行プランフラグを有効にして、showplan.outファイルの結果を確認したり、
# DAOのISAMStatsメソッド や ADOのJET_SCHEMA_ISAMSTATSスキーマを利用して、
# ディスクI/Oやロック頻度について調べてみると良いかもしれません。

なかむぅさん、魔界の仮面弁士さん
Resありがとうございます。

経過です。
調べていくと、SQL Server にフルテキスト検索エンジン
というのがありましたが、O/SがNT系のServerでなければインストール出来ないらしい
というのが判りました。

従って私の環境WIｎ98では問題外というのがわかり
>MSDEへの変換⇒全文検索機能の利用の方向
はボツ(悲)

やはり、「IndexTable」を作って、あいまい検索を減らす方向で又、悩んでみます。
しかし魔界の仮面弁士さんが「2003/08/25(Mon) 11:01」のResでリンクを張ってくれている
サイトは、難しいのでこれまた悩ましいです。

いずれにせよ、ホントに「IndexTable」を作ると結構でかいDBになってしまいそうなので
これを機会にMSDEには移行しようかなと思います。

ボチボチ頑張ります。(苦ぅ〜)

> ところがJETの場合、MEMO型フィールドにIndexは利用できなかったりするのです。
> ……正確には、張れない事もないのですが、完全な効果は得られません。
> (無理に張っても、文字列の後半部分にはインデックスが働きません)

大変失礼しました！

当方、ASP-mdbでシステム開発をしています。
あまりMEMO型を使ったことがなかったので、見当違いなことを発言しすみません。


> そもそも、『WHERE 回答 like '*ListBox*'』のような部分検索においては、
> JETの"RushMore Technology"によるクエリー最適化が働きませんので、ほとんど意味はありません。

勉強不足でした。
LIKEではインデックスが使用されないんですね。
他DBでも同じなんでしょうか、、、


> 15分→20時間(泣)という結果になった事さえありますし。

やはり、よくある話なんですね（＞＜


> もっとも、インデックスが有効に利用されるような設計でなければ、
> 意味はないですけれどね。フィールドに無意味にインデックスを張ったせいで、
> 今度は更新速度が低下してしまった、という話も聞きますし。

DB設計をする上で、しっかり考えなければいけない所ですよね。


> # 複雑なSQLでは、SQLが正しく最適化されているかどうかを検証するために、
> # レジストリの実行プランフラグを有効にして、showplan.outファイルの結果を確認したり、
> # DAOのISAMStatsメソッド や ADOのJET_SCHEMA_ISAMSTATSスキーマを利用して、
> # ディスクI/Oやロック頻度について調べてみると良いかもしれません。

これはJETの話なんでしょうか？
それとも、Access、SQL Server、、、

Oracle等ではSQLトレース？みたいな事を聞いたことがあるのですが、、、
博識な方はいろいろ御存知ですよね、感心しました。


neptuneさん、あまりお役に立てず申し訳ありませんが
頑張って下さいね！！

> > そもそも、『WHERE 回答 like '*ListBox*'』のような部分検索においては、
> > JETの"RushMore Technology"によるクエリー最適化が働きませんので、ほとんど意味はありません。
> LIKEではインデックスが使用されないんですね。
必ずしも、そういうわけではありません。
LIKE 'ListBox*' などであればインデックスが利用されます。


> > # 複雑なSQLでは、SQLが正しく最適化されているかどうかを検証するために、
> > # レジストリの実行プランフラグを有効にして、showplan.outファイルの結果を確認したり、
> > # DAOのISAMStatsメソッド や ADOのJET_SCHEMA_ISAMSTATSスキーマを利用して、
> > # ディスクI/Oやロック頻度について調べてみると良いかもしれません。
> これはJETの話なんでしょうか？
もちろん、JETの話ですよ。


JETクエリの「実行プラン」は、以下のようにして参照できます。

まず、レジストリの
  \\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\(バージョン)\Engines\Debug
というキーに、文字列(REG_SZ)型で JETSHOWPLAN という値を作成し、
そこに ON と書いておきます。
（なお、通常は Debug というキーは存在しないので、無ければ作成してください）

そうすると、クエリの初回実行時に、実行プランの内容が showplan.outという
テキストファイルに出力されるようになりますので、それをメモ帳等で開いて見てください。

# なお、解析処理が終わったら、レジストリ設定を解除しておくのを忘れないようにして下さい。
# そのまま放っておくと、ログがどんどん巨大化していってしまいますから。(^_^;)



それから、「ディスクI/Oやロック頻度」というのは、以下のようにして取得します。

DAOの場合は、
  Debug.Print DBEngine.ISAMStats(0), "ディスク読取数"
  Debug.Print DBEngine.ISAMStats(1), "ディスク書込数"
  Debug.Print DBEngine.ISAMStats(2), "キャッシュからの読取数"
  Debug.Print DBEngine.ISAMStats(3), "キャッシュからの読取数"
  Debug.Print DBEngine.ISAMStats(4), "配置されたロック数"
  Debug.Print DBEngine.ISAMStats(5), "解除されたロック数"
  '以下は、JET 4.0 (DAO 3.6)環境で有効
  Debug.Print DBEngine.ISAMStats(6), "レコードに記憶されたLVS数"
  Debug.Print DBEngine.ISAMStats(7), "共有ページに記憶されたLVS数"
  Debug.Print DBEngine.ISAMStats(8), "排他ページに記憶されたLVS数"
  Debug.Print DBEngine.ISAMStats(9), "LVSのページ数"
  Debug.Print DBEngine.ISAMStats(10),"レコードから移動されたLVS数"
  Debug.Print DBEngine.ISAMStats(11),"未使用のロード済みページ数"
  Debug.Print DBEngine.ISAMStats(12),"これの用途は不明。情報募集中です(汗)"
という感じです。

なお、各統計値をリセットするには、事前に
  DBEngine.ISAMStats 番号, True
を実行してやれば OK です。



また、ADOの場合は、こんな感じです。

    Dim Rs As ADODB.Recordset
    Dim Fld As ADODB.Field

    '(変数 Cn は、Microsoft.JET.OLEDB.4.0 による mdbへの ADODB.Connection)

    Set Rs = Cn.OpenSchema(adSchemaProviderSpecific, , "{8703b612-5d43-11d1-bdbf-00c04fb92675}")
    For Each Fld In Rs.Fields
        Debug.Print Fld.Value, Fld.Name
    Next
    Rs.Close
    Set Rs = Nothing


こちらは、OpenSchemaの実行前に、あらかじめ
    Cn.Properties("Jet OLEDB:Reset ISAM Stats").Value = True
を実行しておくと、毎回、値がリセットされるようになります。
（Falseのままにしておいた場合は、前回の値はリセットされず、値が蓄積されていきます）

こんばんは

魔界の仮面弁士さんにご紹介いただいた「茶筅」について、少々、発見がありましたので
報告がてら書き込んでおきます。

Windows版 茶筅(ChaSen)をVBなどから呼び出すためのラッパーDLL
http://member.nifty.ne.jp/hippo2000/ComCha/ComCha.htm
と
茶筅(ChaSen) Homepage（日本語）：http://chasen.aist-nara.ac.jp/index.html.ja

を使用しているんですが、茶筅の仕様ですが、結構キツイところがあります。
１．半角カタカナが混ざっていると強制終了してしまう。
２．「。」　chr(161) が混ざっていると、文書の終わりと判断されそれ以降は解析されない

１．は茶筅のMLの過去ログでズバリがありましたが、
２．に関しては茶筅の過去ログ「Windows 版茶筅で使用禁止な文字列について 」
　　を検査する。

対策
１．全ての半角カタカナは全角カタカナにあらかじめ変換しておく
２．「。」はあらかじめ全角の「。」に変換しておくか、茶筅の過去ログ
　　「Windows 版茶筅で使用禁止な文字列について 」を読んで総合的な検査方法を考える。

質問後、数日してからずっと悩んでいましたので、もし他の方で悩んでいる方でも
おられましたら参考になれば幸いです。

ところで、本題であった、DBの検索速度ですが、解析を使いこなしてない事や、その後の
データ加工などの改善点は多々ありますが、KeyWord検索にして、Likeを使わないように
する事で20数秒掛かっていたものが測る必要も感じないほど早くなりました。
DBサイズは2倍強になりましたけど。

感謝です。まだぼちぼちやっていきます。v^ ^

魔界の仮面弁士 さんが　08/25-11:01 No.5579 で紹介されていた
>ファイルベースの全文検索が可能な "Namazu for Win32" などが有名ですね。

"Namazu for Win32"　を　Windows98 SE で試して見ましたら、早くて便利です。
TXT HTM BAS FRM 等のインデックスが作成でき、指定したフォルダー（複数可）内のファイル
を検索してインデックスを作成してくれます。

　下記の掲示板のログの検索も Namazu を使っておられます。
　http://fukkey.dyndns.org/pins/search/search.html

　srchs092.exeを使用すれば、上記と同様の感じで、自PC上で使用できます。