Postgresql Batch Insert 방식 성능비교

__author__ = ‘archmagece’

import pika import json

import sys sys.path.append(“.”) import w_parser.data_sender as data_sender

import wftp_daemon.w_parser.data_sender as data_sender

from wftp_daemon.config import *

from datetime import time

import time

createQuery = “"”CREATE TABLE weather.test1 ( col1 bigserial NOT NULL, col2 character varying(100), col3 integer, CONSTRAINT test1_pkey PRIMARY KEY (col1) )”””

test1 : 1by 1 autocommit on

t0 = time.time() sender = data_sender.PostgreSQLSender(tableName=”test1”, p_autocommit=True) for i in range(1, 10000): csvRaw = { “col2”: “coocococoococracter”, “col3”: 1 } sender.sendResultDict(csvRaw) sender.flush()

t1 = time.time() print “test1 : “, t1 - t0

test2 : 1by 1 autocommit off

t0 = time.time() sender = data_sender.PostgreSQLSender(tableName=”test1”, p_autocommit=False) for i in range(1, 10000): csvRaw = { “col2”: “coocococoococracter”, “col3”: 1 } sender.sendResultDict(csvRaw) sender.flush()

t1 = time.time() print “test2 : “, t1 - t0

test3 : exemany autocommit on

t0 = time.time() sender = data_sender.PostgreSQLSender(tableName=”test1”, p_autocommit=True) resultList = list() for i in range(1, 10000): csvRaw = { “col2”: “coocococoococracter”, “col3”: 1 } resultList.append(csvRaw) sender.exeMany(resultList) sender.flush()

t1 = time.time() print “test3 : “, t1 - t0

test4 : exemany autocommit off

t0 = time.time() sender = data_sender.PostgreSQLSender(tableName=”test1”, p_autocommit=False) resultList = list() for i in range(1, 10000): csvRaw = { “col2”: “coocococoococracter”, “col3”: 1 } resultList.append(csvRaw) sender.exeMany(resultList) sender.flush()

t1 = time.time() print “test4 : “, t1 - t0

test5 : string append batch query autocommit on

t0 = time.time() sender = data_sender.PostgreSQLSender(tableName=”test1”, p_autocommit=True)

query = “insert into weather.test1 (col2, col3) values “ values = list() for i in range(1, 10000): csvRaw = { “col2”: “coocococoococracter”, “col3”: 1 } values.append(“(‘%s’, %s)” % (csvRaw[“col2”], csvRaw[“col3”])) query += “,”.join(values)

print query

sender.exeQuery(query) sender.flush()

t1 = time.time() print “test5 : “, t1 - t0

test6 : string append batch query autocommit off

t0 = time.time() sender = data_sender.PostgreSQLSender(tableName=”test1”, p_autocommit=False)

query = “insert into weather.test1 (col2, col3) values “ values = list() for i in range(1, 10000): csvRaw = { “col2”: “coocococoococracter”, “col3”: 1 } values.append(“(‘%s’, %s)” % (csvRaw[“col2”], csvRaw[“col3”])) query += “,”.join(values)

print query

sender.exeQuery(query) sender.flush()

t1 = time.time() print “test6 : “, t1 - t0