看我做的数据库包装器

yulihua49 · 发表于 2010-8-25 10:07

现在来个公平的：
time ./ldtj.sh

SQL*Loader: Release 10.2.0.3.0 - Production on Wed Aug 25 09:55:07 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

specified value for readsize(10000000) less than bindsize(14000000)
Commit point reached - logical record count 1004
Commit point reached - logical record count 2008
Commit point reached - logical record count 3012
Commit point reached - logical record count 4016
Commit point reached - logical record count 5020
Commit point reached - logical record count 6024
Commit point reached - logical record count 7028
Commit point reached - logical record count 7552

real 0m0.991s
user 0m0.400s
sys    0m0.032s

Space allocated for bind array:             13987728 bytes(1004 rows)
Read buffer bytes:14000000

Total logical records skipped:       0
Total logical records read:       7552
Total logical records rejected:       0
Total logical records discarded:       0

Run began on Wed Aug 25 09:55:07 2010
Run ended on Wed Aug 25 09:55:08 2010

Elapsed time was:    00:00:00.97
CPU time was:       00:00:00.41
它对这数据的加载能力是 7785行/秒。
原来我的程序是只commit1次，为更加公平，我也改1000条一次。
这样我纯耗时0.357秒，全耗时0.505秒。
每秒加载21154行。
DAU的速度是sqlldr的近3倍，不夸张吧？

[ 本帖最后由 yulihua49 于 2010-8-25 10:35 编辑 ]

yulihua49 · 发表于 2010-8-25 10:14

现全文发表t_OAD程序，处理任意的表，是否够简单，有何需要改进？大家提意见。

#include <DAU.h>
#include <OAD.h>
#define BATCH_NUM 1000
/* 插入重码就修改
返回小于0出错，在SQL_Connect里
>=0插入的条数
*/
static int upd,loss;
//批量插入，遇重码修改之。在很多重码的场合，效率相当低
int insert_DAO(OAD *oad,int n,DAU *DP)
{
char *p;
char stmt[4096];
int cc,ret,num;
int beg;
beg=
ret=num=0;
do {
ret=OAD_exec(oad,beg,n);//真正的数组插入，就这一句。其余是错误处理
if(ret>0) num+=ret;
if(ret == n-beg) return num;
if(oad->SQL_Connect->Errno != DUPKEY) {
ShowLog(1,"%s:OAD_exec err=%d,%s",__FUNCTION__,
oad->SQL_Connect->Errno,
oad->SQL_Connect->ErrMsg);
return ret;
}
if(ret<0) ret=0;
p=(char *)oad->recs;
p+=(beg+ret)*(oad->reclen);
memcpy(DP->srm.rec,p,oad->reclen);
*stmt=0;
cc=update_by_PK(DP,stmt);
if(cc<=0) {
ShowLog(1,"%s update stmt=%s,err=%d,%s",__FUNCTION__,
stmt,
oad->SQL_Connect->Errno,
oad->SQL_Connect->ErrMsg);
return 0;
}
upd+=cc;
beg+=ret+1;
//ShowLog(5,"%s:beg=%d",__FUNCTION__,beg);
} while(beg<n);
return num;
}
int loadfile(T_SQL_Connect *SQL_Connect,char *tablename,FILE *ifd,FILE *ofd,int Pflg,char *buf,int buflen)
{
char *p,tabn[512];
DAU _DAU;
OAD oad;
int rows,ret;
int n,num;
INT64 now;
ShowLog(5,"loadfile:entry");
now=now_usec();
ret=DAU_init(&_DAU,SQL_Connect,tablename,0,0);
if(ret) {
ShowLog(1,"loadfile:DAU_init tabname=%s,ret=%d",tablename,ret);
return -1;
}
num=_DAU.srm.tp[_DAU.srm.Aflg].offset;
char recs[BATCH_NUM * num];
ShowLog(5,"DAU_init ret=%d,TIMEVAL=%ld,reclen=%d,size=%d",ret,now_usec()-now,num,sizeof(recs));
OAD_init(&oad,&_DAU,recs,BATCH_NUM);
upd=loss=0;
*buf=0;
ret=OAD_mk_ins(&oad,buf);
if(ret) {
ShowLog(1,"aft OAD_mk_ins:stmt=%s,err=%d,%s\n",buf,
SQL_Connect->Errno,
SQL_Connect->ErrMsg);
OAD_free(&oad);
DAU_free(&_DAU);
return -1;
}
n=num=0;
now=now_usec();
for(rows=0;!ferror(ifd);) {
fgets(buf,buflen,ifd);
if(feof(ifd)) break;
OAD_pkg_dispack(&oad,n,buf,'|');
if(BATCH_NUM == ++n) {
ret=insert_DAO(&oad,n,&_DAU);
___SQL_Transaction__(SQL_Connect,TRANCOMMIT);
n=0;
if(ret<0) break;
rows += ret;
}
}
if(n) {
ret=insert_DAO(&oad,n,&_DAU);
if(ret>0) rows += ret;
n=0;
}
ShowLog(2,"loadfile:rows=%d,upd=%d,TIMEVAL=%lld",rows,upd,now_usec()-now);
OAD_free(&oad);
DAU_free(&_DAU);
return rows;
}
static char my_showid[200];
#define DEF_BUF_SIZ 4096
main(int argc,char *argv[])
{
int ret,i,len=DEF_BUF_SIZ;
T_SQL_Connect SQL_Connect;
int Pflg=0;
char *tabname=0;
FILE *ifd,*ofd;
INT64 now;
char *myname;
tzset();
myname=sc_basename(argv[0]);
sprintf(my_showid,"%s:%d",
myname,getpid());
Showid=my_showid;
ifd=0;
ofd=0;
/*******************************************************************
* get Opt
*******************************************************************/
for(i=1;i<argc;i++) {
if(*argv[i]=='-') {
switch(argv[i][1]) {
case 'f':
if(argv[i][2]) ret=envcfg(argv[i]+2);
else {
i++;
ret=envcfg(argv[i]);
}
continue;
case 'P': //输出不能加载的记录
Pflg=1;
if(argv[i][2]) {
ofd=fopen(argv[i]+2,"w");
} else {
i++;
if(i<argc) ofd=fopen(argv[i],"w");
}
continue;
case 'K': //设置工作buff，Kbyte。
Pflg=1;
if(argv[i][2]) {
len=atoi(argv[i]+2);
} else {
i++;
if(i<argc) len=atoi(argv[i]);;
}
continue;
default:
fprintf(stderr,"no know option:%s",argv[i]);
fprintf(stderr,"Usage:%s -f 配置文件 [-P] 输出文件名 [-Kkbytes] [-W]WHERE 子句 [-D]执行日期 [-d]作业日期\n",
argv[0]);
continue;
}
}
tabname=argv[i];
}
if(len < DEF_BUF_SIZ) len=DEF_BUF_SIZ;
char buf[len];
*buf=0;
ShowLog(5,"tabname=%s",tabname);
ret=db_open(&SQL_Connect);
if(ret) {
ShowLog(1,"Open Database err=%d.%s",
SQL_Connect.Errno,
SQL_Connect.ErrMsg);
return 1;
}
ShowLog(5,"DB=%s",SQL_Connect.DBOWN);
if(!ofd) ofd=stdout;
ifd=stdin;
now=now_usec();
int cc;
ret=loadfile(&SQL_Connect,tabname,ifd,ofd,Pflg,buf,sizeof(buf));
ShowLog(2,"loadasc:load %d rec's time=%ld,buf=%s",ret,(long)(now_usec()-now),buf);
now=now_usec();
cc=___SQL_Transaction__(&SQL_Connect,TRANCOMMIT);
ShowLog(5,"loadfile:%d commit TIMEVAL=%lld,ret=%d",ret,now_usec()-now,cc);
if(ofd && ofd != stdout) fclose(ofd);
ret=___SQL_CloseDatabase__(&SQL_Connect);
return 0;
}

