测试一下批量插入,在这个表存储过程单条插入100000条大约是3.6秒。
OCI批量插入,10条:1.2秒
100条:0.45秒。
1000条:0.27秒
单条:8.7秒。
批量还是有巨大优势,但不好管理。
下例在DAU环境下自己调sqlora完成:可以看到不用包装器,程序还是挺繁的,尤其是当列比较多时,而且还不通用。
OCI批量插入,10条:1.2秒
100条:0.45秒。
1000条:0.27秒
单条:8.7秒。
批量还是有巨大优势,但不好管理。
下例在DAU环境下自己调sqlora完成:可以看到不用包装器,程序还是挺繁的,尤其是当列比较多时,而且还不通用。
CODE:
tuxticket@jgbticket:~/test> cat ti.c
/*************************************
create table T (
I1 number(6),
I2 number(6),
I3 number(6),
I4 number(6)
);
PK:
create or replace procedure ti
IS
I integer;
BEGIN
FOR I IN 1..100000 LOOP
INSERT INTO T VALUES (I,I,I,I);
END LOOP;
END;
/
测试批量插入
***************************************/
#include
#define BATCH 1000
typedef struct {
int i1;
int i2;
int i3;
int i4;
short i1_ind;
short i2_ind;
short i3_ind;
short i4_ind;
} ti_stu;
main(int argc,char *argv[])
{
int i,j,num=0;
int ret;
T_SQL_Connect SQL_Connect;
INT64 now;
sqlo_stmt_handle_t cursor;
char *result=0,stmt[1024];
ti_stu ti[BATCH];
if(argc>1) ret=envcfg(argv[1]);
ret=db_open(&SQL_Connect);
if(ret) {
printf("OPEN_Database %s,err=%d.%s\n",
SQL_Connect.DBOWN,
SQL_Connect.Errno,
SQL_Connect.ErrMsg);
return 1;
}
for(i=0;i ti[i].i1_ind=ti[i].i2_ind=ti[i].i3_ind=ti[i].i4_ind=0;
sprintf(stmt,"INSERT INTO %s.T (I1,I2,I3,I4) VALUES(:1,:2,:3,:4)",SQL_Connect.DBOWN);
cursor=sqlo_prepare(SQL_Connect.dbh,stmt);
if(cursor < 0) {
___SQL_GetError(&SQL_Connect);
printf("sqlo_prepare err=%d,%s\n",
SQL_Connect.Errno,
SQL_Connect.ErrMsg);
___SQL_CloseDatabase__(&SQL_Connect);
return 2;
}
printf("stmt=%s\n",stmt);
if((0>sqlo_bind_by_pos2(cursor,1,SQLOT_INT,&ti[0].i1,sizeof(int),&ti[0].i1_ind,0,sizeof(ti_stu))) ||
(0>sqlo_bind_by_pos2(cursor,2,SQLOT_INT,&ti[0].i2,sizeof(int),&ti[0].i2_ind,0,sizeof(ti_stu))) ||
(0>sqlo_bind_by_pos2(cursor,3,SQLOT_INT,&ti[0].i3,sizeof(int),&ti[0].i3_ind,0,sizeof(ti_stu))) ||
(0>sqlo_bind_by_pos2(cursor,4,SQLOT_INT,&ti[0].i4,sizeof(int),&ti[0].i4_ind,0,sizeof(ti_stu)))) {
___SQL_GetError(&SQL_Connect);
printf("sqlo_bind err=%d,%s\n",
SQL_Connect.Errno,
SQL_Connect.ErrMsg);
___SQL_Close__(&SQL_Connect,cursor);
___SQL_CloseDatabase__(&SQL_Connect);
return 3;
}
j=0;
now=now_usec();
for(i=0;i<100000;i++) {
ti[j].i1=ti[j].i2=ti[j].i3=ti[j].i4=i;
if(BATCH==++j) {
ret=sqlo_execute(cursor,j);
j=0;
if(ret) {
___SQL_GetError(&SQL_Connect);
printf("sqlo_execute err=%d,%s\n",
SQL_Connect.Errno,
SQL_Connect.ErrMsg);
break;
}
num+=sqlo_prows(cursor);
}
}
printf("insert %dRec's num=%d,TIMEVAL=%lldus\n",i,num,now_usec()-now);
strcpy(stmt,"TICKET.TI");
now=now_usec();
ret=ORA_Rpc(&SQL_Connect,stmt,&result);
printf("ORA_Rpc:return=%d,TIMEVAL=%lldus\n",ret,now_usec()-now);
___SQL_Transaction__(&SQL_Connect,TRANROLLBACK);
___SQL_CloseDatabase__(&SQL_Connect);
}
tuxticket@jgbticket:~/test> ./ti ld.ini
stmt=INSERT INTO TICKET.T (I1,I2,I3,I4) VALUES(:1,:2,:3,:4)
insert 100000Rec's num=100000,TIMEVAL=269855us
ORA_Rpc:return=0,TIMEVAL=3628331us
/*************************************
create table T (
I1 number(6),
I2 number(6),
I3 number(6),
I4 number(6)
);
PK:
create or replace procedure ti
IS
I integer;
BEGIN
FOR I IN 1..100000 LOOP
INSERT INTO T VALUES (I,I,I,I);
END LOOP;
END;
/
测试批量插入
***************************************/
#include
#define BATCH 1000
typedef struct {
int i1;
int i2;
int i3;
int i4;
short i1_ind;
short i2_ind;
short i3_ind;
short i4_ind;
} ti_stu;
main(int argc,char *argv[])
{
int i,j,num=0;
int ret;
T_SQL_Connect SQL_Connect;
INT64 now;
sqlo_stmt_handle_t cursor;
char *result=0,stmt[1024];
ti_stu ti[BATCH];
if(argc>1) ret=envcfg(argv[1]);
ret=db_open(&SQL_Connect);
if(ret) {
printf("OPEN_Database %s,err=%d.%s\n",
SQL_Connect.DBOWN,
SQL_Connect.Errno,
SQL_Connect.ErrMsg);
return 1;
}
for(i=0;i ti[i].i1_ind=ti[i].i2_ind=ti[i].i3_ind=ti[i].i4_ind=0;
sprintf(stmt,"INSERT INTO %s.T (I1,I2,I3,I4) VALUES(:1,:2,:3,:4)",SQL_Connect.DBOWN);
cursor=sqlo_prepare(SQL_Connect.dbh,stmt);
if(cursor < 0) {
___SQL_GetError(&SQL_Connect);
printf("sqlo_prepare err=%d,%s\n",
SQL_Connect.Errno,
SQL_Connect.ErrMsg);
___SQL_CloseDatabase__(&SQL_Connect);
return 2;
}
printf("stmt=%s\n",stmt);
if((0>sqlo_bind_by_pos2(cursor,1,SQLOT_INT,&ti[0].i1,sizeof(int),&ti[0].i1_ind,0,sizeof(ti_stu))) ||
(0>sqlo_bind_by_pos2(cursor,2,SQLOT_INT,&ti[0].i2,sizeof(int),&ti[0].i2_ind,0,sizeof(ti_stu))) ||
(0>sqlo_bind_by_pos2(cursor,3,SQLOT_INT,&ti[0].i3,sizeof(int),&ti[0].i3_ind,0,sizeof(ti_stu))) ||
(0>sqlo_bind_by_pos2(cursor,4,SQLOT_INT,&ti[0].i4,sizeof(int),&ti[0].i4_ind,0,sizeof(ti_stu)))) {
___SQL_GetError(&SQL_Connect);
printf("sqlo_bind err=%d,%s\n",
SQL_Connect.Errno,
SQL_Connect.ErrMsg);
___SQL_Close__(&SQL_Connect,cursor);
___SQL_CloseDatabase__(&SQL_Connect);
return 3;
}
j=0;
now=now_usec();
for(i=0;i<100000;i++) {
ti[j].i1=ti[j].i2=ti[j].i3=ti[j].i4=i;
if(BATCH==++j) {
ret=sqlo_execute(cursor,j);
j=0;
if(ret) {
___SQL_GetError(&SQL_Connect);
printf("sqlo_execute err=%d,%s\n",
SQL_Connect.Errno,
SQL_Connect.ErrMsg);
break;
}
num+=sqlo_prows(cursor);
}
}
printf("insert %dRec's num=%d,TIMEVAL=%lldus\n",i,num,now_usec()-now);
strcpy(stmt,"TICKET.TI");
now=now_usec();
ret=ORA_Rpc(&SQL_Connect,stmt,&result);
printf("ORA_Rpc:return=%d,TIMEVAL=%lldus\n",ret,now_usec()-now);
___SQL_Transaction__(&SQL_Connect,TRANROLLBACK);
___SQL_CloseDatabase__(&SQL_Connect);
}
tuxticket@jgbticket:~/test> ./ti ld.ini
stmt=INSERT INTO TICKET.T (I1,I2,I3,I4) VALUES(:1,:2,:3,:4)
insert 100000Rec's num=100000,TIMEVAL=269855us
ORA_Rpc:return=0,TIMEVAL=3628331us
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8804348/viewspace-631205/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8804348/viewspace-631205/