DB2批量插入性能对比

 

import ibm_db
import random
import time
first_names = '赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏' \
              '陶姜戚谢邹喻柏水窦章云苏潘葛奚范彭郎鲁韦昌马苗凤花方俞任袁柳酆鲍' \
              '史唐费廉岑薛雷贺倪汤滕殷罗毕安常乐于时傅皮卞齐康伍余元顾孟黄穆萧尹' \
              '姚邵湛汪祁毛狄米贝明臧计伏成戴谈宋庞熊纪舒屈项祝董梁杜阮蓝闵季贾路娄' \
              '危江童颜郭梅林刁钟徐邱骆高夏蔡田樊胡凌霍虞万柯管卢莫房缪干解丁宣邓郁单' \
              '洪包诸左石崔龚程嵇邢裴陆翁羊惠甄曲封储靳段巫乌焦山谷车侯宓全郗班秋仲伊宫' \
              '宁仇栾甘厉祖武符刘景詹龙叶幸司韶郜黎蓟薄印宿白蒲邰赖卓蔺屠蒙池乔胥闻党翟' \
              '谭劳姬申冉雍桑桂濮牛边农温庄晏柴瞿阎连习艾向古廖'
def GBK2312():
    head = random.randint(0xb0, 0xf7)
    body = random.randint(0xa1, 0xf9)   # 在head区号为55的那一块最后5个汉字是乱码,为了方便缩减下范围
    val = f'{head:x}{body:x}'
    str = bytes.fromhex(val).decode('gb2312')
    return str
# 获取随机姓名
def get_random_name():
    first_name = random.choice(first_names)
    if random.randrange(2) == 0:
        return first_name + GBK2312()
    else:
        return first_name + GBK2312() + GBK2312()


# 方案一：拼接SQL批量插入
def first():
    conn = ibm_db.connect("database=POBC; "
                          "hostname=localhost; "
                          "port=50000; "
                          "protocol=tcpip; "
                          "uid=administrator; "
                          "pwd=wyz","","")
    start_time = time.time()
    sql_array = []
    sql_array.append("INSERT INTO info VALUES(1,'张三','男')")
    sexes = ["男", "女"]
    for i in range(80000):
        name = get_random_name()
        sex = random.choice(sexes)
        str = ",({0},'{1}','{2}')".format(i,name,sex)
        sql_array.append(str)
    sql = "".join(sql_array)
    print("拼接完成。。")
    # print(sql)

    stmt = ibm_db.exec_immediate(conn,sql)
    print(ibm_db.num_rows(stmt))
    print(time.time() - start_time)
    '''
    拼接完成。。
    80001
    32.71020555496216
    '''
# 方案二，放入事务，批量提交
def second():
    array = {ibm_db.SQL_ATTR_AUTOCOMMIT: ibm_db.SQL_AUTOCOMMIT_OFF}
    conn = ibm_db.pconnect("database=POBC; "
                          "hostname=localhost; "
                          "port=50000; "
                          "protocol=tcpip; "
                          "uid=administrator; "
                          "pwd=wyz","","",array)
    sexes = ["男", "女"]
    start_time = time.time()
    try:
        for i in range(80000):
            name = get_random_name()
            sex = random.choice(sexes)
            sql = "INSERT INTO info VALUES({0},'{1}','{2}')".format(i, name, sex)
            ibm_db.exec_immediate(conn, sql)
    except:
        print("Transaction couldn't be completed.")
        ibm_db.rollback(conn)
    else:
        ibm_db.commit(conn)
        print("Transaction complete.")
        print("COST:", time.time() - start_time)
    '''
    Transaction complete.
    COST: 232.1929647922516
    '''
# 方案三，常规做法
def third():
    conn = ibm_db.pconnect("database=POBC; "
                          "hostname=localhost; "
                          "port=50000; "
                          "protocol=tcpip; "
                          "uid=administrator; "
                          "pwd=wyz", "", "")
    sexes = ["男", "女"]
    start_time = time.time()
    for i in range(80000):
        name = get_random_name()
        sex = random.choice(sexes)
        sql = "INSERT INTO info VALUES({0},'{1}','{2}')".format(i, name, sex)
        ibm_db.exec_immediate(conn, sql)
    print("Transaction complete.")
    print("COST:", time.time() - start_time)
    '''
    Transaction complete.
    COST: 159.8599555492401
    '''
if __name__ == '__main__':
    # first()
    # second()
    third()