2011年6月13日月曜日

Sqliteでの指定件数削除

自作しているアプリがデータを溜め込む系の方針のため、運用するごとにデータがかさんでいくわけです。
ユーザに意識させないように削除させるにはということで以下のような設計とします。

・保持数を設定値として持つ
・一定間隔で総件数を取得し、保持数+1/10を超過したら削除処理を行う(10万件保持なら11万件超過で起動)

これを実現するために以下のようなSQLを考える。

10万件目の特定カラムを取得
(参考にしたのはこことかここ

select testid from testtable ORDER BY testid DESC LIMIT 1 OFFSET 100000
※上記でtestidは時系列にインクリメントされるカラムとする。

で、このSQLが実行できるかを確認するのに
Cursor cur = _db.rawQuery(SQL文字列, null);
if (cur.moveToFirst()) {
~中略~
}
cur.close();
としたらcur.moveToFirst()の時点でandroid.database.sqlite.SQLiteDiskIOException: disk I/O errorが発生した。

対象のテーブルは、約40MBのデータ、18万件程度が格納されていてまぁ1アプリとしては多いかなと。
とはいえ、18万件入っている中から10万件目を1件だけ取得したいといってるわけだから、diskI/Oてと思うわけで。
いろいろ試した結果
100件目→OK、5万件目→OK、8万件目→NG、10万件目→NG
となりました。
うーん、大量データが投入されたテーブルにoffset指定で取得するのは限界があるのかな。
※どんなExceptionが出るかはここが詳しい。

もともと試験的にSQLの実行と取得値の確認をしたかっただけなので、100件ができてるならいいかなとして次にすすむ。
上記で取得できるであろう指定件数目のカラム値を境界値として副問い合わせで削除処理を行う。

delete from testtable where testid < (select testid from testtable ORDER BY testid DESC LIMIT 1 OFFSET 100000)



こんな感じ。
これを
_db.execSQL(SQL文字列);
と実行してみたら時間はかかったけど期待通りの動きになった。

勝手な教訓。
・Androidで大量データに対するOFFSET指定かつレコード取得は使えない。

2 件のコメント:

  1. アプリ設計で、ユーザーが意識せずにアプリが勝手にデータを消すという設計はあまりよくないかと。データストレージ系アプリというのはデータが大事だからストレージする訳で、それを知らない所で勝手に削除するのはまずいです。個人使用アプリならそれでも問題ないですか、実装するならば、ストレージ対象の空き容量を定期的にチェックし、該当サイズを超えたら保存できなくすべきです。もしくは、〇サイズを超えたら古い物から削除というUIを実装すべきですね。

    返信削除
  2. データの重要性と、想定動作が行えるかの環境確認の2点あると思います。

    ・データの重要性について
    データがユーザの意識化で登録されたアプリという前提であればそのとおりですね。

    言及はしていませんでしたが、ここであげているアプリは「Twitterのツイートを日々自動取得して必要に応じて後から見る」というタイプのもので、ユーザが意識せず溜め込んだ上でユーザが意識せず削除するというものでした。

    業務アプリで言うトレースログレベルの扱いというスタンスで見てくださいな。
    ※ユーザの了承のもと、そういった動作をしているということです。

    ・想定動作に対する環境確認
    つど確認というわけには行きませんが、ディスク容量を圧迫してから身動き取れなくなる前に、異常検知して動作しないというのは必要ですねー。

    この点はまさに個人使用レベルということで(xx

    返信削除