sqlite3 | singular point

【SQLite3】ハイパフォーマンスなインデックスをつけるために覚えておくべきこと

（この記事は旧ブログからの転載です。）

「インデックスは付いているはずなのに検索速度が遅い！」

ということはありませんか？わたしは見事にハマりました。

私の場合、個人使いがほとんどでシビアな性能が要求される web アプリなどを作っているわけではありません。
そんなこともあって、あまり細かいことは気にせず

「データベース検索の際のキーとなるカラムには適当にインデックスをつけておけばいいよなー」

程度に考えて CREATE INDEX ～をしていました。
ところが、インデックスのつけかたにはいくつかの抑えておくべき点があったのです。

ここではいろいろ実験してみてわかったことを書いてみようと思います。
“【SQLite3】ハイパフォーマンスなインデックスをつけるために覚えておくべきこと”の続きを読む

[R] RSQLite の使い方 (2)

前回は 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;")

RSQLite（＋RSQLite.extfuns）パッケージを使うことで R から SQLite3 へ非常に簡単にアクセス可能になる。SQLite 本体もどうやらパッケージに含まれているようで、事前にインストールする必要がないため、パッケージをインストールするだけで非常に簡単に使い始めることができます（パッケージ自体も 1MB もないくらいのサイズです。如何に SQLite が小さいかがよくわかります）。

クエリーを投げる、トランザクション、などの基本的機能は完備されています。さらにデータフレームを一気に INSERT するための dbWriteTable などのおかげで便利に使えます。以下に使い方を示していきます。

一番シンプルな使い方

# (0) 準備：ドライバに SQLite を指定し，データベースをオープン．
#           コネクション・オブジェクト (S4) が帰ってくるので
#           保持しておく．
dbname="test.db"
library(RSQLite)
driver=dbDriver("SQLite")
con=dbConnect(driver,dbname)

# (1) dbGetQuery : すでに存在するデータベースを open してテーブルを取得
# （hoge というテーブルを持っていると仮定）
tbl=dbGetQuery(con,"SELECT * from hoge;")

# (2) dbSendQuery : テーブルが帰ってこない(SELECT 以外の？)クエリーは
#     dbSendQuery を使うべき → エラー処理が可能に
#     (dbGetQuery でも NULL が返るだけでエラーにはならない)
rs=dbSendQuery(con,"CREATE TABLE new_tbl(id INTEGER, hoge TEXT);")
dbinfo=dbGetInfo(rs)
print(dbinfo)

# dbinfo の中身
#=> $statement
#=> [1] "CREATE TABLE new_tbl(id INTEGER, hoge TEXT);"
#=> $isSelect
#=> [1] 0
#=> $rowsAffected
#=> [1] 0
#=> $rowCount
#=> [1] 0
#=> $completed # 正常に終了したか？
#=> [1] 1
#=> $fieldDescription
#=> $fieldDescription[[1]]
#=> NULL

データフレームの取得、挿入

R のデータフレームはリレーショナルデータベースと似た構造になっているので、write.table, read.table のようなノリで読み込み、書き込みを行うことができます。

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)

# 書き込めているかテスト
dbGetQuery(con,"SELECT * FROM random limit 5;")

#=>   row_names    random1    random2
#=> 1         1  0.2354093  1.7083581
#=> 2         2 -0.8101699 -0.6867071
#=> 3         3  0.4537572  1.8399874
#=> 4         4 -2.1129855  1.0211205
#=> 5         5  0.9778609 -0.9177596

# テーブルをデータフレームとして一括取得
dbReadTable(con,"random")

(注意) マニュアルによれば dbReadTable, dbWriteTable はトランザクションを利用しないようです。

続きを書きました。

タグ: sqlite3