oracle入库的速度能到多少_Oracle入库速度测试(Python版)

CREATE TABLE TEST

(

TEST_ID NUMBER(10, 0),

TEST_NAME VARCHAR2(50),

TEST_TIME TIMESTAMP,

TEST_VALUE NUMBER(10, 3)

);

连接Oracle过程略。

几个测试用的方法：

import random

atoz = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ"

def genString(length):

temp = []

for k in range(length):

temp.append(atoz[random.randint(0, 51)])

return "".join(temp)

def genDouble():

d1 = 2500 * random.random()

d2 = 500000 * random.random()

return d1 + d2

def split2(total):

splitSize = 51200

result = []

length = (total + splitSize - 1) / splitSize

for i in range(length-1):

total = total - splitSize

result.append(splitSize)

result.append(total)

return result

拼sql入库：

def simpleInsert(total):

time.sleep(3)

currentStr = time.strftime("%Y-%m-%d %H:%M:%S")

connOra = cx_Oracle.connect(connStr)

begin = time.time()

cursorOra = connOra.cursor()

for i in range(1, total+1):

sqlStr = "INSERT INTO TEST (TEST_ID, TEST_NAME, TEST_TIME, TEST_VALUE) VALUES (%s, '%s', to_date('%s','yyyy-MM-dd HH24:mi:ss'), %s)"%(i, genString(5), currentStr, genDouble())

cursorOra.execute(sqlStr)

connOra.commit()

end = time.time()

print "Count:%s Time:%s"%(total, end - begin)

cursorOra.close()

connOra.close()

测试结果：

Count:20480 Time:34.7809998989

Count:20480 Time:34.8599998951

Count:20480 Time:35.6400001049

Count:20480 Time:35.375

Count:20480 Time:35.9060001373

Count:20480 Time:34.9690001011

Count:20480 Time:35.7179999352

Count:20480 Time:35.1879999638

Count:20480 Time:36.4690001011

Count:20480 Time:35.1870000362

总结：

平均入库速度每1万条17.29秒

绑定参数法入库：

def traditionalInsert(total):

time.sleep(3)

cst = time.localtime()

current = datetime.datetime(cst[0], cst[1], cst[2], cst[3], cst[4], cst[5])

connOra = cx_Oracle.connect(connStr)

begin = time.time()

cursorOra = connOra.cursor()

cursorOra.prepare("INSERT INTO TEST (TEST_ID, TEST_NAME, TEST_TIME, TEST_VALUE) VALUES (:1, :2, :3, :4)")

for i in range(1, total+1):

cursorOra.execute(None, (i, genString(5), current, genDouble()))

connOra.commit()

end = time.time()

print "Count:%s Time:%s"%(total, end - begin)

cursorOra.close()

connOra.close()

测试结果：

Count:51200 Time:28.2030000687

Count:51200 Time:28.5160000324

Count:51200 Time:26.7349998951

Count:51200 Time:27.0309998989

Count:51200 Time:27.3910000324

Count:51200 Time:35.4370000362

Count:51200 Time:26.9070000648

Count:51200 Time:29.375

Count:51200 Time:27.7350001335

Count:51200 Time:27.5309998989

总结：

平均入库速度每1万条5.56秒

数组绑定法入库：

def arrayBindInsert(total):

time.sleep(3)

cst = time.localtime()

current = datetime.datetime(cst[0], cst[1], cst[2], cst[3], cst[4], cst[5])

splitTotal = split2(total)

connOra = cx_Oracle.connect(connStr)

begin = time.time()

cursorOra = connOra.cursor()

cursorOra.prepare("INSERT INTO TEST (TEST_ID, TEST_NAME, TEST_TIME, TEST_VALUE) VALUES (:1, :2, :3, :4)")

for smallCount in splitTotal:

tempitems = []

for i in range(1, smallCount+1):

tempitems.append((i, genString(5), current, genDouble()))

cursorOra.executemany(None, tempitems)

connOra.commit()

end = time.time()

print "Count:%s Time:%s"%(total, end - begin)

cursorOra.close()

connOra.close()

测试结果：

Count:1024000 Time:36.5779998302

Count:1024000 Time:35.375

Count:1024000 Time:37.6559998989

Count:1024000 Time:34.9060001373

Count:1024000 Time:36.5779998302

Count:1024000 Time:37.625

Count:1024000 Time:35.2809998989

Count:1024000 Time:36.1570000648

Count:1024000 Time:36.4219999313

Count:1024000 Time:37.1879999638

总结：

平均入库速度每100万条35.52秒

数组绑定法的注意事项：数组长度最好要小于65535。

python中有一个timeit专门用来测试代码执行时间的，有空可以用一用。

听说有个cython插件，代码稍加改动就可以大幅提高速度，有空可以试试。

分享到：

2008-08-26 19:09

浏览 2682

论坛回复 / 浏览 (0 / 2100)

评论