复制代码

包装器提高效率的秘诀：耗时的操作在init里完成，给后续数据作业创造条件，数据操作时的开销极小。
这里我们看一下那个最频繁使用的OAD_exec：
int OAD_exec(OAD *oad,int begin,int n)
{
int ret;

oad->begin=begin;
oad->rows=(n<=(oad->max_rows_of_batch-begin))?n

ad->max_rows_of_batch-begin;
      if(!begin) { //只有原始的执行如此
//ShowLog(5,"%s:tabname=%s",__FUNCTION__,oad->srm->tabname);
            BB_Tree_Scan(oad->bind_tree,bind_data);
      }
      ret=sqlo_execute1(oad->sth,oad->begin,n);
      if(ret) {
            ___SQL_GetError(oad->SQL_Connect);
            return -1;
      }
      if(oad->a_col_flg) BB_Tree_Scan(oad->bind_tree,get_ret);//如果有RETURNING变量，取回。
      return sqlo_prows(oad->sth);
}
这个包装干了什么？
通过二叉树检查一下数据的NULL情况，设置表示器变量。（如果你自己做，这一步很可能遗漏哦!)
然后就是执行sqlo_exec了，最后取出成功行数。
就这么简单，效率想不高都难。

为什么比sqlldr效率高？因为它使用字符绑定，要每次都绑定变量。而我，一次绑定，多次使用。这得益于DAU内部使用结构，数据已经按类型放在固定的地方。这里回答了newkid的质问，这样的应用为何要使用结构？绑定变量开销很大的。

[ 本帖最后由 yulihua49 于 2010-8-25 12:43 编辑 ]

