Timing operations on an SQLite database.

sqlite3_timing.py

import timeit
import os

DB_PATH = 'database.db'
# Number of runs to generate
NRUNS = int(1e6)

insert_setup = """import sqlite3
con = sqlite3.connect('{0}')
con.execute('CREATE TABLE runs (run INTEGER PRIMARY KEY)')
""".format(DB_PATH)
# Explicit insertation loop
insert_stmt = "for i in range(int({0})): con.execute('INSERT INTO runs (run) VALUES (?)', (i,))".format(NRUNS)
# Implicit insertation loop
insert_bulk_stmt = "runs = range(int({0})); con.executemany('INSERT INTO runs (run) VALUES (?)', [(i,) for i in runs])".format(NRUNS)

insert_time = timeit.timeit(insert_stmt, insert_setup, number=1)
os.remove(DB_PATH)
insert_bulk_time = timeit.timeit(insert_stmt, insert_setup, number=1)
os.remove(DB_PATH)

delete_setup = """import sqlite3
con = sqlite3.connect('{0}')
con.execute('CREATE TABLE runs (run INTEGER PRIMARY KEY)')
for i in range(int({1})):
    con.execute('INSERT INTO runs (run) VALUES (?)', (i,))
""".format(DB_PATH, NRUNS)
# Explicit deletion loop
delete_stmt = "for i in range(int({0})): con.execute('DELETE FROM runs WHERE run=?', (i,))".format(NRUNS)
# Implicit deletion loop
delete_bulk_stmt = "runs = range(int({0})); con.executemany('DELETE FROM runs WHERE run=?', [(i,) for i in runs])".format(NRUNS)

delete_time = timeit.timeit(delete_stmt, delete_setup, number=1)
os.remove(DB_PATH)
delete_bulk_time = timeit.timeit(delete_bulk_stmt, delete_setup, number=1)
os.remove(DB_PATH)

sorting_setup = delete_setup
sorting_stmt = "con.execute('SELECT run FROM runs ORDER BY run ASC')"

sorting_time = timeit.timeit(sorting_stmt, sorting_setup, number=int(1e3))
os.remove(DB_PATH)

print 'Timing SQLite with {0} runs'.format(NRUNS)
print '-'*40
print 'One-by-one insert:', insert_time
print 'Bulk insert:      ', insert_bulk_time
print 'One-by-one delete:', delete_time
print 'Bulk delete:      ', delete_bulk_time
print 'Sorted list:      ', sorting_time

Two sample outputs:

Timing SQLite with 1000000 runs
----------------------------------------
One-by-one insert: 4.18599510193
Bulk insert:       4.03804993629
One-by-one delete: 3.90134692192
Bulk delete:       1.98414802551
Sorted list:       0.00406813621521

Timing SQLite with 1000000 runs
----------------------------------------
One-by-one insert: 3.55196499825
Bulk insert:       3.47870182991
One-by-one delete: 3.48303890228
Bulk delete:       1.84075498581
Sorted list:       0.00348496437073