SQLite vs Redis ソーティング速度比較

get_ranking_data.py

import sqlite3
import redis
import sys
from timeit import Timer

loop = int(sys.argv[1])

def ranking_sqlite(conn, key):
    cur = conn.cursor()
    cur.execute("SELECT val FROM ranking WHERE key = '%s' ORDER BY score DESC limit 10" % key)
    vals = [row[0] for row in cur]
    cur.close()
    #print vals

def ranking_redis(r, key):
    vals = [val for val in r.zrevrange(key, 0, 9)]
    # print vals

    
setup1 = """
import random
from __main__ import ranking_sqlite, conn
"""
stmt1 = """
key = '%08d' % random.randint(0, 100000)
ranking_sqlite(conn, key)
"""
setup2 = """
import random
from __main__ import ranking_redis, r
"""
stmt2 = """
key = '%08d' % random.randint(0, 100000)
ranking_redis(r, key)
"""

loop = int(sys.argv[1])

conn = sqlite3.connect('data.db')
t = Timer(stmt1, setup1)
print 'SQLite (loop=%d)' % loop
print t.timeit(loop)
conn.close()

r = redis.Redis(host='localhost', port=6379)
t = Timer(stmt2, setup2)
print 'Redis (loop=%d)' % loop
print t.timeit(loop)

make_data_redis.py

import redis
import uuid
import random

r = redis.Redis(host='localhost', port=6379)
for i in range(100000):
    key = '%08d' % i
    for j in range(random.randint(1, 50)):
        val = str(uuid.uuid4())
        score = random.randint(1, 100)
        r.zadd(key, val, score)

make_data_sqlite.py

import uuid
import random

conn = sqlite3.connect('data.db')
cur = conn.cursor()
for i in range(100000):
    key = '%08d' % i
    for j in range(random.randint(1, 50)):
        val = str(uuid.uuid4())
        score = random.randint(1, 100)
        cur.execute('INSERT INTO ranking VALUES (?,?,?)', (key, val, score))
    conn.commit()
cur.close()
conn.close()