yulihua49 · 发表于 2010-8-25 11:05

原帖由 yulihua49 于 2010-8-25 10:07 发表
现在来个公平的：
time ./ldtj.sh

SQL*Loader: Release 10.2.0.3.0 - Production on Wed Aug 25 09:55:07 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

specified value for readsize(10000000) less than bindsize(14000000)
Commit point reached - logical record count 1004
Commit point reached - logical record count 2008
Commit point reached - logical record count 3012
Commit point reached - logical record count 4016
Commit point reached - logical record count 5020
Commit point reached - logical record count 6024
Commit point reached - logical record count 7028
Commit point reached - logical record count 7552

real 0m0.991s
user 0m0.400s
sys    0m0.032s

Space allocated for bind array:             13987728 bytes(1004 rows)
Read buffer bytes:14000000

Total logical records skipped:       0
Total logical records read:       7552
Total logical records rejected:       0
Total logical records discarded:       0

Run began on Wed Aug 25 09:55:07 2010
Run ended on Wed Aug 25 09:55:08 2010

Elapsed time was:    00:00:00.97
CPU time was:       00:00:00.41
它对这数据的加载能力是 7785行/秒。
原来我的程序是只commit1次，为更加公平，我也改1000条一次。
这样我纯耗时0.357秒，全耗时0.505秒。
每秒加载21154行。
DAU的速度是sqlldr的近3倍，不夸张吧？

当然，高性能应该归功于OCI，但可以说明，DAU的包装效率要高于sqlldr的包装效率。
而且，sqlldr只是个实用程序，DAU是个开发平台，它可以让你写出许许多多的高效率应用程序。

[ 本帖最后由 yulihua49 于 2010-8-25 11:08 编辑 ]

yulihua49 · 发表于 2010-8-25 17:26

static int ins_stop(OAD *OADP,DAU *DP,int num,char *stmt)
{
int ret;

      if(DP->del_sth < 0) DAU_mk_where(DP,"train_code",stmt);
      ret=DAU_delete(DP,stmt);//先删
      ShowLog(5,"%s次，del %d Rec's",((DL10_TRAINSTOPINFO_stu *)DP->srm.rec)->train_code,ret);
      ret=OAD_exec(OADP,0,num);//批量插入
      if(ret==num) ShowLog(2,"%s次成功插入%d站",((DL10_TRAINSTOPINFO_stu *)DP->srm.rec)->train_code,ret);
      else ShowLog(1,"%s次，插入错误 %d,err=%d,%s",((DL10_TRAINSTOPINFO_stu *)DP->srm.rec)->train_code,
            ret,DP->SQL_Connect->Errno,DP->SQL_Connect->ErrMsg);
      return ret;
}

原先插入11000多记录用30秒，改批量后2.3秒。

从excel表读出数据，查站码计算运行图后插入数据库。

这个如果用存储过程，把若干车站的全部数据传给存储过程，有这功夫，活早干完了。每个车次少则2-3站，多则几十个停站，每记录19个列，叫我怎么往存储过程传？

解析xls的过程很复杂，存储过程干不了。解析结果在结构里，结构要进数据库。这活DAU最拿手了。

[ 本帖最后由 yulihua49 于 2010-8-25 17:48 编辑 ]

newkid · 发表于 2010-8-25 21:59

被sqlldr忽悠了两年，它原来是成组插入的。
我的单条插入算法与他PK，达到83%的性能，我容易吗？

谁忽悠你了？我一开始就和你说过数组绑定呀？那时候你还没掌握这个杀手锏，连绑定变量你都反对呢，能怪谁？

还需要用存储过程PK一下？

那就来用存储过程+外部表PK一下，谁怕谁。前面好像写过了？
用了外部表，整个加载过程变成一个INSERT ... SELECT 的SQL, 你来和它比比看。
PLSQL读文件比C语言慢，本来它就不适合干这个，所以我不会用读文本+插入的方式。

DAU的速度是sqlldr的近3倍，不夸张吧？

它是个通用程序，解析的功能比你强得多，当然这地方会有损耗。什么时候你实现了SQLLDR的控制文件的所有解析功能再来PK不迟。

原先插入11000多记录用30秒，改批量后2.3秒。

从excel表读出数据，查站码计算运行图后插入数据库。

这个如果用存储过程，把若干车站的全部数据传给存储过程，有这功夫，活早干完了。每个车次少则2-3站，多则几十个停站，每记录19个列，叫我怎么往存储过程传？

解析xls的过程很复杂，存储过程干不了。解析结果在结构里，结构要进数据库。这活DAU最拿手了。

把EXCEL转成文本，放在数据库上，用外部表访问。用SQL做你的所有业务（查站码啦，计算运行图啦，插入表啦）。谁说存储过程干不了？不信把你的需求写出来试试！

