MS Access Tips/Sample and VBA and Blog customize etc...

テキストマッチングしたセルの背景色を変更する処理をもっと高速化

前記事から続きです。さらなる高速化にチャレンジします。

タイトル画像

難易度:

高速化対策5 AutoFillterを1回で済ませる

AutoFillter の抽出条件は、 Criteria1、 Criteria2 の2つまでですが、Operator:=xlFilterValues を指定すると、 Criteria1 に複数の値を持つ配列を指定出来て、それに合致するデータのみに絞り込めます。これを使って、抽出、背景色変更処理を1回で済ませるようにします。

実行結果(イミディエイトウィンドウ)

4504件というの配列に格納したデータ数です。AutoFilter は1回のみです

70秒から20秒に短縮できました。しかし、高速化対策3 の配列でループする方法の14秒には負けてます。

高速化対策6 Like演算子の比較をInStrに置き換える

部分一致比較を下記のようにLike演算子でしているが、

これを、下記の InStr の式に置き換えたらどうだろうかと試してみました。

NGDomainSetCollor5 の Like を InStr に置き換えてみました。

実行結果(イミディエイトウィンドウ)

高速化対策3 の14秒を超えました。
部分一致の比較は、Likeより InStrの方が高速なんですね。
意味の分かり安さはLikeの方ですので通常はLikeでいいですが、速度を追求したいときは InStr を使うといいですね。

しかし、高速化対策3 もLikeを使ってますので、それも InStr に変更してみましょう。
NGDomainSetCollor3 の Like を InStrに変えて実行してみました。

実行結果(イミディエイトウィンドウ)

最初の対策なしコードで 265秒 かかっていたのが、5秒まで短縮できたのでもう十分といえるのだか、もう少し頑張ってみよう。

高速化対策7

下記で紹介してる関数でそれぞれの処理をミリ秒レベルで計測してみました。

VBAでミリ秒以下の高精度で処理時間計測 - hatena chips

高速化対策5 の最後の、AutoFilter、背景色変更処理は 70ミリ秒(0.07秒)ぐらいしかかかってないので、
ちがいは、ループ内の処理ということになる。

高速化対策3

高速化対策5

背景色変更処理より、動的配列に値を格納する処理の方か重いということになります。
ちなみに、ReDim Preserve で配列を拡張するのが重いのかと、事前に必要なサイズにしておいてから、ループするようにしてみたが、ほとんど改善は見られませんでした。

ここの改善は難しそうなので、別の部分を検討しましょう。InStrが意外と高速というのがわかったので、ループで部分一致を探すのではなく、検索対象のドメインを連結して一つの文字列にして、それを InStrで検索したらどうだろう、ということで試してみました。

実行結果(イミディエイトウィンドウ)

やりました。ついに1秒を切りました!!
NGDomainSetCollor5の改良版(AutoFillterは1回のみ LikeをInstrに変更)が9秒でしたので、10倍の高速化です。
InStr恐るべし。ただ、コードは難解です。数カ月後に見たらちょっと自分でも解読に苦労しそうです。

NGDomainSetCollor3の改良版もこの手法が使えば高速化できるかもしれませんが、マッチングしたドメインをインデックスに変換する必用があるのですが、これが複雑になりそうです。今回はここまでとします。

拍手する

1 Comments

Skm says..."ひさしぶりに"

久しぶりにAccessをさわる機会があり、昔出入りしていたAccessClubが
復活していたことに驚きを覚え
hatena様のことも思い出し、訪れました。
精力的に活動されていらっしゃるようで大変嬉しくコメントを残させていただきます。
またいろいろ勉強させていただきます。

2017.05.31 18:03 | URL | #EBUSheBA [edit]

Leave a reply






Trackbacks

trackback URL
http://hatenachips.blog34.fc2.com/tb.php/512-f372f2bc
該当の記事は見つかりませんでした。