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 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 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 selectBenchPgsql(conn): def insertAddress(cur): cur.executemany( "insert into addresses (address) values (%s)", map((lambda x: (x,)), address) ) def insertDepartment(cur): cur.executemany( "insert into departments (department_name, created) values (%s, CURRENT_TIMESTAMP)", map((lambda x: (x,)), department) ) 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 insertFunc2(cur, chunk): for i in chunk: if (i % 33) != 0: cur.execute( "select department_id from departments where department_name = %s", [department[i % len(department)]] ) did = cur.fetchone()[0] cur.execute( "select user_id from users where user_name = %s", ["user%08d" %i] ) uid = cur.fetchone()[0] cur.execute( """ insert into user_department (user_id, department_id) values (%s, %s) """, [uid, did] ) def performInsert(): cur = conn.cursor() insertAddress(cur) insertDepartment(cur) bulkUpdate( conn, (lambda cur: None), lambda cur: conn.commit(), insertFunc ) bulkUpdate( conn, (lambda cur: None), lambda cur: conn.commit(), insertFunc2 ) def performSelect(): cur = conn.cursor() cur.execute( "prepare myquery as " """ 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' """ ) for i in range(1, 500): cur.execute("execute myquery") cur.fetchone() bench.createTablePgsql(conn) bench.withStopwatch("insert departments with Postgres", performInsert) bench.withStopwatch("select departments with Postgres", performSelect)