- 论坛徽章:
- 15
|
本帖最后由 yulihua49 于 2010-04-02 16:48 编辑
测试一下批量插入,在这个表存储过程单条插入100000条大约是3.6秒。
OCI批量插入100000条,
每批10条:1.2秒
100条:0.45秒。
1000条:0.27秒
单条:8.7秒。
批量还是有巨大优势,但不好管理。
下例在DAU环境下自己调sqlora完成:可以看到不用包装器,程序还是挺繁的,尤其是当列比较多时,而且还不通用。
- 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 <DAU.h>
- #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<BATCH;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
- [ 本帖最后由 yulihua49 于 2010-4-2 15:12 编辑 ]
复制代码 __________________
欢迎到我的blog:
http://blog.chinaunix.net/u3/92831/ |
|