アクセスで大量のデータを扱う際の注意は？

質問

アクセスである機器での計測データの管理、集計などの作業を行いたいと考えています。
計測データのレコード数が多いので、それらを効率良く管理するためのノウハウを知りたいと思い質問させて頂きました。

実際にレコード数がどの程度かというと、計測間隔が30秒に1回、計測機器が最大90点まで接続可能であることから、
一日最大259,200レコード、さらに時間と機器名称のデータが各レコードに追加されます。(各計測値は小数点以下第2位までの小数)
これを1年積み重ねると94,608,000レコードという膨大な数量になってしまいます。
さらにこれを何年、というかたちで積み重ねてゆくことを考えて、これらの大量のデータを効率よく管理、または集計に利用するために
必要な知識、テーブルの作り方やファイル管理の方法などをご教授頂けたらと思います。

拙い知識の中で私が現在検討している内容としては、

(1)１年半前までのデータはテーブルに残しておきたい
(直近データの処理レスポンスを重視したい/前年同月での比較等を行いたいため)

(2)１年半以上前のデータはどこか別の所へバックアップし、必要な時に読み込んでくる様にしたい
(１年半以上前のデータは集計に利用する頻度が稀であるため)

(3)アクセスで行う集計はごく簡単なもの
(ある期間を指定したらそのデータを抽出し、グループ毎に和算をして、それをグラフ化する。
一度の集計で利用するのは、最大でも全体の中の1,000レコード程。多くの場合は120レコード程しか使用しない)

(4)基本的にアクセスで行う集計にはレスポンスの速さが要求される。(特に直近データ)

基本的にデータは大量なのですが処理自体はとても簡単なものなのです。

大量のデータを保存している影響を処理スピードに与えないようなシステムを作るにはどんな所に注意をしたらよろしいのでしょうか？

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

回答No.6

venzou · ベストアンサー

No3です。
-----------------------------------------
＞各計測値は小数点以下第2位までの小数
前回書くのを忘れましたが、
100倍して整数化すれば、データベースは多少軽くなり、
集計の高速化も少し期待できますね。

-----------------------------------------
＞2分毎のデータが要求されます。
と言うことであれば、1/4ですね。
年間最大 23,652,000レコード、Accessでは辛いかな（＾＾；

予算的に、商用データベースが無理なら、
無料で利用できるデータベースを検討してみてはいかがでしょうか？

SQL Server 2005 Express Edition
http://www.microsoft.com/japan/sql/editions/express/default.mspx

SQL Server 2005 Express Editionとは
http://www.thinkit.co.jp/free/article/0603/9/1/

MSDE 2000
http://www.microsoft.com/japan/sql/msde/default.mspx

「SQL Server Express」と「MSDE」を比較する
http://itpro.nikkeibp.co.jp/article/COLUMN/20060412/235184/

↑Microsoftブランドなので、導入しやすい？

PostgreSQL
http://ja.wikipedia.org/wiki/PostgreSQL

↑フリーのデータベースの定番？

-----------------------------------------
＞よって、データを大量に保存しているから処理が遅くなるという事態はなんとか避けたいのが実情です。

VBAでファイル（CSV｜バイナリ）から読み込む処理は、
速度的にはかなり不利です。

集計の度に読み込み・・・と言うのは、本当に最終手段だと思います。

速度的には、C言語などで専用のアプリを作る方が良いと思いますが、
こういう方向に走ると、データベースを利用する意味がなくなってきまね。（＾＾；

お礼

貴重な情報ありがとうございます。
情報システム部門と相談して商用データベースの導入も検討していきたいと思います。

集計の都度読み込みはやはり最終手段ですね。
とはいえ現状はこれでやるしか方法は無さそうなのでなんとかやってみたいと思います。

専用アプリ...これはもう夢のまた夢の世界ですw

回答No.5

noname#140971

' ====================
' 　ログファイル保存要領
' ====================

Dim Amounts(2880) As Double

を、ランダムファイルで保存。
機器番号1はレコード１
機器番号2はレコード2
・・・・・
機器番号99はレコード99

ファイル名=20071010

実際問題としては、Amounts（）のインデックスを時間と看做せばこういうことでも可能。
ファイルサイズ＝22275KB+α

CSV形式で、機器番号、計測時間、計測値の生データで保存することが現実的か否か？
ランダムファイルで、いわば、全体を一つの巨大な構造体変数として保存することを考えなくて良いのか？

