出産前後の痔にはご注意!

テキストファイルからキーワードを拾って
SQLをなげています
SQLの質問になってしまうかもしれません
いまはADO接続でやっています

Open ファイル as input......

SQL = select * from tbl where name like '%キーワード%'
execute(SQL)

レコードセットの値で処理をいろいろ・・・
Loop

もともとのDBの件数がものすごくおおくてselect文に結構な時間が
かかってしまいます。速度をあげるほうほうってあるのでしょうか
私にはおもいつかなくて・・・

こういったほうほうは どう? ってのがありましたら
おしえていただきたいのですが よろしくおねがいします。

このQ&Aに関連する最新のQ&A

A 回答 (5件)

確かに・・・Like演算子・・・あまり使いたくないですね・・・


文字列比較は処理を遅くさせるし、増してや「=」ではなくLikeですから、膨大な時間がかかる恐れが・・・

ちなみにぼく自身、DB系を多くしています。今の仕事もDB系なのですが、元となるホストは他の会社が行っており、それにあわせて作らなければなりません。
で、そのホスト連携部分に文字列を比較しなければならない部分があるんですよ・・・・
自分の会社ならまだしも、他の会社がすでに設計済みのDBだから変えようがないのです。。。

まぁ愚痴っても仕方ないか・・・


なのでぼくも(不本意ながら)Like演算子を使用しています。


長い前置きはさておき・・・

本題のSQLのスピードなのですが、
http://homepage2.nifty.com/inform/vbdb/addnew.htm
こちらに面白い記述がありました。

AddNewにかかるスピードの検証で
Access データベースの場合: AddNew のほうが INSERT INTO より5倍以上速い
SQL Server の場合: INSERT INTO のほうが AddNew より 1.4倍 くらい速い
とあります。


たぶんで物を言ってはいけないと思うけど、言っちゃいます。

(1)もしDBがアクセスで
 >レコードセットの値で処理をいろいろ・・・
 のところがUpdate用のSQL文で処理を行ってる場合

 Recordsetをして、処理を行う


(2)もしDBがアクセス以外で
 >レコードセットの値で処理をいろいろ・・・
 のところがRecordsetで処理を行ってる場合

 Recordsetをせずに、UPDATE用のSQLを実行する


未検証なのですが多分イメージとして、こういうパターンが各DBに適してるのかな?

すでにこのパターンなのであれば、意味ないですね(^^;)

参考URL:http://homepage2.nifty.com/inform/vbdb/addnew.htm
    • good
    • 0

teebeeさん



AccessでもADOで接続したら、ワイルドカードは%なんですよ。
    • good
    • 1

ayato さん、こんばんは。


このSQLの書き方だと、ひょっとしてindexには期待できないのでしょうか...

ワイルドカードが"%"ってことは、少なくともアクセスじゃないなぁとかひょっとしたらOracle?とか、想像で書いてますが、ワイルドカードを使った検索でindex使えるのって前方一致の時だけじゃないです?
(有識者の方、間違ってたら指摘してください)

likeを使わなくちゃいけないの?とか気になっちゃいます。
他の列をキーにして検索できたりしないのでしょうか?

#なんか、逆に質問ばっかりになっちゃったみたいでごめんなさい。
    • good
    • 0

#1の方の通り、検索条件となるフィールドにインデックスを


作成することが一つあります。何でもつけていいというわけではありませんが。

また、プログラムの実装アルゴリズムですが、
DBの接続はSQLを発行するたびに接続しては遅いです。
さすがにayatoさんはつなぎっぱなしにしてますよね?
一度私はSQL発行ごとにDB接続をやってしまい、速度低下になってました(爆)。

また、検索条件があいまい検索をしなくてはいけないほど複雑なら、
テーブル設計に問題があることも考えられます。

ちょっとプログラムだけの話ではないですが、テーブル設計に問題がある可能性もあるということを知っていればいいかと思います。
    • good
    • 0

まず、likeを使ったあいまい検索は、全レコードを検索しますのでどうしてもパフォーマンスの低下が見られます。


可能であれば、使用を避けたほうが良いです。
その他には、テーブルに検索条件になるフィールドにインデックスを作成してみてはどうでしょうか。私の経験上かなりの改善が見られました。
    • good
    • 0

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qinsertを高速化させたい

