ZODB vs Relational Database: a simple benchmark

Recently, I posted about relational databases performances. In that experiment, I found SQLite3, a database that comes in the Python standard distribution, to be the second fastest database backend available for multiple inserts.

Since this blog is about Python, I soon felt bad about not including ZODB in that comparison. At the time I justified that omission, by saying to myself that ZODB cannot be compared to standard DBs because it is an object database. Subconsciously, I thought ZOBD would loose so badly in a race against relational databases, that I feared for its reputation. Silly me.

The truth is: object databases such as ZODB, can be a perfect replacement for relational databases in a large portion (if not the majority) of database driven applications. Had I stopped to look more carefully at ZODB before, I would have saved countless hours of struggle with ORMS.

As you can see in the figure above, for up to a 100000 inserts per transaction, ZODB's performance is comparable to SQLite3 and since ZODB allows you to store arbitrarily complex objects, you don't have to cook up complex SQL queries to get at data you need, the relation between each datum is given by the design of the object you are storing. In some apps of mine, I have to write code to extract the the data from my Python objects, put them in table format (to store in a relational db), and then, when I read them back, I have to have more code to put them back where they belong. With ZODB, none of that is necessary.

ZODB stores your data in a file like SQLite, however it supports other storage types, see this table for a comparison of storage types.

ZODB is certainly one of the hidden jewels of Zope. Due to the lack of good documentation (an exception, though somewhat outdated), many Python programmers either don't known that ZODB can be used outside of Zope or don't know how to get started with it.

The goal of this post is not to serve as a tutorial of ZODB, since I am hardly an expert in the subject, but to spike the interest in adopting ZODB for mundane applications outside Zope.

Let get to the code:

import time, os, glob
import sqlite3
import ZODB
from   ZODB import FileStorage, DB
import pylab as P

def zInserts(n):
    print "Inserting %s records into ZODB"%n
    for i in xrange(n):
        dbroot[i] = {'name':'John Doe','sex':1,'age':35}
    connection.transaction_manager.commit()

def zInserts2(n):
    print "Inserting %s records into ZODB"%n
    dbroot['employees'] = [{'name':'John Doe','sex':1,'age':35} for i in xrange(n)]
    connection.transaction_manager.commit()


def testSqlite3Disk(n):
    print "Inserting %s records into SQLite(Disk) with sqlite3 module"%n
    conn = sqlite3.connect('dbsql')
    c = conn.cursor()
    # Create table
    c.execute('''create table Person(name text, sex integer, age integer)''')
    persons = [('john doe', 1, 35) for i in xrange(n)]
    c.executemany("insert into Person(name, sex, age) values (?,?,?)", persons)
    c.execute('select * from Person')
    print "Number of records selected: %s"%len(c.fetchall())
    c.execute('drop table Person')


recsize = [1000,5000,10000,50000,100000,200000,400000,600000,800000,1000000]
zperf = []
sqlperf =[]
for n in recsize:
    # remove old databases
    if os.path.exists('testdb.fs'):
        [os.remove(i) for i in glob.glob('testdb.fs*')]
    if os.path.exists('dbsql'):
        os.remove('dbsql')
    # setup ZODB storage
    dbpath = 'testdb.fs'
    storage     = FileStorage.FileStorage(dbpath)
    db          = DB(storage)
    connection  = db.open()
    dbroot      = connection.root()
    #begin tests
    t0 = time.clock()
    zInserts(n)
    t1 = time.clock()
    # closing and reopening ZODB' database to make sure
    # we are reading from file and not from some memory cache
    connection.close()
    db.close()
    storage  = FileStorage.FileStorage(dbpath)
    db  = DB(storage)
    connection  = db.open()
    dbroot = connection.root()
    t2 = time.clock()
    print "Number of records read from ZODB: %s"%len(dbroot.items())
    t3 = time.clock()
    ztime = (t1-t0)+(t3-t2)
    zperf.append(ztime)
    print 'Time for ZODB: %s seconds\n'%ztime
    t4 = time.clock()
    testSqlite3Disk(n)
    t5 = time.clock()
    stime = (t5-t4)
    sqlperf.append(stime)
    print 'Time for Sqlite3 with db on Disk: %s seconds\n'%stime
P.plot(recsize,zperf,'-v',recsize,sqlperf,'-^')
P.legend(['ZODB','SQLite3'])
P.xlabel('inserts')
P.ylabel('time(s)')
P.show()

As you can see in this very simple example, Using ZODB is no harder than using a dictionary, and it performs better than all ORMs I know! Below are the numeric results for the beginning of the plot above.

ZODB allows for a much more sophisticated usage than the one shown here. I chose to do it this way to make the insert operations on ZODB and SQLite as similar as possible. I hope the ZODB gurus out there will get together to write an up-to-date detailed tutorial on ZODB for Python programmers. ZODB deserves it. And so do we!