def updateBenchPgsql(conn):
def insertFunc(cur, chunk):
cur.executemany(
"insert into departments (department_name, created) values (%s, CURRENT_TIMESTAMP)",
map((lambda i: ("dept%08d" %i,)), chunk)
)
def updateFunc(cur, chunk):
cur.executemany(
"update departments set department_name = %s where department_name = %s",
map((lambda i: ("deptUpdated%08d" %i, "dept%08d" %i)), chunk)
)
def performInsert():
updateDepartmentBench(
conn, (lambda cur: None), lambda cur: conn.commit(), insertFunc
)
def performUpdate():
updateDepartmentBench(
conn, (lambda cur: None), lambda cur: conn.commit(), updateFunc
)
bench.createTablePgsql(conn)
bench.withStopwatch("insert departments with Postgres", performInsert)
bench.withStopwatch("update departments with Postgres", performUpdate)
def updateBenchSqlite(conn):
def insertFunc(cur, chunk):
cur.executemany(
"insert into departments (department_name, created) values (?, CURRENT_TIMESTAMP)",
map((lambda i: ("dept%08d" %i,)), chunk)
)
def updateFunc(cur, chunk):
cur.executemany(
"update departments set department_name = ? where department_name = ?",
map((lambda i: ("deptUpdated%08d" %i, "dept%08d" %i)), chunk)
)
def performInsert():
updateDepartmentBench(
conn, lambda cur: cur.execute('BEGIN TRANSACTION'), lambda cur: cur.execute('COMMIT'), insertFunc
)
def performUpdate():
updateDepartmentBench(
conn, lambda cur: cur.execute('BEGIN TRANSACTION'), lambda cur: cur.execute('COMMIT'), updateFunc
)
bench.createTableSqlite(conn)
bench.withStopwatch("insert departments with SQLite", performInsert)
bench.withStopwatch("update departments with SQLite", performUpdate)
def queryUserPgsql(conn, doNtimes):
cur = conn.cursor()
def performer(i):
cur.execute(
"select * from users order by user_name limit 1 offset random()")
cur.fetchall()
bench.withStopwatch("Postgres query user", lambda: doNtimes(performer))
def queryAddressPgsql(conn, doNtimes):
cur = conn.cursor()
def performer(i):
cur.execute(
"select * from addresses order by address limit 1 offset random()")
cur.fetchall()
bench.withStopwatch("Postgres query address", lambda: doNtimes(performer))
def insertAddressSqlite(conn, doNtimes):
def insertFunc(cur, i):
cur.execute("insert into addresses (address) values (?)",
("addr%08d" % i, ))
bench.withStopwatch(
"insert addresses with SQLite",
lambda: doNtimes(lambda i: doInTransactionSqlite(
conn, lambda cur: insertFunc(cur, i))))
def insertAddressPgsql(conn, n):
def insertFunc(cur, i):
cur.execute("insert into addresses (address) values (%s)",
("addr%08d" % i, ))
def performer(i):
doInTransactionPgsql(conn, lambda cur: insertFunc(cur, i))
bench.withStopwatch("insert addresses with Postgres",
lambda: doNtimes(performer))
def queryDepartmentSqlite(conn, doNtimes):
cur = conn.cursor()
def performer(i):
cur.execute(
"select max(created) from departments where created < datetime(CURRENT_TIMESTAMP, '-10 seconds')"
)
cur.fetchall()
bench.withStopwatch("SQLite query department", lambda: doNtimes(performer))
def insertDepartmentSqlite(conn, doNtimes):
def insertFunc(cur, i):
cur.execute(
"insert into departments (department_name, created) values (?, CURRENT_TIMESTAMP)",
("dept%08d" % i, ))
bench.withStopwatch(
"insert departments with SQLite",
lambda: doNtimes(lambda i: doInTransactionSqlite(
conn, lambda cur: insertFunc(cur, i))))
def insertBenchPgsql(conn):
def insertFunc(cur, chunk):
cur.executemany(
"insert into departments (department_name, created) values (%s, CURRENT_TIMESTAMP)",
map((lambda i: ["dept%08d" % i]), chunk))
def performBench():
insertDepartmentBench(conn, (lambda cur: None),
lambda cur: conn.commit(), insertFunc)
bench.withStopwatch("insert departments with Postgres", performBench)
def queryDepartmentPgsql(conn, doNtimes):
cur = conn.cursor()
def performer(i):
cur.execute(
"select max(created) from departments where created < CURRENT_TIMESTAMP + '-10 seconds'"
)
cur.fetchall()
bench.withStopwatch("Postgres query department",
lambda: doNtimes(performer))
def queryUserDepartmentSqlite(conn, doNtimes):
cur = conn.cursor()
def performer(i):
cur.execute(
"select * from user_department order by user_id limit 1 offset random()"
)
cur.fetchall()
bench.withStopwatch("SQLite query user department",
lambda: doNtimes(performer))
def insertDepartmentPgsql(conn, doNtimes):
def insertFunc(cur, i):
cur.execute(
"insert into departments (department_name, created) values (%s, CURRENT_TIMESTAMP)",
("dept%08d" % i, ))
def performer(i):
doInTransactionPgsql(conn, lambda cur: insertFunc(cur, i))
bench.withStopwatch("insert departments with Postgres",
lambda: doNtimes(performer))
def insertUserDepartmentPgsql(conn, doNtimes):
def insertFunc(cur, i, keys):
cur.execute(
"insert into user_department(user_id, department_id) values (%s, %s)",
(keys[0], keys[1]))
def performer(i):
keys = getKeysPgsql(conn, userDeptPicker)
doInTransactionSqlite(conn, lambda cur: insertFunc(cur, i, keys))
bench.withStopwatch("insert user_department with Postgres",
lambda: doNtimes(performer))
def insertUserSqlite(conn, doNtimes):
def insertFunc(cur, i, keys):
cur.execute(
"insert into users(address_id, user_name, first_name, last_name, created) "
+ "values (?, ?, ?, ?, CURRENT_TIMESTAMP)",
(keys[0], "user%08d" % i, "fname%08d" % i, "lname%08d" % i))
def performer(i):
keys = getKeysSqlite(conn, addrPicker)
doInTransactionSqlite(conn, lambda cur: insertFunc(cur, i, keys))
bench.withStopwatch("insert users with SQLite",
lambda: doNtimes(performer))
def insertBenchSqlite(conn):
def insertFunc(cur, chunk):
cur.executemany(
"insert into departments (department_name, created) values (?, CURRENT_TIMESTAMP)",
map((lambda i: ("dept%08d" % i, )), chunk))
def performBench():
insertDepartmentBench(conn,
lambda cur: cur.execute('BEGIN TRANSACTION'),
lambda cur: cur.execute('COMMIT'), insertFunc)
bench.createTableSqlite(conn)
bench.withStopwatch("insert departments with SQLite", performBench)
def copyInsertBenchPgsql(conn):
def insertFunc(cur, chunk):
cur.copy_from(io.StringIO(''.join(
map((lambda i: "dept%08d\t%s\n" % (i, datetime.datetime.now())),
chunk))),
'departments',
columns=('department_name', 'created'))
def performBench():
insertDepartmentBench(conn, (lambda cur: None),
lambda cur: conn.commit(), insertFunc)
bench.withStopwatch("insert departments with Postgres using COPY",
performBench)
def selectBenchPgsql(conn):
def insertFunc(cur, chunk):
cur.executemany(
"insert into departments (department_name, created) values (%s, CURRENT_TIMESTAMP)",
map((lambda i: ("dept%08d" % i, )), chunk))
def performInsert():
updateDepartmentBench(conn, (lambda cur: None),
lambda cur: conn.commit(), insertFunc)
def performSelect():
cur = conn.cursor()
cur.execute("prepare myquery as "
"select * from departments order by created desc limit 5")
for i in range(1, 50000):
cur.execute("execute myquery")
cur.fetchall()
bench.createTablePgsql(conn)
bench.withStopwatch("insert departments with Postgres", performInsert)
bench.withStopwatch("select departments with Postgres", performSelect)
def selectBenchSqlite(conn):
def insertAddress(cur):
cur.executemany(
"insert into addresses (address) values (?)",
map((lambda x: (x,)), address)
)
def insertFunc(cur, chunk):
for i in chunk:
cur.execute("select address_id from addresses where address = ?", [address[i % len(address)]])
aid = cur.fetchone()[0]
cur.execute(
"""
insert into users (address_id, user_name, first_name, last_name, created)
values (?, ?, ?, ?, CURRENT_TIMESTAMP)
""",
[aid, "user%08d" %i, "first%08d" %i, "last%08d" %i]
)
def performInsert():
cur = conn.cursor()
insertAddress(cur)
bulkUpdate(
conn,
lambda cur: cur.execute('BEGIN TRANSACTION'), lambda cur: cur.execute('COMMIT'), insertFunc
)
def performSelect():
cur = conn.cursor()
# Seems no prepared statement support for sqlite...
for i in range(1, 500):
cur.execute(
"select count(*) from users u inner join addresses a on u.address_id = a.address_id where address = ?",
(address[i % len(address)],)
)
cur.fetchall()
bench.createTableSqlite(conn)
bench.withStopwatch("insert departments with SQLite", performInsert)
bench.withStopwatch("select departments with SQLite", performSelect)
def selectBenchSqlite(conn):
def insertFunc(cur, chunk):
cur.executemany(
"insert into departments (department_name, created) values (?, CURRENT_TIMESTAMP)",
map((lambda i: ("dept%08d" % i, )), chunk))
def performInsert():
updateDepartmentBench(conn,
lambda cur: cur.execute('BEGIN TRANSACTION'),
lambda cur: cur.execute('COMMIT'), insertFunc)
def performSelect():
cur = conn.cursor()
# Seems no prepared statement support for sqlite...
for i in range(1, 50000):
cur.execute(
"select * from departments order by created desc limit 5")
cur.fetchall()
bench.createTableSqlite(conn)
bench.withStopwatch("insert departments with SQLite", performInsert)
bench.withStopwatch("select departments with SQLite", performSelect)
def selectBenchPgsql(conn):
def insertAddress(cur):
cur.executemany(
"insert into addresses (address) values (%s)",
map((lambda x: (x,)), address)
)
def insertFunc(cur, chunk):
for i in chunk:
cur.execute("select address_id from addresses where address = %s", [address[i % len(address)]])
aid = cur.fetchone()[0]
cur.execute(
"""
insert into users (address_id, user_name, first_name, last_name, created)
values (%s, %s, %s, %s, CURRENT_TIMESTAMP)
""",
[aid, "user%08d" %i, "first%08d" %i, "last%08d" %i]
)
def performInsert():
cur = conn.cursor()
insertAddress(cur)
bulkUpdate(
conn, (lambda cur: None), lambda cur: conn.commit(), insertFunc
)
def performSelect():
cur = conn.cursor()
cur.execute(
"prepare myquery as "
"select count(*) from users u inner join addresses a on u.address_id = a.address_id where address = $1"
)
for i in range(1, 500):
cur.execute("execute myquery (%s)", (address[i % len(address)],))
cur.fetchall()
bench.createTablePgsql(conn)
bench.withStopwatch("insert departments with Postgres", performInsert)
bench.withStopwatch("select departments with Postgres", performSelect)
def selectBenchSqlite(conn):
def insertAddress(cur):
cur.executemany(
"insert into addresses (address) values (?)",
map((lambda x: (x,)), address)
)
def insertDepartment(cur):
cur.executemany(
"insert into departments (department_name, created) values (?, CURRENT_TIMESTAMP)",
map((lambda x: (x,)), department)
)
def insertFunc(cur, chunk):
for i in chunk:
cur.execute("select address_id from addresses where address = ?", [address[i % len(address)]])
aid = cur.fetchone()[0]
cur.execute(
"""
insert into users (address_id, user_name, first_name, last_name, created)
values (?, ?, ?, ?, CURRENT_TIMESTAMP)
""",
[aid, "user%08d" %i, "first%08d" %i, "last%08d" %i]
)
def insertFunc2(cur, chunk):
for i in chunk:
if (i % 33) != 0:
cur.execute(
"select department_id from departments where department_name = ?",
[department[i % len(department)]]
)
did = cur.fetchone()[0]
cur.execute(
"select user_id from users where user_name = ?",
["user%08d" %i]
)
uid = cur.fetchone()[0]
cur.execute(
"""
insert into user_department (user_id, department_id)
values (?, ?)
""",
[uid, did]
)
def performInsert():
cur = conn.cursor()
insertAddress(cur)
insertDepartment(cur)
bulkUpdate(
conn,
lambda cur: cur.execute('BEGIN TRANSACTION'), lambda cur: cur.execute('COMMIT'), insertFunc
)
bulkUpdate(
conn,
lambda cur: cur.execute('BEGIN TRANSACTION'), lambda cur: cur.execute('COMMIT'), insertFunc2
)
def performSelect():
cur = conn.cursor()
for i in range(1, 500):
cur.execute(
"""
select count(u.user_id) from users u
inner join addresses a on u.address_id = a.address_id
left join user_department ud on u.user_id = ud.user_id
inner join departments d on ud.department_id = d.department_id
where (d.department_name = 'Sales1' or d.department_name is null) and address = 'Tokyo'
"""
)
cur.fetchone()
bench.createTableSqlite(conn)
bench.withStopwatch("insert departments with SQLite", performInsert)
bench.withStopwatch("select departments with SQLite", performSelect)
lambda: doWithThreadPgsql(lambda conn: queryUserDepartmentPgsql(
conn, doNtimes)))
def pgsqlUpdateBench(args, doNtimes):
doWithThreadPgsql(bench.createTablePgsql).join()
doInThreads(
lambda: doWithThreadPgsql(lambda conn: insertDepartmentPgsql(
conn, doNtimes)),
lambda: doWithThreadPgsql(lambda conn: insertAddressPgsql(
conn, doNtimes)), lambda: doWithThreadPgsql(
lambda conn: insertUserPgsql(conn, doNtimes)),
lambda: doWithThreadPgsql(lambda conn: insertUserDepartmentPgsql(
conn, doNtimes)))
if __name__ == '__main__':
args = docopt(__doc__)
doNtimes = lambda func: loop(func, 300)
bench.withStopwatch("SQLite update all",
lambda: sqliteUpdateBench(args, doNtimes))
bench.withStopwatch("Postgres update all",
lambda: pgsqlUpdateBench(args, doNtimes))
doNtimes = lambda func: loop(func, 50000)
bench.withStopwatch("SQLite query all",
lambda: sqliteQueryBench(args, doNtimes))
bench.withStopwatch("Postgres query all",
lambda: pgsqlQueryBench(args, doNtimes))
