SQLの駅結合度付ビュー

[xkikeg]

CREATE VIEW station_kilo
   AS SELECT * FROM (SELECT station.*,count(*) AS jointcount
                          FROM station NATURAL JOIN kilo GROUP BY kilo.stationid)
                      NATURAL JOIN kilo;

というViewを現在与えているが、さらにNATURAL JOIN lineしてしまったほうが多分使い勝手はいい（それほど良くなるか？というのは置いておいて。）
しかし、自分のThinkPad X40で実行した時には付け加えた時の実行時間の遅さは半端無かった。今自分の手元のマシンで試してみると一瞬で出てきたのだが、これは再考の余地があるかもしれない（例えばたまたま重いプログラムが裏で走ってたとか）。
実際に後で実機で試して見るべき。

[xkikeg]

結論

• 作ったビューに対してline.linename LIKE '東海道'とかしてしまうと実行時間がとても遅くなることが分かった。
  • ちなみにline NATURAL JOIN ...すると若干は早い（でもちょっと遅い）
• 一方line.lineid=69のように直接路線IDで引くと早いことが分かった。

実行環境

あと、X201s(Ubuntu 11.10 oneiric)で実行するとCore i7の威力なのかSQLite3のバージョンが3.7.3から3.7.7に上がったからか、めっちゃ早い。

• 一応実行計画読んでみたけど意味不明。
• Pythonで実験したところX201sでは25%程度のオーバヘッドが加わる。
• 一方T40 (Debian squeeze)ではidで引くと1秒/op.程度なのがname LIKEで11秒/op.になる。これはアーキテクチャというよりはSQLiteの実装がショボかったんではという気がしてくる。

結論としてはどうせlinenameでLIKEするうっかりさんはいるんだから最初からlineも結合しておけば便利なんじゃねという感じ。実験用のコードは下に示す。

#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3
import time

DB = sqlite3.connect("ares.sqlite")

def lineid():
    for i in range(100):
        cur = DB.execute(u"SELECT * FROM station_kilo NATURAL JOIN line WHERE lineid=69")
        for i in cur: pass

def linename():
    for i in range(100):
        cur = DB.execute(u"SELECT * FROM station_kilo NATURAL JOIN line WHERE linename LIKE '東海道'")
        for i in cur: pass

def get_time(function):
    begin = time.clock()
    function()
    end = time.clock()
    return end - begin

print "lineid: ", get_time(lineid), "sec"
print "linename: ", get_time(linename), "sec"