Oracle入库速度测试(Python版)

upcseodvjd

测试环境：
    Intel Xeon 2.4G四核心 2.5G内存
    Server 2003 Enterprise Edition Service Pack 2
    Oracle9i Enterprise Edition 9.2.0.1.0
    python-2.5.2
    组件cx_Oracle-4.3.1-win32-9i-py25
建立测试表：
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插件，代码稍加改动就可以大幅提高速度，有空可以试试。