' =====================================================
' 　ファイルを直接に読み込み計算してはなぜダメなのか？
' =====================================================

フォルダーを年、月で作成し、その中に日別のランダムファイルを保存しておく。
そうすれば、さしたる手間もなくデータを読み込めると思います。
この場合、フォルダー、ファイル名、レコード番号を指定すれば検索なんて無用。
これが一番高速と思われるのだが・・・。

' ===================================================
' 　計算結果を二次データとしてデータベースに保存する
' ===================================================

もちろん、ログの解析計算を何度も行うのは無駄の無駄。
そこで、解析結果をデータベースに保存するというアイデアが浮上。

まあ、個人的な意見です。

補足

ご返答いただきありがとうございます。

(1)ランダムファイルについて知らなかったので調べてみました。
成程、ランダムファイルとシーケンシャルファイルがあることがわかりましたが、今回私が作成しようとしているMDBは特定の１レコードについて処理を行うのではなく、ある範囲のレコードを集計・グラフ化といった作業を行う都合上、ランダムファイルは向かないのでは？と感じました。
また元のファイル(変換後)については、グラフ化等の作業は行うもののデータ自体に変更は加えません。
それに加えて多ユーザーからの同時アクセスの可能性も考えると、シーケンシャルファイル形式の方が良いのではないかと考えますがどうでしょうか？もし「そういう事ではない」等あったらご教授願いたいと思います。

(2)直接読み込みについて
読み込みの方法案としては、'YYYYMMDD'.csvにして、テキストボックスなどからの入力に対し変数で対応させたいと考えています。
年・月でフォルダを分けることは考えていませんが、この部分に関してはそれほど検索スピードは変わらないと思うのですがいかがでしょうか？

(3)二次データ
たしかに同じ解析を何度も行うのは無駄だと思います。
ただほとんどの場合同じ解析を後日行うことは無いのが実情なのです。
解析結果を保存しておくと逆にデータベースが膨れ上がってしまう様な気がするのですが...どうなのでしょう？

回答No.4

noname#60992

No2です。
(1)日付別のCSVファイルというのは、いわゆるログファイルですよね。　データベースでデータ管理を行う必要があるのか、ログ解析ソフト（及びデータ書き出しソフト）だけで用が足りるのかどうかは全体の構成を考える上では重要なポイントです。　
(2)については、あくまでもデータベース管理を行ううえでのお話です。　データを入れておくMDBはテーブルのみを用意する。　その他、データの取り込みや、抽出、統計などは、他のMDBを使う。　その際にはリンクテーブルを使うよりは、ODBCなどで直に接続することが望ましい。（これができればデータベースを他のものに変更するのも簡単です。）

補足

ご返答頂きありがとうございます。
データベースとログ解析の本来の意味はよくわかりませんが、データをどんどん積み重ねていくという構造ではないかもしれません。
CSVで出力するのはファイルサイズ軽減のためで、実際は下でも書いたのですが処理したデータを別MDBでグラフ化する形を考えています。
やはりデータ取り込み＆変換＆日付別出力専用でMDBを組んだ方が良いみたいですね。
このCSVデータを別MDBで読み込ませてグラフ化しようと思います。

回答No.3

venzou

＞一日最大259,200レコード
これだけ多いと、全てのレコードに目を通すことは無いでしょう。

集計が目的なら、ある程度集計したデータをテーブルに残し、
元データは削除します。（最適化も忘れずに）

例えば1時間毎の集計にしておけば、1/120の量で済みます。
年間で 788,400レコード。Accessでも問題ないレベルでしょう。

＞(3)アクセスで行う集計はごく簡単なもの

どういう集計結果を残すのかは、この集計の内容に依存します。
この集計に必要最低限の情報のみをテーブルに残す方針で。

集計の内容によっては、この回答の方法が有効な場合もありますが、
逆に使えない場合もあります。

＞(4)基本的にアクセスで行う集計にはレスポンスの速さが要求される。

集計に必要なデータはテーブルにあるので、レスポンス的にも有利です。
（ファイルを開いて、集計するより軽くなります）

＞日付別でCSVファイルに出力する

これには、賛成です。
元データが必要になった場合は、VBAを使って、
参照する仕組みを作れば良いと思います。

補足

ご返答頂きありがとうございます。
データの間隔は、社内で利用する資料の関係上最低でも2分毎のデータが要求されます。
よって最大まで集計してもあまりレコード数が減らないのが現実です。
それであればいっそのことそのまま使ってしまおうかと考えているのです。