問題:insertが遅い。20件程度の情報をテーブルにinsertするのに、30秒以上かかる。
環境:Windows XP, MYSQL(TABLE1のidにはindexつき)
仕様:textdata.csvを開き、idを取得する。取得したidをgetid変数に入れる。
   取得したidがTABLE1に無いか調べる。idが無ければinsert。
   あれば読み飛ばす。textdata.csvのidが無くなれば終わり。


con = CREATEOLEOBJ("ADODB.Connection")

//ここでCSVファイルを開き、idを取得しループさせる

getidSQL = "Select * from TABLE1 where id = '" + getid + "';"
RS = con.Execute(getidSQL)

If RS.EOF Then
//DB登録---------------------------------------------------------------------------------------
str = "INSERT INTO TABLE1 (id) values('" + getid + "');"
con.Execute(str)
//DB登録終了------------------------------------------------------------------------------------
endif

//CSVループ

ちなみに、DBにinsertせずに、かわりにテキストファイルに書き込む方法だと一瞬で挿入が完了します。
これをDBに書き込む時に高速化を図りたいのですが、何か良い方法はないでしょうか?

問題:insertが遅い。20件程度の情報をテーブルにinsertするのに、30秒以上かかる。
環境:Windows XP, MYSQL(TABLE1のidにはindexつき)
仕様:textdata.csvを開き、idを取得する。取得したidをgetid変数に入れる。
   取得したidがTABLE1に無いか調べる。idが無ければinsert。
   あれば読み飛ばす。textdata.csvのidが無くなれば終わり。


con = CREATEOLEOBJ("ADODB.Connection")

//ここでCSVファイルを開き、idを取得しループさせる

getidSQL = "Select * from TABLE1 where id = '" + getid + "';"
...続きを読む

Aベストアンサー

総データ数、インデックスの設定、ユニークやプライマリの設定によって
ことなりますが、

>idが無ければinsert。
>あれば読み飛ばす

という仕様であれば、idにユニークを設定し、
INSERT IGNORE INTO TABLE1 (id) values(・・・)
で十分ですね

QAccessのテーブルデータを一気にVBAで追加したい・・

Accessのテーブルデータを一気にVBAで追加したい・・

Accessに一時商品登録データというテーブルがあり、
問題なければ商品登録データにデータを流し込みたいと思っています

テーブルのデータ構造は全く同じです

VBAで一時商品登録のテーブルから一件ずつデータを読み取って
商品登録データに追加することは出来るのですが
一気にデータを追加する方法があれば教えていただけないでしょうか?

よろしくお願いいたいます

Aベストアンサー

本当にいろいろな方法があります。

一番簡単なのは、
あらかじめ「追加クエリ」を作成しておき

 Docmd.OpenQuery "追加クエリ名"

あるいは DAO で

 Set dbs = CurrentDB
 dbs.Excute "追加クエリ名"

追加クエリを使用しない場合は
追加クエリの SQL文 に相当するSQL を 実行。

 strSQL="INSERT INTO 商品登録データ SELECT 一時商品登録データ.* FROM 一時商品登録データ"

 Docmd.RunSQL strSQL

あるいは DAO で

 Set dbs = CurrentDB
 dbs.Excute strSQL

とか。

直書き、間違いがあったら御免。

QDAOとADOの違いについて

Accessからイントラネット上のデータベースに接続するための接続方法で困っています。
DAOとADOの違いが分からず困っています。
メリット、デメリットが分かる方、どうか教えてください。

宜しくお願い致します。

Aベストアンサー

DAO(データアクセスオブジェクト)はAccessで使用されるMicrosoftJetデータベースエンジン を公開した最初のオブジェクト指向インターフェイスです。DAOは単一システムのアプリケーションや小規模でローカルなネットワークで使用するのに最も適しています。
とMSDNライブラリでは説明しています。
DAOはJetデータベースエンジンを対象にして開発されているのでJetのもつセキュリティ機能・データ定義機能などを十分に操作することができます。
ADO(ActiveXDataObjects)ではオブジェクトの種類が簡素化されているためJetデータベースエンジンの細かな機能を制御することができないといった点が不足している部分がありますがADOXやJROの各オブジェクトモデルで使用できる機能を使えばADOで不足している機能をほとんど補うことができます。
DAOはデータベースにAccessを使用するアプリケーションの場合、最大のパフォーマンスを得ることができます。しかしアプリケーションを運用していくうちに規模が大きくなってデータベースをSQLServerに移行することがあるかもしれません。
そのような可能性が含まれているのであれば最初からADOで開発しておいたほうが無難です。ADOはSQLServerに対する処理で良いパフォーマンスを得ることができます。

