前回は R で SQLite を利用するための基本中の基本を示しました。
今回は高速化の方法など少しだけ踏み込んだ内容について記します。
トランザクションの利用
トランザクションを使って大量のクエリを高速に処理します。トランザクションって何?って人はググってみてください。前回も書きましたが、dbWriteTable などはトランザクションを利用しないので、場合によってはこちらを使ったほうが速い、のかもしれません(未確認)。
library(RSQLite)
driver=dbDriver("SQLite")
dbname="test.db"
con=dbConnect(driver,dbname)
# 準備
test.tbl=as.data.frame(matrix(rnorm(100),nc=2))
colnames(test.tbl)=c("random1","random2")
rs=dbWriteTable(con,"random",test.tbl,row.names=F)
# トランザクション開始
dbBeginTransaction(con)
rs=dbSendQuery("DELETE FROM random WHERE random1>0 AND random2>0;")
# DELETE される行が 10 行より多ければロールバックする(DELETEしない)
if(dbGetInfo(rs)$rowsAffected > 10){
cat("Rollback!")
dbRollback(con)
} else { # そうでなければコミットする(DELETE が反映される)
cat("Commit!")
dbCommit(con)
}
# コミットされたかどうかチェック
#(この場合コミットされていないのでデータベースの内容はそのまま)
dbGetQuery(con,"select count(*) from random;")
#=> count(*)
#=> 1 50
C/C++-API でいうところの sqlite3_prepare & sqlite3_bind_XX の利用
これも高速化のテクニックのひとつです。あらかじめパラメータつきのクエリを準備しておいてパラメータに値を次々に代入して SQL 文を実行するための方法です。
library(RSQLite)
driver=dbDriver("SQLite")
dbname="test.db"
con=dbConnect(driver,dbname)
# 準備(上で作った random テーブルがすでにあれば必要なし)
test.tbl=as.data.frame(matrix(rnorm(100),nc=2))
colnames(test.tbl)=c("random1","random2")
rs=dbWriteTable(con,"random",test.tbl,row.names=F)
# Prepare された SQL ステートメントを使ってデータ挿入
dbBeginTransaction(con)
keys=data.frame(r1=runif(10), r2=runif(10)) # 適当なデータ
# セミコロンに続いてデータフレームのカラム名で指定
pquery="INSERT INTO random (random1, random2) VALUES (:r1,:r2);"
rs=dbSendPreparedQuery(con, pquery, keys)
dbCommit(con)
拡張関数の利用
RSQLite.extfuns パッケージを使うと sin, exp, floor のような数学関数,trim のような文字列関数,stdev, mode, median のような統計関数が使えるようになります。
これらの関数を使うことでより複雑なクエリを生成することが可能になり、結果として処理速度の向上が見込めるかもしれません。
下の例では有名なアヤメデータ(iris)に対して、
- 種(Species)ごとにグループ分けして がく片の長さ(Sepal.Length)の標準偏差の小さい順にデータフレームを作成
という処理をしています(注:plyr パッケージの ddply 関数を使えばほぼ同じことができます)。
library(RSQLite)
library(RSQLite.extfuns) # 拡張関数用のパッケージ
driver=dbDriver("SQLite")
dbname="test.db"
con=dbConnect(driver,dbname)
init_extensions(con) # 拡張関数が使えるようにするためのおまじない
# 準備(iris データを使う)
data(iris)
rs=dbWriteTable(con,"iris",iris,row.names=F)
# Species ごとにグループ分けして Sepal.Length の標準偏差の小さい順にデータフレームを作成
dbGetQuery(con,"select Species, stdev(Sepal_Length) as slstdev from iris group by Species order by slstdev;")