(3)の集計ですが、これはある期間(時間)と特定の計測機器を指定し、そのデータをフォームでグラフ化する
といった内容です。処理内容としては抽出＆グラフ化でしょうか。
(いくつかの機器をグループ化する事もあるので和算はあります。)
よって、データを大量に保存しているから処理が遅くなるという事態はなんとか避けたいのが実情です。
ただしこの処理は今回のデータ保存用MDBとは別のMDBで行います。

(4)ですが、元データはすでにCSVで落ちているものを取り込んでます。
そのままでは集計に利用できない形式(データ配列)になっているので、これを利用しやすい形に変換、保存する用のMDBを考えています。
(上で書いた通り集計、グラフ化するMDBは別に作ろうと思っています。)
変換したデータのうち当日分を除くものを日付別で出力させておき、集計用MDBから指定された日付のCSVを処理用テーブルに取り込む、
しかしこの場合は各処理用MDBが直接CSVファイルを読み込みに行った方が早いですかね？
処理用MDBは多数あって、それが同時に同じCSVを参照(読み込み)しても問題ないなら
「元データ取り込み＆変換＆出力」
「読み込み＆抽出＆集計＆グラフ化」
で完全に二分化してしまおうか、と書いていて思いました。

回答No.2

noname#60992

パフォーマンスおよび、管理リスクを考えるとアクセスはお勧めできませんが、どうしてもアクセスで行いたいなら、
(1)データを分ける必要があります。
（例えば日付別にするとか、計器別の月別とか）
(2)データ部分とフロントエンドを切り離すことも必要です。
(3)データ部分に関しては、ファイルサイズの上限もありますので、時折最適化行える仕組みが必要。　
(4)バックアップする仕組みが必要。（停止時間を発生させることができるのかなど?）

補足

ご回答頂きありがとう御座います。
No.1での回答にも書きましたが、(1)については日付別でCSVファイルに出力する様に変更しようかと考えております。

(2)のデータ部分とフロントエンドを切り離すというのは実際にどのような構造のことを言うのでしょうか？
現状では日時データ・機器コード・計測値の３要素で１レコードになっております。

(3)適正化(4)バックアップは、夜間などに時間を決めて行う仕組みを作ろうと考えております。

回答No.1

noname#140971

服飾デザイナでプログラマではない門外漢ですが・・・。

１、Access に計測データを登録して参照するのは不可能。
２、目的を達するのに相応しい仕組みを考えるのが先決。

例えば、Access に登録したデータを100レコード参照する際にも１００万レコードが関係するのがAccess。
SQL　Ｓｅrver などですと、これが純粋に100レコードの参照で済みます。
当然に、こういうリンクテーブルという仕組みのAccessは大量のデータは処理不能。

＜目的を達するのに相応しい仕組みを考えるのが先決＞

「なーんだ、そんなやり方であれば、速くてあたり前」－これを考えるのが先決じゃないですかね。

・膨大なデータは、単にテキストファイルで保存しておく。
・最終的に集計・計算する直前の二次データのみをデータベースに落とす。
　ここまでは、プログラミング言語の仕事。
・二次データは SQL Server、Oracle で管理する。
　ここからは、データベース言語とそのフロントアプリケーションの役割。

単なる一例ですが、仕組みというか仕掛けの問題じゃないですかね。
それを、データベースの設計的テクニックに置き換えて思案しても・・・。

と、私は思います。

補足

ご回答頂きありがとう御座います。
テキストファイルというのはCSV形式という事でしょうか？
実はこのデータは元々1日1ファイルの計測値がCSVファイルで出力されたものを取り込んでいます。
そのままの状態だと諸所の都合により集計に不向きだったので、それを変換したものをテーブルに保存しておこうと考えておりました。
なるほど、Accessは全体のレコードが関係してしまうのですね。
ということはこの変換したファイルをまた1日1ファイル毎に出力して保存をしておけば良いということでしょうかね？

たしかに、仰るとおりアクセスのスキルというよりデータベースの構造自体の問題ですね、これは。
現状ではアクセスを使用しなければならない事情がありますので、なんとかその中で収まるようにしたいと思います。
逆にアクセスの範囲内で無理であれば仕様自体を変更してその中に収める予定です。
まぁゆくゆくはSQL等への移管も考えなければならないと思いますが...