oracle 插入 基准测试,oracle proc 插入操作性能优化实践

场景：

student 表中 10万条数据。

从 student 表中取出所有数据，插入到 student_his 表中

优化方法：

1.批量插入(效果明显)

2.批量查询(效果不明显)

3.批量提交(效果不明显)

4.预编译 sql 语句(效果不明显)

效果：

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

测试步骤：

1.创建数据库表

create tablestudent

(

student_idinteger,

student_namevarchar2(20),

salaryfloat(126)

);create unique index index_student_id onstudent(student_id);insert into student (student_id, student_name, salary) values (1, ‘test‘, 2);create tablestudent_his

(

student_idinteger,

student_namevarchar2(20),

salaryfloat(126)

);create unique index index_student_his_id on student_his(student_id);

2.准备数据：

insert.pc 插入 10万条数据

vim insert.pc

#include #include

/*插入条数*/

#define COUNTNUM 100000

/*数组大小*/

#define RECORDNUM 1000EXEC SQL INCLUDE SQLCA;voidsqlerror();

typedefstructRecord

{intstudent_id;char student_name[20];floatsalary;

}Record;intmain()

{

EXEC SQL BEGIN DECLARE SECTION;char username[20];char password[20];char db_name[20];intstudent_id;char student_name[15];floatsalary;

Record records[RECORDNUM];

EXEC SQL END DECLARE SECTION;

printf("size:%d\n",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\n", student_id);for(j=0; j

{

memset (records,0, sizeof(records));for(i=0; 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\n", 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\n");return 0;

}voidsqlerror()

{/*出错回滚*/EXEC SQL WHENEVER SQLERROR CONTINUE;

printf("ORACLE error detected:\n");

printf("%s\n", sqlca.sqlerrm.sqlerrmc);

EXEC SQL ROLLBACK;

}

编译运行脚本：

vim runin.sh

proc insert.pccc -g -o insert insert.c -L$ORACLE_HOME/lib -L$ORACLE_HOME/rdbms/lib -L/usr/lib64 -lclntsh -lm -lctime ./insert

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

common.pc 使用一般游标方式

#include #include#includeEXEC SQL INCLUDE SQLCA;voidsqlerror();

typedefstructRecord

{intstudent_id;char student_name[20];floatsalary;

}Record;intmain()

{

EXEC SQL BEGIN DECLARE SECTION;char username[20];char password[20];char db_name[20];intstudent_id;char student_name[15];floatsalary;char strsql[256];

Record records;

EXEC SQL END DECLARE SECTION;

printf("records size:%d\n",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\n", strsql);

EXEC SQL PREPARE sql_stmt FROM :strsql;if(sqlca.sqlcode)

{

printf("sql_stmt error[%d]", sqlca.sqlcode);returnsqlca.sqlcode;

}

EXEC SQL DECLARE sel_cursor cursorforsql_stmt;if(sqlca.sqlcode)

{

printf("cursor error[%d]", sqlca.sqlcode);returnsqlca.sqlcode;

}

EXEC SQL OPEN sel_cursor;if(sqlca.sqlcode)

{

printf("open error[%d]", sqlca.sqlcode);returnsqlca.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\n");return 0;

}voidsqlerror()

{/*出错回滚*/EXEC SQL WHENEVER SQLERROR CONTINUE;

printf("ORACLE error detected:\n");

printf("%s\n", sqlca.sqlerrm.sqlerrmc);

EXEC SQL ROLLBACK;

exit(1);

}

编译运行脚本：

vim runco.sh

proc common.pccc -g -o common common.c -L$ORACLE_HOME/lib -L$ORACLE_HOME/rdbms/lib -L/usr/lib64 -lclntsh -lm -lctime ./common

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

#include #include#include

/*数组大小*/

#define RECORDNUM 1000EXEC SQL INCLUDE SQLCA;voidsqlerror();

typedefstructRecord

{intstudent_id;char student_name[20];floatsalary;

}Record;intmain()

{

EXEC SQL BEGIN DECLARE SECTION;char username[20];char password[20];char db_name[20];intstudent_id;char student_name[15];floatsalary;char strsql[256];

Record records[RECORDNUM];

EXEC SQL END DECLARE SECTION;

printf("records size:%d\n",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\n", strsql);

EXEC SQL PREPARE sql_stmt FROM :strsql;if(sqlca.sqlcode)

{

printf("sql_stmt error[%d]", sqlca.sqlcode);returnsqlca.sqlcode;

}

EXEC SQL DECLARE sel_cursor cursorforsql_stmt;if(sqlca.sqlcode)

{

printf("cursor error[%d]", sqlca.sqlcode);returnsqlca.sqlcode;

}

EXEC SQL OPEN sel_cursor;if(sqlca.sqlcode)

{

printf("open error[%d]", sqlca.sqlcode);returnsqlca.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\n", 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\n");return 0;

}voidsqlerror()

{/*出错回滚*/EXEC SQL WHENEVER SQLERROR CONTINUE;

printf("ORACLE error detected:\n");

printf("%s\n", sqlca.sqlerrm.sqlerrmc);

EXEC SQL ROLLBACK;

exit(0);

}

编译运行脚本：

vim runfa.sh

proc fast.pccc -g -o fast fast.c -L$ORACLE_HOME/lib -L$ORACLE_HOME/rdbms/lib -L/usr/lib64 -lclntsh -lm -lctime ./fast

说明：

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

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

原文：https://www.cnblogs.com/etangyushan/p/12575736.html