〇〇 · 发表于 2010-8-26 00:31

lz做一个多线程并行插入？

yulihua49 · 发表于 2010-8-26 10:12

原帖由〇〇于 2010-8-26 00:31 发表
lz做一个多线程并行插入？

实际上那个程序就是多线程并行插入。4个线程并行（因为原始表是4组列并行）。使用了数据库连接池。
后来发现池里4个连接和1个链接总体运行时间是一样的，没有区别。后来连接池只放一个连接，4个线程争用。

现在的DAU是全功能ORA数据库开发平台，能处理复杂语句如RETURNING子句，多表关联操作，批量插入。线程安全，还有数据库连接池功能。
可以调用存储过程。blob数据可以调用底层实现。DAU没有封闭底层。

使用连接池的小例子:

//准备数据单元，处理连接池
static int store(TRN_MMNT_stu *trn_rec,int num,char *stmt)
{
T_SQL_Connect SQL_Connect;
DAU trn_dau;
int ret;
INT64 now;
___SQL_Init_SQL_Connect(&SQL_Connect);
ret=get_DB_connect(&SQL_Connect,poolno);
now=now_usec();
if(ret) {
ShowLog(1,"%s,DB ERROR=%d,%s",__FUNCTION__,
SQL_Connect.Errno,
SQL_Connect.ErrMsg);
return -1;
}
DAU_init(&trn_dau,&SQL_Connect,0,trn_rec,TRN_MMNT_tpl);
___SQL_Transaction__(&SQL_Connect,TRANBEGIN);
//ShowLog(5,"mmnt dbh %d BEGIN TIMEVAL=%lld",SQL_Connect.dbh,now_usec()-now);
ret=store_data(&trn_dau,num,stmt); //执行插入数据库
DAU_free(&trn_dau);
if(!ret) ___SQL_Transaction__(&SQL_Connect,TRANCOMMIT);
else {
___SQL_Transaction__(&SQL_Connect,TRANROLLBACK);
sprintf(stmt+strlen(stmt)," err=%d,%s",SQL_Connect.Errno,SQL_Connect.ErrMsg);
}
ShowLog(5,"mmnt %s:dbh=%d,num=%d,total TIMEVAL=%lld",__FUNCTION__,SQL_Connect.dbh,num,now_usec()-now);
release_DB_connect(&SQL_Connect,poolno);
return ret;
}

复制代码

[ 本帖最后由 yulihua49 于 2010-8-26 11:14 编辑 ]

yulihua49 · 发表于 2010-8-26 10:15

原帖由 newkid 于 2010-8-25 21:59 发表

谁忽悠你了？我一开始就和你说过数组绑定呀？那时候你还没掌握这个杀手锏，连绑定变量你都反对呢，能怪谁？

它是个通用程序，解析的功能比你强得多，当然这地方会有损耗。什么时候你实现了SQLLDR的控制文件的所有解析功能再来PK不迟。

把EXCEL转成文本，放在数据库上，用外部表访问。用SQL做你的所有业务（查站码啦，计算运行图啦，插入表啦）。谁说存储过程干不了？不信把你的需求写出来试试！

我也是个通用程序，没必要用它那个控制文件，有别的办法。
我没说你忽悠我，是我没注意。还有什么杀手锏？你还可以说，我还可以做。

最新结果，我把alter session commit_write = NOWAIT去掉了，结果：
sqlldr：0.978秒，净时间0.96秒
我是0.834秒，净时间0.710秒

还有一个不重要的问题，内存消耗。为了绑定这1000个记录它动用了14M内存，我不到1M。如果在多线程服务器里这就很有感觉了。

我如果多次绑定会比他慢。
你可以看49页490楼的那个程序，不包装，多次绑定，专用的，要比DAU慢50%以上。

但现在可以说，我的包装器效率不低于最精湛的oracle程序sqlldr。不需要怀疑包装器的效率了。
我可以设置alter session commit_write = NOWAIT而大幅度提速，他行吗？

比赛解析xls，就不必了，会把帖子弄爆的。那不过是另外一种读外部文件加入数据库的小技俩，并无通用性和普遍意义。
我不是说你不能做，而是不方便，即使做成，也是给数据库引擎造成极大压力，你干活时，卖不成票了。
所以，这种活还是不要存储过程干好。

[ 本帖最后由 yulihua49 于 2010-8-26 11:05 编辑 ]

〇〇 · 发表于 2010-8-26 10:15

那sqlldr是单进程？

yulihua49 · 发表于 2010-8-26 10:26

原帖由〇〇于 2010-8-26 10:15 发表
那sqlldr是单进程？

sqlldr是单进程，我那个t_OAD也是单进程。比赛是公平的。

[PRO*C] 看我做的数据库包装器

回复 #539 yulihua49 的帖子

回复 #540 yulihua49 的帖子

批量处理应用实例