Skip to content

Instantly share code, notes, and snippets.

@kmuthukk
Last active August 11, 2019 03:42
Show Gist options
  • Save kmuthukk/d245a0f48fa796aa4a72bb54f94aec57 to your computer and use it in GitHub Desktop.
Save kmuthukk/d245a0f48fa796aa4a72bb54f94aec57 to your computer and use it in GitHub Desktop.
Simple INSERTs into a user_actions table (with use of prepare)
# On CentOS you can install psycopg2 thus:
#
# sudo yum install postgresql-libs
# sudo yum install python-psycopg2
import psycopg2;
import time
conn = psycopg2.connect("host=localhost dbname=postgres user=postgres port=5433")
conn.set_session(autocommit=True)
cur = conn.cursor()
start_time = time.time()
cur.execute("""DROP TABLE IF EXISTS user_actions""");
now_time = time.time()
print "Dropped (if exists): user_actions table"
print("Time: %s ms ---" % ((now_time - start_time) * 1000))
start_time = time.time()
cur.execute("""
CREATE TABLE user_actions(
id integer,
msg_id integer,
msg text,
PRIMARY KEY(id, msg_id)
)
""")
now_time = time.time()
print "Create user_actions table."
print("Time: %s ms ---" % ((now_time - start_time) * 1000))
num_users=200
num_msgs=50
print "Inserting %d rows with prepared statement" % (num_users*num_msgs)
start_time = time.time()
cur.execute("""PREPARE insert_stmt (int, int, text) AS
INSERT INTO user_actions VALUES ($1, $2, $3)""")
for idx in range(num_users):
for jdx in range(num_msgs):
cur.execute("""EXECUTE insert_stmt(%s, %s, %s)""",
(idx, jdx, "msg--"+str(idx)+"--"+str(jdx)));
now_time = time.time()
print("Inserted %d rows" % (num_msgs * num_users))
print("Time: %s ms ---" % ((now_time - start_time) * 1000))
print("Avg Time: %s ms ---" % ((now_time - start_time) * 1000 / (num_msgs * num_users)))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment