SQL 問い合わせの性能確認手順

SQL 問い合わせの性能実験：SQL 問い合わせ対象となるデータのデータサイズと性能との関係を見る．

処理時間の計測では，計測のばらつきを抑えるため，計測のたびにキャッシュのクリアを行い，3回の計測の平均をとる． 計測中のプロセッサ，ストレージの利用状況の確認は，dstat（システム計測ソフト）のページで説明している．

このページで紹介しているソフトウェア類の利用条件等は，利用者で確認すること．

前準備

SQLite 3 について: 別ページ »にまとめ

SQLite 3 での SQL 問い合わせの性能

SQL 問い合わせのテスト実行（SQLite 3 を使用）

次のように設定して実行すること．

エラーメッセージが出ないことを確認． 

# 集計（group by）の問い合わせ
echo "select region, count(*) from T1000M_1 group by region;" | time sqlite3 T1000M_1.db
# 選択（where）の問い合わせ
echo "select birth_cap from T1000M_1 where birth_cap > 80000 limit 10;" | time sqlite3 T1000M_1.db

SQL 問い合わせでのデータサイズと処理時間の関係（SQLite 3 を使用）

データサイズを 250M バイトから 12000M バイトまで変えながら，SQL 問い合わせの処理時間を計測する． 計測のばらつきを抑えるため，計測のたびにキャッシュのクリアを行い，3回の計測の平均をとる． 

#!/bin/bash
function elapsed() {
    echo `python3 -c "import sys; print( float(sys.argv[2]) - float(sys.argv[1]) )" $1 $2`
    return
}

function avg() {
    echo `python3 -c 'import sys; print("%.3f" % ((float(sys.argv[1]) + float(sys.argv[2]) + float(sys.argv[3])) / 3.0) )' $1 $2 $3`
    return
}

# キャッシュのクリア（PageCache, dentries and inodes）
function cache_clear() {
    /usr/bin/sync
    /usr/bin/sync
    /usr/bin/sync
    /usr/bin/sync
    /usr/bin/sync
    sleep 3
    sudo sysctl -w vm.drop_caches=3 > /dev/null
    echo 3 | sudo tee -a /proc/sys/vm/drop_caches > /dev/null
    return
}

# $1: SQLite 3 Database name, $2: Table name
function sqlite3_query() {
    echo "select region, count(*) from $2 group by region;"
    start=`date +%s.%N`
    echo "select region, count(*) from $2 group by region;" | sqlite3 $1 > /dev/null
    current=`date +%s.%N`
    echo `elapsed $start $current`
    return
}

# 250M 
cache_clear
elapsed1=`sqlite3_query T250M_1.db T250M_1`
cache_clear
elapsed2=`sqlite3_query T250M_1.db T250M_1`
cache_clear
elapsed3=`sqlite3_query T250M_1.db T250M_1`
echo 250M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 500M 
cache_clear
elapsed1=`sqlite3_query T500M_1.db T500M_1`
cache_clear
elapsed2=`sqlite3_query T500M_1.db T500M_1`
cache_clear
elapsed3=`sqlite3_query T500M_1.db T500M_1`
echo 500M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 1000M 
cache_clear
elapsed1=`sqlite3_query T1000M_1.db T1000M_1`
cache_clear
elapsed2=`sqlite3_query T1000M_1.db T1000M_1`
cache_clear
elapsed3=`sqlite3_query T1000M_1.db T1000M_1`
echo 1000M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 2000M 
cache_clear
elapsed1=`sqlite3_query T2000M_1.db T2000M_1`
cache_clear
elapsed2=`sqlite3_query T2000M_1.db T2000M_1`
cache_clear
elapsed3=`sqlite3_query T2000M_1.db T2000M_1`
echo 2000M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 4000M 
cache_clear
elapsed1=`sqlite3_query T4000M_1.db T4000M_1`
cache_clear
elapsed2=`sqlite3_query T4000M_1.db T4000M_1`
cache_clear
elapsed3=`sqlite3_query T4000M_1.db T4000M_1`
echo 4000M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 8000M 
cache_clear
elapsed1=`sqlite3_query T8000M_1.db T8000M_1`
cache_clear
elapsed2=`sqlite3_query T8000M_1.db T8000M_1`
cache_clear
elapsed3=`sqlite3_query T8000M_1.db T8000M_1`
echo 8000M: `avg $elapsed1 $elapsed2 $elapsed3` 

# 12000M 
cache_clear
elapsed1=`sqlite3_query T12000M_1.db T12000M_1`
cache_clear
elapsed2=`sqlite3_query T12000M_1.db T12000M_1`
cache_clear
elapsed3=`sqlite3_query T12000M_1.db T12000M_1`
echo 12000M: `avg $elapsed1 $elapsed2 $elapsed3`

インデックスの効果の確認（SQLite 3 を使用）

選択（where）の問い合わせについて，インデックス作成の前後で処理時間を比べる． 「T1000M_1」のところはテーブル名，「T1000M_1.db」のところは SQLite 3 データベース名に設定して実行すること． 

# インデックス作成前の問い合わせ
echo "select count(*) from T1000M_1 where birth_cap > 80000;" | time sqlite3 T1000M_1.db
# インデックスの作成（作成自体にも時間がかかることを確認）
echo "create index idx_birth_cap on T1000M_1(birth_cap);" | time sqlite3 T1000M_1.db
# インデックス作成後の問い合わせ
echo "select count(*) from T1000M_1 where birth_cap > 80000;" | time sqlite3 T1000M_1.db

Python からの SQL 問い合わせと処理時間の計測

Python 標準ライブラリの sqlite3 モジュールを使う． 下のプログラムをファイル名 sqlquery.py で保存し，次のように実行する． 

python3 sqlquery.py T1000M_1.db T1000M_1

#!/usr/bin/python3
# SQL 問い合わせの処理時間の計測
# 使い方: python3 sqlquery.py ＜SQLite 3 データベース名＞ ＜テーブル名＞
import sys
import time
import sqlite3

dbname = sys.argv[1]
tablename = sys.argv[2]

conn = sqlite3.connect(dbname)
cur = conn.cursor()

# 集計（group by）の問い合わせ
start = time.perf_counter()
cur.execute("select region, count(*) from %s group by region" % tablename)
result = cur.fetchall()
elapsed = time.perf_counter() - start
print(result)
print("group by: %.3f sec" % elapsed)

# 選択（where）の問い合わせ
start = time.perf_counter()
cur.execute("select count(*) from %s where birth_cap > 80000" % tablename)
result = cur.fetchall()
elapsed = time.perf_counter() - start
print(result)
print("where: %.3f sec" % elapsed)

conn.close()

csvkit による CSV ファイルへの直接の SQL 問い合わせ

csvkit の csvsql を使うと，CSV ファイルをデータベースにインポートすることなく，SQL で直接問い合わせできる． 内部では CSV ファイル全体をメモリ上の SQLite データベースに読み込んでから問い合わせを実行する．

csvkit のインストール（Ubuntu 上） 

sudo apt update
sudo apt -y install python3-pip
pip3 install -U csvkit

csvsql による SQL 問い合わせ．「T250M_1.csv」のところは CSV ファイル名，「T250M_1」のところはテーブル名（拡張子を除いたファイル名になる）に設定して実行すること． 

time csvsql --query "select region, count(*) from T250M_1 group by region" T250M_1.csv
time csvsql --query "select birth_cap from T250M_1 where birth_cap > 80000 limit 10" T250M_1.csv

考察ポイント