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

最新推荐文章于 2024-03-10 01:17:30 发布

廷哥带你小路超车

最新推荐文章于 2024-03-10 01:17:30 发布

阅读量135

点赞数

文章标签： oracle 插入基准测试

场景：

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;