Access 削除クエリが重い

お世話になります。

環境：Access2003
　　　 SQL Server 2008 R2 Express

先日、Accessのローカルにあるテーブルが20万レコードを超えた為、SQLサーバーへ移行しました。

しかし、Accessより削除クエリにて（フォーム上に設置した削除ボタンにて）当該テーブルのレコードを削除しようとすると、4分ほどかかりました。

なお、ローカルにあったときは上記操作をしても2、3秒程度で削除完了してました。

PCのスペックやテーブルのレコード数によるかとは思うのですが、ローカルテーブルとSQLテーブルでこんなに時間が違うものなのでしょうか。

また、回避策や何か設定すれば早くなるなどございましたら、ご教授頂けたらと思います。

宜しくお願い致します。

No.3 回答者： x-1919 回答日時： 2012/11/06 08:11

SQL Server 側の設計をどのようにしたのかわからないが、適切なインデックスを作成し、クエリの見直しを行うのが第一歩だと思う。

一般的に WHERE で使われる列はインデックス候補となりやすいため、受注日のインデックスを作成する。
そしておそらくは VBA のコードの中で
Dim sql As String
sql = "DELETE testデータ.受注日 FROM testデータ WHERE ((testデータ.受注日)=#" & juchu & "#));"
とかやっているのかと思うが、SQL 文を動的に作成して実行するのをやめて、SQL Server 側にストアド プロシージャとして作成しなおし、UI 側からはこのプロシージャに必要な日付値を与えてキックする方法にする。

とりあえずそんなところか？

この回答への補足

結果的に、SQL上で当該テーブルのIDを主キーとし、更に受注日をインデックスすることにより、削除クエリを実行しても2、3秒で削除することが出来るようになりました。

補足日時：2012/11/08 19:46

この回答へのお礼

ご回答ありがとうございます。

>適切なインデックスを作成し、クエリの見直しを行うのが第一歩だと思う。
主キーとインデックスの違いもいまいち分からない状況です。。

当該テーブルはSQL側ではどのフィールドにも主キーをもたせず、Accessより当該テーブルのリンクテーブルを作成する際に『ID』フィールドを主キーとして指定しました。で、その状態で削除クエリが重いので、受注日を主キーとしたリンクテーブルも作成しました・・・しかしそのテーブルで削除クエリを実行すると『1行の更新/削除により、1行以上のリンクテーブルに影響がありました。固有インデックスに重複する値が含まれています。』とエラーになり、削除できませんでした。

このエラーについて調べたところ『実行した更新クエリまたは削除クエリの効果が、リンクされている SQL テーブルにあるほかのレコードにも作用しました。更新および削除クエリは、1 回に 1 つのレコードのみに作用します。』ということでした。当該テーブルには同じ受注日が複数レコード存在してます。主キーを指定したフィールドで抽出した場合、複数レコードを一括して削除できない？？ということなのでしょうか。。

>SQL Server 側にストアド プロシージャとして作成しなおし、
>UI 側からはこのプロシージャに必要な日付値を与えて
>キックする方法にする。

については、勉強不足ですみません。。

お礼日時：2012/11/06 09:43

No.2 回答者： IDii24 回答日時： 2012/11/05 23:07

おそらくAccessでリンクテーブルを開いてそこで削除処理をしているのでしょう。

これはAccessがSQLのデーターを特定できてないからです。つまり20万件の情報を読み込むだけ読み込む。さらにAccess側で削除レコードを特定する。そのレコードをまたSQLに問い合わせる。問い合わせて見つかったレコードに削除の処理をかける。SQLに反映させる。
そういう大量データーの行ったり来たりを行っていると考えられます。Acceｓｓにはレコードを特定できる機能はないので。カーソル処理と言ってポインターを置くだけなんです。
ですからAccessをSQLにつなぐ場合はリンクテーブルを使ってはダメなのです。VBAで書くか、あるいはパススルークエリーで表示だけさせる。さらにExecuteコマンドで特定のレコードにDELETE SQL文を直接投げる。これでおそらく一秒もかからず処理できます。
つまり乗りこなすには、それだけ知識も勉強も必要だということです。

No.1 回答者： IDii24 回答日時： 2012/11/05 17:56

SQLServerとは大規模なデーター処理にも耐えうる設計になってます。

つまり安全に処理もできればスピード優先の処理もできます。
ただその加減は開発者のスキル次第と言う事になります。つまりオートマの車なら特によいですが、F1の車ですから、テクニックとメカニックのサポートが必要なのです。
削除とはどういう処理かと言えば、削除対象を検索して、テーブルに削除フラグを立て、ログをログ領域に書き出す処理です。つまり大変な処理なんですよ。これは後から処理をトレースして確実に戻すこともできるためです。さらに削除領域にはデーターは残るのでいつかはコンプレス作業も必要です。削除を繰り返すとログ領域がいっぱいになり、何時かはエラーになります。知らないうちにHDDがいっぱいと言うのはこのためが多いです。
この場合は古いログ領域を切り捨てる必要があり、切り捨てコマンドを打つかバックアップコマンドでバックアップするかしかありません。
単純にAccessのように使いたいというならAccessの方が便利です。ですがどうしても削除する場合はログを取らずに消すことができます。これはSQLコマンドの後にログを取らないコマンドをつける。あるいはテーブルのデーター全部を消す場合はTruncateのコマンドでクリアできます。
当然これらの処理ではデーターが戻らなくなるので、通常はやりません。なぜなら大きな大事な情報を預かるDBですから。

この回答への補足

SQL上にて当該テーブルのIDを主キーとしたところ、削除クエリの実行が2、3秒で出来るようになりました。但し、この状態だと、受注日でFindfirstするのに時間がかかる為、更に受注日をインデックスすることによって削除も早くでき、受注日のFindfirstも早くできるようになりました。

補足日時：2012/11/08 19:52

この回答へのお礼

早速のご回答ありがとうございます。
今回AccessのローカルテーブルをSQLへ移行した理由としては、レコード数が増えたのと、他のPCよりDBを参照させたい為でした。

このテーブルには『受注日』というフィールドがあり、削除クエリではその日付を条件にレコードを削除しています。
DELETE testデータ.受注日 FROM testデータ WHERE ((testデータ.受注日)=#" & juchu & "#));
※juchu変数にはフォーム上で選択された受注日が入ってます。

単純に選択クエリだと2、3秒で抽出できるているので、あとはそのレコードを削除すればいいだけなので、なんでそんなに時間がかかるのか・・・と思った次第です。

確かに教えて頂いた通り、SQLでは削除するにもログに書き込んだり色々やっているのでしょうが、こんなに時間がかかるものなのでしょうか。。

実際にAccessのローカルテーブルの場合だと、選択クエリで抽出するぐらいの時間で削除できています。

お礼日時：2012/11/05 19:12