SQLiteパフォーマンス計測 SELECT vs SUM

#動機
SQLiteを使ったAndroidアプリを開発している。
日々積み上がるデータを集計する必要があり、長期間の運用に耐えうるパフォーマンスが得られるか検証してみた。

#評価対象

実プロジェクト用の検証なので、そのままのコードは晒せないがデータ構造は以下の様になる。Int,Long,BooleanはINTEGER型としてStringはTEXT型として対応するテーブルを作成している。

kotlin

class Record{
	var num1 : Int = 0
	var num2 : Int = 0
	var date : Long = 0　// ★ INDEXを作成している
	var num3: Int = 0
	var num4: Int = 0
	var num5: Int = 0
	var num6 : Int = 0
	var amount : Long = 0
	var num7 : Int = 0
	var text1 : String = ""
	var text2: String = ""
	var num8 : Int = 0
	var num9 : Int = 0
	var flag1 : Boolean = false
}

#評価方法

1日30件ほど積み上がるデータを20年間蓄積した場合、年単位の集計をどの程度の時間でこなせるかを評価したい。
またSELECTを使った場合とSUMを使った場合の性能の違いも確認してみたい。
行った試験は以下の４パターン

• SUMで年単位で集計

• SUMで全期間(20年）で集計

• SELECTで年単位で集計

• SELECTで全期間(20年）で集計

※SUNを使った集計はSUM(amount)としてSQLを発行している。
※SELECTを使った集計はRecord中のamount:Longのみ取り出して加算している。
※年単位のデータ数は10,950件、20年分だと219,000件になる。

kotlin

Log.i("test", "---- select test (per year) ----")
var totalMS:Long = 0
for(year in 2000..(2000+(years-1))) {
	val range = DateRange(DateEx(year,1,1), DateEx(year+1,1,1))
	val t1 = Date().time
	var sum = sumBySelect()
	val t2 = Date().time
	Log.i("test", "year${year} sum:${sum} time:${t2-t1}ms")
	totalMS += (t2-t1)
}
Log.i("test","select total:${totalMS}ms")

Log.i("test", "---- select test (all year) ----")
val range = DateRange(DateEx(2000,1,1), DateEx(2000+years,1,1))
val t1 = Date().time
var sum = sumBySelect()
val t2 = Date().time
Log.i("test", "select all year sum:${sum} time:${t2-t1}ms")

#試験環境
評価にはHUAWEI P20 lite (Android9)を使用した。
AndroidStudioからデバッグモードでリモート実行しているので、リリース版を実機にインストールして評価すればもっと良い結果が得られると思う。

#試験結果

---- SUM (per year) ----
  year:2000 sum:10950000 time:21ms
  year:2001 sum:10950000 time:20ms
  year:2002 sum:10950000 time:19ms
  year:2003 sum:10950000 time:20ms
  year:2004 sum:10950000 time:19ms
  year:2005 sum:10950000 time:19ms
  year:2006 sum:10950000 time:20ms
  year:2007 sum:10950000 time:20ms
  year:2008 sum:10950000 time:20ms
  year:2009 sum:10950000 time:19ms
  year:2010 sum:10950000 time:20ms
  year:2011 sum:10950000 time:19ms
  year:2012 sum:10950000 time:19ms
  year:2013 sum:10950000 time:20ms
  year:2014 sum:10950000 time:19ms
  year:2015 sum:10950000 time:20ms
  year:2016 sum:10950000 time:19ms
  year:2017 sum:10950000 time:19ms
  year:2018 sum:10950000 time:19ms
  year:2019 sum:10950000 time:19ms

                         totalMS:390ms

---- SUM (all year) ----
  all year: sum:219000000 time:348ms

---- SELECT (per year) ----
  year:2000 sum:10950000 time:195ms
  year:2001 sum:10950000 time:35ms
  year:2002 sum:10950000 time:35ms
  year:2003 sum:10950000 time:37ms
  year:2004 sum:10950000 time:36ms
  year:2005 sum:10950000 time:36ms
  year:2006 sum:10950000 time:36ms
  year:2007 sum:10950000 time:36ms
  year:2008 sum:10950000 time:35ms
  year:2009 sum:10950000 time:35ms
  year:2010 sum:10950000 time:36ms
  year:2011 sum:10950000 time:35ms
  year:2012 sum:10950000 time:36ms
  year:2013 sum:10950000 time:36ms
  year:2014 sum:10950000 time:35ms
  year:2015 sum:10950000 time:36ms
  year:2016 sum:10950000 time:35ms
  year:2017 sum:10950000 time:36ms
  year:2018 sum:10950000 time:35ms
  year:2019 sum:10950000 time:34ms
    
                         totalMS:870ms
    
---- SELECT (all year) ----
  select all year: sum:219000000 time:5596ms

#考察
SUMについては20年間運用しても実用的なパフォーマンスで集計できることが分かった。
両者を比較するとSELECTはSUMに対して1.8倍程度時間(初回除く）がかかっている。初回は10倍程度の処理時間になる。
またSELECTを使って全期間を集計した場合、年単位で集計したものを合計した時間より遥かに長い時間(6倍)がかかっている。これは推測だが全レコードを収容するResultSetをメモリ上に確保するのに時間を要するのかもしれない。