SQLAlchemy types benchmark

benchmark.py

from test import model
Session = model.Session

import time
from decorator import decorator

def measure(description):
    def wrapper(fn, *args, **kw):
        print "{0}...".format(description),

        start_fetch = time.time()
        r = fn(*args, **kw)
        if r:
            print "Fetched %d in %0.2f seconds" % (len(r), time.time() - start_fetch)
        else:
            print "Done in %0.2f seconds" % (time.time() - start_fetch)

        return r
    return decorator(wrapper)


def benchmark(description='', size=10000):
    sample = {'banana': 12345, 'cars': ['red', 'blue', 'green'], 'eyes': {'left': 1, 'right': 2}, 'stuff': [{'thing': 'blah', 'num': 1}, {'other_thing': 'blah', 'num': 2}]}

    if Session.bind.url.database != ':memory:':
        print "Not memory, aborting."
        return


    @measure("Populate data")
    def test(table):
        for i in xrange(size):
            Session.add(table(data=sample))
        Session.commit()

    @measure("Fetching data")
    def test2(table):
        for d in Session.query(table).all():
            d.data['stuff'][0]

    @measure("Starting benchmark")
    def benchmark_type(table):
        print table
        model.Base.metadata.drop_all(Session.bind)
        model.Base.metadata.create_all(Session.bind)
        test(table)
        test2(table)

    benchmark_type(model.TestPickle)
    benchmark_type(model.TestJSON)
    benchmark_type(model.TestCompressedJSON)

gistfile2.txt

Starting benchmark... <class 'test.model.TestPickle'>
Populate data... Done in 3.11 seconds
Fetching data... Done in 0.77 seconds
Done in 3.89 seconds
(Note: yielded a 2.0mb database)

Starting benchmark... <class 'test.model.TestJSON'>
Populate data... Done in 3.94 seconds
Fetching data... Done in 3.43 seconds
Done in 7.38 seconds
(Note: yielded a 1.6mb database)

Starting benchmark... <class 'test.model.TestCompressedJSON'>
Populate data... Done in 4.22 seconds
Fetching data... Done in 3.59 seconds
Done in 7.81 seconds
(Note: yielded a 1.2mb database)

mytypes.py

from sqlalchemy import types

import json
import zlib

class JSON(types.TypeDecorator):
    impl = types.String

    def process_bind_param(self, value, dialect):
        return value is not None and json.dumps(value)

    def process_result_value(self, value, dialect):
        return value is not None and json.loads(value)


class CompressedJSON(types.TypeDecorator):
    impl = types.LargeBinary

    def process_bind_param(self, value, dialect):
        if value is None:
            return

        return zlib.compress(json.dumps(value))

    def process_result_value(self, value, dialect):
        if value is None:
            return

        return json.loads(zlib.decompress(value))