Microsoft Access Club Access超初心者対象Forum Access初級者対象Forum Access初・中級者対象Forum Access VBA Tips Forum DAO、ADO、SQL Forum

     

リストへもどる

投稿記事の一括表示

タイトルAccessの抽出条件で関数を用いた時の実行スピード
記事No171984
投稿日: 2017/08/12(Sat) 22:58
投稿者チャコール
解決済: ON
OS:Windows 10
Access Version: Access 2016

以下のいずれが早いのでしょうか?
1. テーブルのとカラムAに「テスト」が登録されている場合と「テスト」が登録されている場合があり、これを2つの抽出条件を使って抽出する場合
2. strConv([カラムA],4)で変換した結果を「テスト」というキーワードで抽出する場合

2つのテーブルを1つのキーで結合し、カラムAにはインデックスをつけています。 件数は2万件程度です。

あまり変わりがないかもしれませんがご存知の方教えていただけないでしょうか

タイトルRe: Accessの抽出条件で関数を用いた時の実行スピード
記事No171985
投稿日: 2017/08/13(Sun) 14:53
投稿者hatena
解決済: ON
> 以下のいずれが早いのでしょうか?
> 1. テーブルのとカラムAに「テスト」が登録されている場合と「テスト」が登録されている場合があり、これを2つの抽出条件を使って抽出する場合
> 2. strConv([カラムA],4)で変換した結果を「テスト」というキーワードで抽出する場合
> 
> 2つのテーブルを1つのキーで結合し、カラムAにはインデックスをつけています。 件数は2万件程度です。

片方の「テスト」は半角ということですね。(投稿時に動で全角に変換されたのかな?)

関数の中にフィールドを入れてしまうと、インデックスは使用できませんので、遅くなります。

ちなみに、クエリでは、全角と半角は区別されませんので、条件は全角の(あるいは半角の)「テスト」だけでも、
全角と半角の両方が抽出されます。

タイトルRe^2: Accessの抽出条件で関数を用いた時の実行スピード
記事No171986
投稿日: 2017/08/13(Sun) 21:08
投稿者チャコール
解決済: ON
> > 以下のいずれが早いのでしょうか?
> > 1. テーブルのとカラムAに「テスト」が登録されている場合と「テスト」が登録されている場合があり、これを2つの抽出条件を使って抽出する場合
> > 2. strConv([カラムA],4)で変換した結果を「テスト」というキーワードで抽出する場合
> >
> > 2つのテーブルを1つのキーで結合し、カラムAにはインデックスをつけています。 件数は2万件程度です。
>
> 片方の「テスト」は半角ということですね。(投稿時に動で全角に変換されたのかな?)
>
> 関数の中にフィールドを入れてしまうと、インデックスは使用できませんので、遅くなります。
>
> ちなみに、クエリでは、全角と半角は区別されませんので、条件は全角の(あるいは半角の)「テスト」だけでも、
> 全角と半角の両方が抽出されます。

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

関数の中にフィールドを入れると遅くなるということで、フィールドを関数で全角にせず、抽出条件時のみにstrConv関数を使い抽出すれば、抽出条件が1つとなりますが、この方がインデックスが使えて早いということでよろしいでしょうか。

また、一点申し上げるのを忘れていました。ODBC経由でORACLEのテーブルの2つに接続しております。それをAccessのクエリで2つのテーブルを結合し抽出条件を設定しておりました。Accessだと半角全角が関係ないことを教えていただいた後に確認しました。
当方では.NETなどのインターフェイスからその項目について全角で入力はできるものの、なぜか半角で入力するルールになっているのにもかかわらず全角で入力してくるユーザがいるため、前回何度かうまく抽出できず、現在、2つの抽出条件を設定しております。

タイトルRe^3: Accessの抽出条件で関数を用いた時の実行スピード
記事No171987
投稿日: 2017/08/14(Mon) 12:40
投稿者hatena
解決済: ON
ORACLEに接続しているのですね。

> 関数の中にフィールドを入れると遅くなるということで、フィールドを関数で全角にせず、抽出条件時のみにstrConv関数を使い抽出すれば、抽出条件が1つとなりますが、この方がインデックスが使えて早いということでよろしいでしょうか。

「抽出条件時のみにstrConv関数を使い抽出すれば、抽出条件が1つとなります」というのが具体的にどのような設定なのか分かりません。
想定している条件式(WHERE句)を提示してもらえますか。


> また、一点申し上げるのを忘れていました。ODBC経由でORACLEのテーブルの2つに接続しております。それをAccessのクエリで2つのテーブルを結合し抽出条件を設定しておりました。Accessだと半角全角が関係ないことを教えていただいた後に確認しました。
> 当方では.NETなどのインターフェイスからその項目について全角で入力はできるものの、なぜか半角で入力するルールになっているのにもかかわらず全角で入力してくるユーザがいるため、前回何度かうまく抽出できず、現在、2つの抽出条件を設定しております。

オラクルでは全角半角を区別するのですね。
なら、2つの抽出条件を設定するのでいいと思います。

それ以前に、速度を重視するなら、パススルークエリにしたほうがいいでしょう。

ODBC経由のテーブルでクエリを作成すると、すべてのデータをアクセスに読み込んでから、アクセス側で抽出処理等をすることになります。
パススルークエリは、オラクルの方でSQL処理をして、結果だけをアクセスに送りますので、高速になります。(ただし更新はできません。)

あと、.NETなどのインターフェイスで入力させているなら、全角で入力したら、入力後に半角に変換するような処理を実装しておくのが普通だと思います。

タイトルRe^4: Accessの抽出条件で関数を用いた時の実行スピード
記事No171988
投稿日: 2017/08/14(Mon) 12:59
投稿者チャコール
解決済: ON
> ORACLEに接続しているのですね。
>
> > 関数の中にフィールドを入れると遅くなるということで、フィールドを関数で全角にせず、抽出条件時のみにstrConv関数を使い抽出すれば、抽出条件が1つとなりますが、この方がインデックスが使えて早いということでよろしいでしょうか。
>
> 「抽出条件時のみにstrConv関数を使い抽出すれば、抽出条件が1つとなります」というのが具体的にどのような設定なのか分かりません。
> 想定している条件式(WHERE句)を提示してもらえますか。
今自宅なのでOracleではないのですが、イメージとしてはこんな形ではないかと思っているのですが、間違いでしょうか。問題はこの備考フィールドにはセイヒンという言葉だけでなく違う文字もセイヒンという文字と共に追加されてきます。そうすると以下のSELECT文ではだめだということになると思いますので、すると2つの抽出条件は変わらずということになるのではないかと思います。
1つの場合
SELECT T_TEST.製品コード, T_TEST.製品名, T_TEST.備考
FROM T_TEST
WHERE ((StrConv([備考],4)="セイヒン"));
2つの場合
SELECT T_TEST.製品コード, T_TEST.製品名, T_TEST.備考
FROM T_TEST
WHERE (((T_TEST.備考) Like "*セイヒン*")) OR (((T_TEST.備考) Like "*セイヒン*")); 
上記2つの場合の抽出条件のいずれかのセイヒンは半角です。このフォームは半角を全角に変えてしまうのですね。
>
>
> > また、一点申し上げるのを忘れていました。ODBC経由でORACLEのテーブルの2つに接続しております。それをAccessのクエリで2つのテーブルを結合し抽出条件を設定しておりました。Accessだと半角全角が関係ないことを教えていただいた後に確認しました。
> > 当方では.NETなどのインターフェイスからその項目について全角で入力はできるものの、なぜか半角で入力するルールになっているのにもかかわらず全角で入力してくるユーザがいるため、前回何度かうまく抽出できず、現在、2つの抽出条件を設定しております。
>
> オラクルでは全角半角を区別するのですね。
> なら、2つの抽出条件を設定するのでいいと思います。
>
> それ以前に、速度を重視するなら、パススルークエリにしたほうがいいでしょう。
>
> ODBC経由のテーブルでクエリを作成すると、すべてのデータをアクセスに読み込んでから、アクセス側で抽出処理等をすることになります。
> パススルークエリは、オラクルの方でSQL処理をして、結果だけをアクセスに送りますので、高速になります。(ただし更新はできません。)
>
> あと、.NETなどのインターフェイスで入力させているなら、全角で入力したら、入力後に半角に変換するような処理を実装しておくのが普通だと思います。

パススルークエリですね。わかりました。挑戦してみます。

なお、.NETですが、パッケージ製品なのでカスタマイズがそこまで及んでいないらしく、全角入力できるのにもかかわらず検索機能を使うには半角文字でないとダメだということで半角にできるものは半角で入力するというルールになっているということを聞きました。とても変わった運用と仕様になっているんだなと思っております。私も半角に変換するような処理が必要かと思いますが、今の時代どこのシステムも全角で入力できるようになってますから、ハードディスクも安くなりましたし、データベースの性能も向上していますので、全角で入力しても問題のない仕様に変更する提案をしたいと思います。その方が既存ユーザや新たなユーザが普通に使えて楽になると思っています。

いろいろ教えていただきありがとうございました。本件解決です。

タイトルRe^5: Accessの抽出条件で関数を用いた時の実行スピード
記事No171991
投稿日: 2017/08/16(Wed) 03:15
投稿者hatena
解決済: ON
> 今自宅なのでOracleではないのですが、イメージとしてはこんな形ではないかと思っているのですが、間違いでしょうか。問題はこの備考フィールドにはセイヒンという言葉だけでなく違う文字もセイヒンという文字と共に追加されてきます。そうすると以下のSELECT文ではだめだということになると思いますので、すると2つの抽出条件は変わらずということになるのではないかと思います。

部分一致の条件ということですか。
だとしたら、どの方法もつかってもインデックスは無効です。
あいまい条件でインデックスが有効なのは、前方一致のみです。

> 1つの場合
> SELECT T_TEST.製品コード, T_TEST.製品名, T_TEST.備考
> FROM T_TEST
> WHERE ((StrConv([備考],4)="セイヒン"));

これは部分一致ではないですね。
完全一致です。しかも、フィールドが関数内ですので、インデックスも無効です。

WHERE 備考="セイヒン" OR 備考="セイヒン";

というようにすればインデックスは有効です。

2つの場合
> SELECT T_TEST.製品コード, T_TEST.製品名, T_TEST.備考
> FROM T_TEST
> WHERE (((T_TEST.備考) Like "*セイヒン*")) OR (((T_TEST.備考) Like "*セイヒン*")); 

Likeの部分一致ですので、インデックスは無効です。

- 以下のフォームから自分の投稿記事を修正・削除することができます -
処理 記事No パスワード

ページの先頭へ 前ページへ戻る