Jetデータベースエンジンを主なターゲットにしているならDAOで可。それ以外のデータベースを利用するのであればADOを選択する方が良いのではと思います。

DAO(データアクセスオブジェクト)はAccessで使用されるMicrosoftJetデータベースエンジン を公開した最初のオブジェクト指向インターフェイスです。DAOは単一システムのアプリケーションや小規模でローカルなネットワークで使用するのに最も適しています。
とMSDNライブラリでは説明しています。
DAOはJetデータベースエンジンを対象にして開発されているのでJetのもつセキュリティ機能・データ定義機能などを十分に操作することができます。
ADO(ActiveXDataObjects)ではオブジェクトの種類が簡素化されて...続きを読む

QAccessのRefresh・Requery・Repaintの違い

Requeryはもう一度ソースレコード(テーブル)を読み込むようです。このとき、テーブルの先頭レコードに移動してしまいます。
Refreshは最新のレコード(テーブル)を再表示するような気がします。レコードの移動は起こらない気がします。
Repaintは、VBAでキャプションなどを変更したとき使っています。
でも、よくわかっていません。
どんなときにどんなメソッドを使えばいいのでしょうか?
詳しい方、よろしくお願いいたします。

Aベストアンサー

たびたびすみません。
調べてたらこんなのがでてきました。
http://www.nurs.or.jp/~ppoy/access/access/acF007.html

参考URL:http://www.nurs.or.jp/~ppoy/access/access/acF007.html

Q【ADO】「Execute」を使うときは「Update」は不要?

Sub test()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Desktop\test1.mdb"
Set rs = New ADODB.Recordset
rs.Open "テーブル1", cn, adOpenStatic, adLockPessimistic
cn.Execute "DELETE FROM テーブル1"
rs.Update '保存
Set rs = Nothing
Set cn = Nothing
End Sub

を実行したとき
テーブル1にデータがあるときはエラーにはならないのですが
何もデータがないときは「rs.Update」でエラーになります。
なので「Update」は消して実行していますが
「Update」がなくても「Execute」を実行した後は自動的に保存されるのでしょうか?

よろしくお願いします。

Sub test()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Desktop\test1.mdb"
Set rs = New ADODB.Recordset
rs.Open "テーブル1", cn, adOpenStatic, adLockPessimistic
cn.Execute "DELETE FROM テーブル1"
rs.Update '保存
Set rs = Nothing
Set cn = Nothing
End Sub

を実行したとき
テーブル1にデータがあるときはエラーにはならないのですが
何もデータがないときは「rs.Update」...続きを読む

Aベストアンサー

Sub test()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Desktop\test1.mdb"
Set rs = New ADODB.Recordset
rs.Open "テーブル1", cn, adOpenStatic, adLockPessimistic
cn.Execute "DELETE FROM テーブル1"
rs.Update '保存
Set rs = Nothing
Set cn = Nothing
End Sub

Sub test()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Desktop\test1.mdb"
cn.Execute "DELETE FROM テーブル1"
Set cn = Nothing
End Sub
と変更しても変わりはありません。
データベースの更新を行うだけが目的であるのならば、コピーを作成する必要はありません。

一方、ユーザーの操作を非同期(操作と処理が同時でないこと)で、データベースに反映したい場合にレコードセットを使用した更新を使用します。
Sub test()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Desktop\test1.mdb"
Set rs = New ADODB.Recordset
rs.Open "テーブル1", cn, adOpenStatic, adLockPessimistic
'フォームロードでレコードセットを作成し、ボタン押下で削除するなど
'今回は一括削除
Do Until rs.EOF
rs.Delete
rs.MoveNext
Loop
rs.close
cn.close
Set rs = Nothing
Set cn = Nothing
End Sub

