Skip to content

Instantly share code, notes, and snippets.

@kmuthukk
Last active August 11, 2019 03:43
Show Gist options
  • Save kmuthukk/5c8695e9f8ba376f8e3b1c6f52e6c5eb to your computer and use it in GitHub Desktop.
Save kmuthukk/5c8695e9f8ba376f8e3b1c6f52e6c5eb to your computer and use it in GitHub Desktop.
Simple INSERTs into a user_actions table (without use of prepare step)
# 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))
print("==================")
num_users=200
num_msgs=50
print "Inserting %d rows without use of prepare/bind" % (num_users*num_msgs)
start_time = time.time()
for idx in range(num_users):
for jdx in range(num_msgs):
cur.execute("""
INSERT INTO user_actions(id, msg_id, msg) VALUES (%d, %d, '%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