Oracle入库速度测试(Python版)

测试环境：
    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插件，代码稍加改动就可以大幅提高速度，有空可以试试。