Sub test()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Desktop\test1.mdb"
Set rs = New ADODB.Recordset
rs.Open "テーブル1", cn, adOpenStatic, adLockPessimistic
cn.Execute "DELETE FROM テーブル1"
rs.Update '保存
Set rs = Nothing
Set cn = Nothing
End Sub

Sub test()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLE...続きを読む

QAccess サブフォームでの選択行の取得

こんにちは。

Access初心者です。

サブフォームでテーブルの項目を表示させていますが、
選択された行を取得する方法はありますか?
サボフォームの下の方に現在選択されているレコード数が表示されてますが、その値でかまいません。

調べているのですが、なかなか検討がつきません。
宜しくお願い致します。

Aベストアンサー

フォーム名がフォーム1、サブフォームコントロールの名前がサブフォーム1だとすると、

Forms!フォーム1!サブフォーム1.Form.CurrentRecord

で取得できます。
(「Forms」と「Form」がありますのでご注意下さい)


また、フォーム1にコードを記述する場合であれば

Me!サブフォーム1.Form.CurrentRecord

サブフォーム1へのコード記述であれば

Me.CurrentRecord

という構文によっても、それぞれ取得が可能です。

QDoEvents関数って何?

こんにちは。

VBAやプログラミングに詳しい皆様に
教えていただきたい質問があります。

cells(1,1)からcells(5000,1)までの値を消去するときに
処理の進行状況を表示するためにuserform上にプログレスバーを表示したいと思います。

そこで下記のようなコードを入力しました。

userform1.show
for i =1 to 5000
cells(i,1)=""
userform1.progressbar1.value=i/5000*100
next i
unload userform1

しかしこれだとuserformの背景が真っ白になってしまい
ラベルの文字も消えてしまいます。
そこで「EXCEL VBA パーフェクトマスター」という本を見たら

for i =1 to 5000
cells(i,1)=""
userform1.progressbar1.value=i/5000*100
DoEvents
next i
unload userform1
と入力すれば解決することがわかりました。

しかし「DoEvents」についてあまり詳しく書いていなかったのでDoEvents関数をヘルプで見ると、
「発生したイベントがオペレーティング システムによって処理されるように、プログラムで占有していた制御をオペレーティング システムに渡すフロー制御関数です。」

と書いてあるのですが正直、書いてあることがよくわかりません。

どなたかDoEvents関数について、
もう少しわかりやすく教えていただけませんか。
それから、最初に書いたコードで実行すると
ユーザーフォームの背景が真っ白になってしまう原因も
教えていただけませんか?

よろしくお願いいたします。

こんにちは。

VBAやプログラミングに詳しい皆様に
教えていただきたい質問があります。

cells(1,1)からcells(5000,1)までの値を消去するときに
処理の進行状況を表示するためにuserform上にプログレスバーを表示したいと思います。

そこで下記のようなコードを入力しました。

userform1.show
for i =1 to 5000
cells(i,1)=""
userform1.progressbar1.value=i/5000*100
next i
unload userform1

しかしこれだとuserformの背景が真っ白になってしまい
ラベルの文字も消えてしまいます。
そ...続きを読む

Aベストアンサー

簡単に言うと、
OS に制御を渡すってことです。(ヘルプそのまんま)
時間が掛かるループ処理などの場合、ループが終わるまで制御は独占されてしまいます。
ですのでループ中は OS や Excel そのものにも再描画をさせる暇さえ与えません。
途中に DoEvents を入れると制御が OS に渡るので、OS は溜まっていた処理をそこで行うことができます。
結果、フォームの再描画などが行われることになります。

注意点ですが、
Private Sub CommandButton1_Click()
  Dim i As Long

  For i = 1 To 50000
    DoEvents
    Cells(i,1) = ""
  Next i
End Sub

Private Sub CommandButton2_Click()
  MsgBox "hoge"
End Sub

っていうフォームのコードがあった場合、
DoEvents を入れることによって、ループ中にユーザーがCommandButton2 を押すことによって CommandButton2 のクリック イベントも動いちゃいます。
CommandButton1 のクリック イベントではループの前に
CommandButton1.Enabled = False
CommandButton2.Enabled = False
を書いてフォーム上の CommandButton を無効にしておき、ループが終わったら
CommandButton1.Enabled = True
CommandButton2.Enabled = True
と書いて CommandButton を有効に戻してください。

