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))