ACCESS のクエリー実行に異常に時間がかかる

質問

以前、Microsoft Answers で下記の質問をしていましたが、良い回答がなかなかつかないので、あらためてこちらで質問させていただきます。
http://answers.microsoft.com/ja-jp/office/forum/office_2010-access/%E3%82%AF%E3%82%A8%E3%83%AA%E3%83%BC%E3%81%AE%E5%AE%9F%E8%A1%8C%E3%81%AB%E6%99%82%E9%96%93%E3%81%8C%E3%81%8B%E3%81%8B%E3%82%8A%E3%81%99%E3%81%8E%E3%82%8B/5726aaf8-3e97-4658-96e2-9a6da44759bc

最初の回答にあるSP1のアップデートが出るのを待っていました。
先日、SP1のアップデートが出たので、さっそくインストールしました。
しかし、ほとんど改善されず、せっかく購入したのに使えない状態です。
Microsoft に問い合わせても、「データ量が多すぎるのが原因、それ以上のことはわかりません」といった答えしか返ってきません。

困っています。どなたかぜひお助け願います。

回答No.5

ssfja

#1補足です。

http://code.google.com/p/cowares-excel-hello/source/browse/trunk/alpha/ssf_access_writer_alpha.txt

ここの 1,353行目に、mdbファイルを開き、
CurrentDb 相当のものを取得する記述があります。
周辺を見れば Access Object との関連は理解できると
思います。

これは Excel VBA ですが、 VBScript でも同じです。
参照設定の代わりに CreateObject になるぐらいです。

これらのキーワードがわかれば、
検索すれば、もっと丁寧に説明したページも出てくると思います。

お礼

回答ありがとうございます。
URL　参考にさせいてただいて、わかりにくいところがあればまた補足します。

回答No.4

nora1962

レジストリですが64bit上の32bitAPの場合
[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Debug]
"JETSHOWPLAN"="ON"

の可能性があります。

お礼

回答ありがとうございます。
レジストリキーを一度よく調べて、わかりにくいところがあればまた補足します。

補足

レジストリキーのWow6432がなく、Wow6432Nodeとなっているのですが、それでよろしいのでしょうか。
また、Engines以下にDebugがないのですが、どうすればよろしいのでしょう。
レジストリキーなどふだんさわることがないので、まったくわかりません。

回答No.3

nora1962

500万件のうち何件ぐらい抽出しようとしているのでしょうか？
結構多いのが、大量のデータ抽出をインデックスを使ったランダムアクセスで行って、CPUは空いていてI/O WAITを起こしているパターンです。

レジストリをいじることになりますが、JETで実行されるSQLの実行計画をファイルに出力することが出来ます。

http://monado.dtiblog.com/blog-category-35.html

ace形式で取得できるかは確認していませんがMDB形式なら

windows7 access 2010 では
レジストリキー
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Debug]
"JETSHOWPLAN"="ON"

にセットすると\Users\ユーザ名\Documentsに「showplan.out」が出力されます。
これで実際にどんなアクセスをしているか確認してはどうでしょぅ。

お礼

回答ありがとうございます。
参考URL、レジストリキー、一度よく調べて、わかりにくいところがあればまた補足します。

補足

抽出するデータ件数は多いときで3000件強です。

回答No.2

t-ka

テーブルの構造はどうなっているでしょう？

[テーブル名]tbl01
[ID] オートナンバー型　インデックス　はい（重複なし）
[日付]　日付/時刻型　　インデックス　いいえ
[コード]　長整数型　　インデックス　いいえ

にて提示いただいた下記のコードを実行し500万行にてテストしました。

Sub CreateT()
Randomize
Dim db As Database, rs As Recordset, i As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("select * from tbl01")
For i = 1 To 5000000
rs.AddNew
rs("日付") = #1/1/2011# + Int(1000 * Rnd)
rs("コード") = Int((1000 * Rnd) + 1)
rs.Update
Next
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
End Sub　

選択クエリ内に於いて下記の様なSQLを実行しました。
概ね5秒程度で結果が返ってきます。

"SELECT * FROM テーブル名 WHERE コード = " & コード & " ORDER BY 日付 DESC;"

しかし、[コード]フィールドのインデックスをはい（重複あり）になっていると
結果が返ってくるまで時間がかかり且つ安定しません。

私は、AC2000のJET内のことなので、ACEで64bitの場合は他の影響があるかもしれません。

お礼

回答ありがとうございます。
テストまでしていただいて恐縮です。

補足

テーブルの構造ですが
　テーブル名　DATATABL

フィールド
　DATE 日付/時刻型　インデックス　はい (重複あり)
　CODE 数値型　整数型　インデックス　はい (重複あり)
　以下データ　数値型　テキスト型等　いずれもインデックスなしです

DATE とCODEがともに同じレコードはありません。

回答No.1

ssfja

オフィス2010も64bit環境も持っていません。

そのため突っ込んだ手助けは出来ませんが、
リンク先を読み、まだ試すべきものがあると思い、
以下に挙げます。

1. VBScript と Excel VBA で試す。

VBScript で同種のコードを試すことにより、
オフィス2010に固有の現象かどうかを確認できます。

Excel VBA は、さらにそれが Access 2010 VBA のみの
現象かどうかを確認する目的です。

VBScript で問題が起きないなら、明確にオフィス2010 VBA の
障害でしょう。

2. 日本語のみのテーブルを作って試す。

これは、フィールド名やクエリ文に日本語があることが
影響しているかどうかの確認です。
日本語固有の問題は現地開発者が気づかないので、
無視されがちだからです。

これで明らかな違いが出れば、
Access 2010 VBA の日本語資源の扱いで起きた障害と断定できます。

3. OpenRecordset に書いたクエリを、Access 側に製作し、
そのクエリを開く。

これで違いが出るようなら、クエリのコンパイル回りで発生した
障害だと考えられます。

1,2,3 のいずれかで、「遅くならない」ものを見つけることができれば、
ハードウェアとは無関係な、 Microsoft 製品のみを原因とした障害です、
と報告できると思います。

お礼

回答ありがとうございます。
VBScript の使い方及び Excel VBA で ACCESS のデータの呼び出し方がわかればためしてみたいと思います。

補足

1.について
VBScript の使い方、及び Excel VBA で ACCESS のデータの呼び出し方がよくわかりません。
　もう少しくわしくご説明いただけませんでしょうか。
　無知で申し訳ありません。

2.について
　リンクURLのSQL文は、わかりやすくするために一部日本語で書いていますが、実際はすべて英文字です。
　コード・・・CODE、日付・・・DATEなど

3.について
　リンクURLの中には書いていませんが、クエリーのデザインビューで作ったクエリーを実行しても、同じくらい時間がかかっています。