oracle proc 插入操作性能优化实践

场景：

student 表中 10万条数据。
从 student 表中取出所有数据，插入到 student_his 表中

优化方法：

1.批量插入（效果明显）
2.批量查询（效果不明显）
3.批量提交（效果不明显）
4.预编译 sql 语句（效果不明显）

效果：

10万条数据，普通方法 27 秒，优化后 8 秒

测试步骤：

1.创建数据库表

create table student
(
student_id integer,
student_name varchar2(20),
salary float(126)
);
create unique index index_student_id on student(student_id);
insert into student (student_id, student_name, salary) values (1, 'test', 2);


create table student_his
(
student_id integer,
student_name varchar2(20),
salary float(126)
);
create unique index index_student_his_id on student_his(student_id);

2.准备数据：

insert.pc 插入 10万条数据

vim insert.pc

#include <stdio.h>
#include <string.h>

/*插入条数*/
#define COUNTNUM 100000
/*数组大小*/
#define RECORDNUM 1000

EXEC SQL INCLUDE SQLCA;

void sqlerror();

typedef struct Record
{
    int student_id;
    char student_name[20];
    float salary;
}Record;

int main()
{
    EXEC SQL BEGIN DECLARE SECTION;
    char username[20];
    char password[20];
    char db_name[20];
    int student_id;
    char student_name[15];
    float salary;
    Record records[RECORDNUM];
    
    EXEC SQL END DECLARE SECTION;
    
    printf("size:%d
",sizeof(records));
    
    int j = 0;
    int i = 0;
    int count = 1; 
    
    /*出错处理*/
    EXEC SQL WHENEVER SQLERROR DO sqlerror();
    EXEC SQL WHENEVER NOT FOUND GOTO notfound;
    
    /*链接数据库*/
    strcpy(username,"ngbs");
    strcpy(password,"ngbs");
    strcpy(db_name,"ngbs");
    EXEC SQL CONNECT :username IDENTIFIED BY :password USING :db_name;
    
    /*获取当前最大id号，防止插入重复*/
    EXEC SQL SELECT max(student_id) INTO :student_id FROM student;
    printf("max student_id:%d
", student_id);
    
    for(j=0; j<COUNTNUM/RECORDNUM; j++)
    {
        memset (records, 0, sizeof(records));
        for(i=0; i<RECORDNUM; i++)
        {
            records[i].student_id = student_id+count;
            sprintf(records[i].student_name, "%s%d", "stu", student_id+i);
            records[i].salary = student_id*i+0.5;
            //printf("student_id=%d, student_name=%s, salary=%f
", records[i].student_id, records[i].student_name, records[i].salary);
            count++;
        }
        /*使用数组方式一次插入多条纪录*/
        EXEC SQL INSERT INTO student(student_id,student_name,salary) values (:records);
    }

notfound:
    /*提交*/
    EXEC SQL COMMIT WORK RELEASE;
    printf("ok
");
    return 0;
}

void sqlerror()
{
    /*出错回滚*/
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    printf("ORACLE error detected:
");
    printf("%s
", sqlca.sqlerrm.sqlerrmc);
    EXEC SQL ROLLBACK;
}

编译运行脚本：

vim runin.sh

proc insert.pc
cc -g -o insert insert.c -L$ORACLE_HOME/lib -L$ORACLE_HOME/rdbms/lib -L/usr/lib64 -lclntsh -lm -lc
time ./insert

3.对比两种处理方法的快慢

common.pc 使用一般游标方式

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

EXEC SQL INCLUDE SQLCA;

void sqlerror();

typedef struct Record
{
    int student_id;
    char student_name[20];
    float salary;
}Record;

int main()
{
    EXEC SQL BEGIN DECLARE SECTION;
    char username[20];
    char password[20];
    char db_name[20];
    int student_id;
    char student_name[15];
    float salary;
    char strsql[256];
    Record records;
    
    EXEC SQL END DECLARE SECTION;
    
    printf("records size:%d
",sizeof(records));
    
    memset(strsql, 0, sizeof(strsql));
    
    /*出错处理*/
    EXEC SQL WHENEVER SQLERROR DO sqlerror();
    EXEC SQL WHENEVER NOT FOUND GOTO notfound;
    
    /*链接数据库*/
    strcpy(username,"ngbs");
    strcpy(password,"ngbs");
    strcpy(db_name,"ngbs");
    EXEC SQL CONNECT :username IDENTIFIED BY :password USING :db_name;

    
    /*清空*/
    EXEC SQL TRUNCATE TABLE student_his;

    /*打开游标*/
    sprintf(strsql, "select * from student");
    printf("strsql:%s
", strsql);
    EXEC SQL PREPARE sql_stmt FROM :strsql;
    if(sqlca.sqlcode)
    {
        printf("sql_stmt error[%d]", sqlca.sqlcode);
        return sqlca.sqlcode;
    }
    EXEC SQL DECLARE sel_cursor cursor for sql_stmt;
    if(sqlca.sqlcode)
    {
        printf("cursor error[%d]", sqlca.sqlcode);
        return sqlca.sqlcode;
    }
    EXEC SQL OPEN sel_cursor;
    if(sqlca.sqlcode)
    {
        printf("open error[%d]", sqlca.sqlcode);
        return sqlca.sqlcode;
    }
    
    int flag = 1;
    while(flag)
    {
        /*取出数据 student*/
        EXEC SQL FETCH sel_cursor INTO :records;

        /*插入 student_his*/
        EXEC SQL INSERT INTO student_his values(:records); 
    }
notfound:
    /*提交*/
    EXEC SQL COMMIT WORK RELEASE;
    printf("ok
");
    return 0;
}

void sqlerror()
{
    /*出错回滚*/
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    printf("ORACLE error detected:
");
    printf("%s
", sqlca.sqlerrm.sqlerrmc);
    EXEC SQL ROLLBACK;
    exit(1);
}

编译运行脚本：

vim runco.sh

proc common.pc
cc -g -o common common.c -L$ORACLE_HOME/lib -L$ORACLE_HOME/rdbms/lib -L/usr/lib64 -lclntsh -lm -lc
time ./common

fast.pc 使用游标+结构体数组方式

#include <stdio.h>
#include <stdlib.h>
#include <string.h>

/*数组大小*/
#define RECORDNUM 1000

EXEC SQL INCLUDE SQLCA;

void sqlerror();

typedef struct Record
{
    int student_id;
    char student_name[20];
    float salary;
}Record;

int main()
{
    EXEC SQL BEGIN DECLARE SECTION;
    char username[20];
    char password[20];
    char db_name[20];
    int student_id;
    char student_name[15];
    float salary;
    char strsql[256];
    Record records[RECORDNUM];
    
    EXEC SQL END DECLARE SECTION;
    
    printf("records size:%d
",sizeof(records));
    
    memset(strsql, 0, sizeof(strsql));
    memset(records, 0, sizeof(records));
    
    /*出错处理*/
    EXEC SQL WHENEVER SQLERROR DO sqlerror();
    EXEC SQL WHENEVER NOT FOUND GOTO notfound;
    EXEC ORACLE OPTION (HOLD_CURSOR=YES);
    
    /*链接数据库*/
    strcpy(username,"ngbs");
    strcpy(password,"ngbs");
    strcpy(db_name,"ngbs");
    EXEC SQL CONNECT :username IDENTIFIED BY :password USING :db_name;
    
    /*清空*/
    EXEC SQL TRUNCATE TABLE student_his;

    /*打开游标*/
    sprintf(strsql, "select * from student");
    printf("strsql:%s
", strsql);
    EXEC SQL PREPARE sql_stmt FROM :strsql;
    if(sqlca.sqlcode)
    {
        printf("sql_stmt error[%d]", sqlca.sqlcode);
        return sqlca.sqlcode;
    }
    EXEC SQL DECLARE sel_cursor cursor for sql_stmt;
    if(sqlca.sqlcode)
    {
        printf("cursor error[%d]", sqlca.sqlcode);
        return sqlca.sqlcode;
    }
    EXEC SQL OPEN sel_cursor;
    if(sqlca.sqlcode)
    {
        printf("open error[%d]", sqlca.sqlcode);
        return sqlca.sqlcode;
    }
    
    int flag = 1;
    while(flag)
    {
        memset(records, 0, sizeof(records));
        /*一次取出多条数据*/
        EXEC SQL FETCH sel_cursor INTO :records;
    if (sqlca.sqlcode)
        {
        if(sqlca.sqlcode == 100)
        {
            printf("NOT FOUND DATA[%d]", sqlca.sqlcode);
            if (sqlca.sqlerrd[2] > RECORDNUM)
            {
                printf("最后条数：%d
", sqlca.sqlerrd[2] - RECORDNUM);
                flag = 0;
            }
            else
            {
                /*没有记录*/
                break;
            }                
        }
        else
        {
            printf("fetch error[%d]", sqlca.sqlcode);
            break;
        }
    }

        /*一次插入多条数据*/
        EXEC SQL INSERT INTO student_his values(:records);
        EXEC SQL COMMIT;
    }
notfound:
    /*提交*/
    EXEC SQL COMMIT WORK RELEASE;
    printf("ok
");
    return 0;
}

void sqlerror()
{
    /*出错回滚*/
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    printf("ORACLE error detected:
");
    printf("%s
", sqlca.sqlerrm.sqlerrmc);
    EXEC SQL ROLLBACK; 
    exit(0);
}

编译运行脚本：

vim runfa.sh

proc fast.pc
cc -g -o fast fast.c -L$ORACLE_HOME/lib -L$ORACLE_HOME/rdbms/lib -L/usr/lib64 -lclntsh -lm -lc
time ./fast

说明：

fast.pc 中调用 memset() 清空数组的操作，这个操作在10万次插入过程中会增加2-3秒的时间。

经过测试，数组大小 RECORDNUM 设置为 1000 左右效率比较高，没有进行更细致范围的测试。

参考链接：

https://www.cnblogs.com/kingstarer/p/11968247.html