これを工夫すれば、CommandButton2 で CommandButton1 のループを途中キャンセルする処理もすることができます。

Private Canceled As Boolean

Private Sub CommandButton1_Click()

  CommandButton2.Enabled = False

  Dim i As Long
  For i = 1 To 50000
    DoEvents

    If Canceled = True Then
      MsgBox "キャンセルしました"
      Exit Sub
    End If

    Cells(i, 1).Value = ""
  Next i
End Sub

Private CommandButton2_Click()
  Canceled = True
End Sub



コードの行頭にあるスペースは見易さのために全角スペースで作成していますので、これをこのままコピペするとエラーになるかもしれません。
コピペするなら行頭の全角スペースを半角スペースに直してください。

簡単に言うと、
OS に制御を渡すってことです。(ヘルプそのまんま)
時間が掛かるループ処理などの場合、ループが終わるまで制御は独占されてしまいます。
ですのでループ中は OS や Excel そのものにも再描画をさせる暇さえ与えません。
途中に DoEvents を入れると制御が OS に渡るので、OS は溜まっていた処理をそこで行うことができます。
結果、フォームの再描画などが行われることになります。

注意点ですが、
Private Sub CommandButton1_Click()
  Dim i As Long

  For i = 1 To 50000
...続きを読む

QACCESS ADOでupdateが効かない

いつもありがとうございます。

現在、ADOを使ってカレントプロジェクト以外のMDBファイルのテーブルにレコードを追加する処理を行っているのですが、何のエラーメッセージも出ないままレコードが追加されません。

この場合、updateコマンドが実行された後の状態(リターンコード)を調べる事はできないでしょうか?

Aベストアンサー

前回の回答を訂正します。

<ある環境下ではエラーが起きないこともあります!>

さて、本日、Execute での UPDATE 文が実行されないという不具合に遭遇しました。
エラーもなく表面上は「実行しました!」とのメッセージ。
「ウーン!」と考えて、UPDATE 文の発行による更新を断念。
SELECT文で読み込み1レコードづつ更新するようにコードを訂正しました。
結果、この不具合は解消されました。

多分、実行したPCが某社のCAD。
「Windows、Office の更新は誤動作の原因となるのでしていない」とのこと。
こういう場合、非手続き的なコードよりも手続き的なコードの方が信頼が高いと感じました。
質問者も、シコシコと更新手続きを書いてみると解決するかもしれません。

「エラーが出ると思う!」との下りは、このような事情で撤回します。

Qカレントレコードが無い事を判定させる方法

SQLを使ってmdb内のレコードセットを取得し、
無かったらエラーを返す、という処理を作成
する場合、カレントレコードが無い事を
判定するにはどうすれば良いでしょうか?
イメージは下記のような感じです。

レコードセット as DAO.Recordset

'レコードセット取得
 Set レコードセット = db.OpenRecordset(作成したSQL文)

'判定
If カレントレコード無 Then
MsgBox "エラーメッセージ"
Exit Sub
End If

Aベストアンサー

カレントレコード無は"レコードセット.RecordCount = 0"
で行けませんか?

Q【ACCESS】フォーム名/コントロール名を文字列型変数で指定するには

バージョン:Access2002

フォーム「frm01」にテキストボックス「tb01」が作ってある場合、
Forms.frm01.tb01.Value="あいう"
とすれば、フォームもコントロールも指定できるのですが、
Dim strTxt As String
strTxt = "tb01"
Forms.frm01.strTxt.Value="あいう"
だと、文字列型変数"strTxt"が展開されないのでフォーム「frm01」のコントロール「strTxt」を探してしまいエラーになってしまいます。

文字列型変数でフォームやコントロールを指定するには、どのようにすればよいのでしょうか?

Aベストアンサー

フォームの場合
 Forms(strFrm)

フォームのコントロールの場合
 Forms(strFrm).Controls(strTxt)


これでも参照できますが、普通ここまで省略しませんね。
 Forms(strFrm)(strTxt)

あとで見たとき、訳がわからなくなりそう。


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング