看我做的数据库包装器

newkid

"整个SQL的开销，相当大的仍然是语句的执行，尤其是那些decode函数，还有其他函数。"

实际上硬解析的开销完全有可能超过执行本身。TOM的书EFFECTIVE ORACLE BY DESIGN第五章没有电子版，我给你抄一段：
The hard parse includes the step of query optimization. This is an arduous, CPU-intensive process that may take longer to perform than the actual statement execution! We can see how this works using TKPROF in an example. Consider this block of code:

Drop table t;
create table t (x int);

alter session set sql_trace=true;

begin
   for i in 1..1000
   loop
      execute immediate 'insert into t values ('||i||')';
   end loop;
end;
/

接下来的TKPROF报告表明parse的CPU是0.91, execute是0.31; (高达三倍!)
改用绑定变量的动态SQL, parse的CPU是0.05, execute是0.32;
再改用嵌入式SQL (这是PLSQL的好处), parse的CPU是0, execute是0.33; (PARSE只有一次，已经小得无法测量了)

对于你给的例子，三千多条数据，我写了个SQL LOADER 的 CONTROL文件，你可以试验一下。SQL LOADER是用绑定变量的C程序。

你把附件TJ01.ctl拷贝到TJ01.txt那个目录，然后在操作系统运行命令：
sqlldr userid=my_name/my_password@my_database control=tj01.ctl rows=10000 bindsize=10000000 readsize=10000000

看看和你的不用绑定变量的差多少？数据量越大差距越大，你这几千条还是太少。

另外，你还是没有回答票据如何生成的问题。难道你是先把数据先导出到文本文件，然后再用这个工具加载到SEATS表？

yulihua49

原帖由 newkid 于 2008-12-16 00:21 发表


"整个SQL的开销，相当大的仍然是语句的执行，尤其是那些decode函数，还有其他函数。"

实际上硬解析的开销完全有可能超过执行本身。TOM的书EFFECTIVE ORACLE BY DESIGN第五章没有电子版，我给你抄一段：
The hard parse includes the step of query optimization. This is an arduous, CPU-intensive process that may take longer to perform than the actual statement execution! We can see how this works using TKPROF in an example. Consider this block of code:

Drop table t;
create table t (x int);

alter session set sql_trace=true;

begin
   for i in 1..1000
   loop
      execute immediate 'insert into t values ('||i||')';
   end loop;
end;
/

接下来的TKPROF报告表明parse的CPU是0.91, execute是0.31; (高达三倍!)
改用绑定变量的动态SQL, parse的CPU是0.05, execute是0.32;
再改用嵌入式SQL (这是PLSQL的好处), parse的CPU是0, execute是0.33; (PARSE只有一次，已经小得无法测量了)

对于你给的例子，三千多条数据，我写了个SQL LOADER 的 CONTROL文件，你可以试验一下。SQL LOADER是用绑定变量的C程序。

你把附件TJ01.ctl拷贝到TJ01.txt那个目录，然后在操作系统运行命令：
sqlldr userid=my_name/my_password@my_database control=tj01.ctl rows=10000 bindsize=10000000 readsize=10000000

看看和你的不用绑定变量的差多少？数据量越大差距越大，你这几千条还是太少。

另外，你还是没有回答票据如何生成的问题。难道你是先把数据先导出到文本文件，然后再用这个工具加载到SEATS表？

票据如何生成, 从称之为‘母表’的数据库生成，牵涉太多的业务规则。不是文件导入的。
正在弄bind程序，晕头转向中，libsqlora老是不对劲。

truncate这个数据库还需1-2秒，可以把这个时间扣除。

我之所以写这个load，因为需要处理重码，解决数据追加的问题，将来不是总能truncate的。

这个load将变身成各种专用、半专用的load，可能要处理更复杂的逻辑。

[ 本帖最后由 yulihua49 于 2008-12-16 11:52 编辑 ]

newkid

"truncate这个数据库还需1-2秒，可以把这个时间扣除。"
可以用APPEND代替TRUNCATE。

复杂的逻辑，比如MERGE等可以另外用SQL或存储过程实现。先用SQL LOADER把数据加载到一张STAGING TABLE再后续处理。

SQL LOADER在功能、速度上都比你自己做的好；批量载入再批量处理绝对比你逐条处理要好。我前面说过SQL的思想就是集合操作，这和你的“按记录操作”的定性思维有很大差别，但它是效率最高的。

你如果不相信，请设计一个复杂的加载处理需求，我再和你打个擂台。记得要用大批量数据来比试!

我刚刚发现11G的这个好东西，不敢独享：
11G的DBMS_SQL支持REF CURSOR和NUMER CURSOR互相转换。
这意味着：一个存储过程打开的REF CURSOR可被另一个不知道其结构的存储过程所访问;
这意味着：可以写一个通用的模块来进行格式转换，把你的多个字段合并成一个JSON 格式的字符串;
这意味着：你可以认为所有存储过程都只返回一个列，里面是一个JSON 格式的字符串, 你的程序不用再对其处理。
而像to_char(ON_DATE,'YYYY-MM-DD HH24:MI')这样的简单转换就直接写在SQL中，因此模板完全可以抛弃了。

虽然我不赞成在数据库端格式化数据，但你实在要这么干也未尝不可，你的程序又再次减肥了。

yulihua49

原帖由 newkid 于 2008-12-17 00:37 发表
"truncate这个数据库还需1-2秒，可以把这个时间扣除。"
可以用APPEND代替TRUNCATE。

复杂的逻辑，比如MERGE等可以另外用SQL或存储过程实现。先用SQL LOADER把数据加载到一张STAGING TABLE再后续处理。

SQL LOADER在功能、速度上都比你自己做的好；批量载入再批量处理绝对比你逐条处理要好。我前面说过SQL的思想就是集合操作，这和你的“按记录操作”的定性思维有很大差别，但它是效率最高的。

你如果不相信，请设计一个复杂的加载处理需求，我再和你打个擂台。记得要用大批量数据来比试!

我刚刚发现11G的这个好东西，不敢独享：
11G的DBMS_SQL支持REF CURSOR和NUMER CURSOR互相转换。
这意味着：一个存储过程打开的REF CURSOR可被另一个不知道其结构的存储过程所访问;
这意味着：可以写一个通用的模块来进行格式转换，把你的多个字段合并成一个JSON 格式的字符串;
这意味着：你可以认为所有存储过程都只返回一个列，里面是一个JSON 格式的字符串, 你的程序不用再对其处理。
而像to_char(ON_DATE,'YYYY-MM-DD HH24:MI')这样的简单转换就直接写在SQL中，因此模板完全可以抛弃了。

虽然我不赞成在数据库端格式化数据，但你实在要这么干也未尝不可，你的程序又再次减肥了。

多谢，学习了。

绑定变量的insert还没调通。最近比较忙，没有太多时间做实验。
你给我程序的结果：
time ./ldtj.sh

SQL*Loader: Release 10.2.0.3.0 - Production on Wed Dec 17 08:57:43 2008

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

Commit point reached - logical record count 717
Commit point reached - logical record count 1434
Commit point reached - logical record count 2151
Commit point reached - logical record count 2868
Commit point reached - logical record count 3585
Commit point reached - logical record count 3776

real    0m0.718s
user    0m0.196s
sys     0m0.020s

我程序的结果：
time ./ldasc >/dev/null <../sql/TJ01.txt

real    0m6.988s
user    0m0.740s
sys     0m0.072s
看来，我是慢了近10倍。
但是这并不说明bind的作用。
见如下参数：
bindsize=10000000 readsize=10000000
我估计它是直接调用底层进行块读写，连SQL都不用。全部IO时间只有0.5秒，估计是717条记录写一次。仅仅是bind，不可能减少IO时间的。

我的程序，user时间只有0.74秒，我优化到家，也就这点时间。这里边有多少是分析SQL的呢？


当年，INFORMIX-SE提供了底层操作C-ISAM,我们一直在用，比SQL快25倍呢。ORACLE没提供这东西，只留着自己用。
用那个工具，插入10000条记录（跟这个差不多，100个DAT吧，逻辑是一样的）3秒左右，当时可是200M主频，8M内存，20M SCSI的时代，与今天不可同日而语，已经是相当可观了。
但它还不是块读写，仍然是记录读写。
刚发现，与SQL*loader一样快，看来不必块读写，记录读写也行，绕过SQL就可以。

还有，ORACLE如此怪异，见我前边的日志和程序，
为什么运行时间如此怪异？
insert + loss（149秒）  >insert （6秒） > insert + update （4秒）
完全是违反常规的，如何解释？
看来，先查询再进行insert或update可能速度更快。

[ 本帖最后由 yulihua49 于 2008-12-17 09:53 编辑 ]

newkid

“见如下参数：bindsize=10000000 readsize=10000000”
这个是为了减少COMMIT次数和读取TXT文件的次数，跟SQL没有关系。用的仍然是绑定变量和传统路径加载(SQL)。
你可以把这个参数设到最大值20971520，COMMIT次数更少了因而速度也更快。


"我估计它是直接调用底层进行块读写，连SQL都不用。"
那个直接路径加载参数是DIRECT=TRUE, 我没有打开，就是想让它作传统路径的INSERT.

"全部IO时间只有0.5秒，估计是717条记录写一次。仅仅是bind，不可能减少IO时间的。"
不知道时间是怎么衡量的，但IO的减少和上述两个SQL LOADER参数有关。

"我的程序，user时间只有0.74秒，我优化到家，也就这点时间。这里边有多少是分析SQL的呢？"
去像我举例那样先做一个alter session set sql_trace=true; 再用TKPROF去分析跟踪文件，里面有解析时间。


"当年，INFORMIX-SE提供了底层操作C-ISAM,我们一直在用，比SQL快25倍呢。ORACLE没提供这东西，只留着自己用。"
谁说ORACLE没有提供？INSERT有APPEND提示可用; 但索引的维护是绕不过去的。绕过SQL也就跳过了REDO日志，有一定风险。
还有很多加载技巧，比如用外部表;DATA PUMP; 先DISABLE约束/删除索引，事后再重建。这些都是在ETL中使用的，像售票系统这样的OLTP很少加载外部数据。

"还有，ORACLE如此怪异，见我前边的日志和程序，"
照理说UPDATE开销最大，INSERT最小。你确定你的试验是在单用户环境下吗？如果有其他负栽，硬解析是很受影响的，因为里面使用了很多全局的LATCH. 你不妨在自己的PC机上装一个ORACLE作试验。

yulihua49

原帖由 newkid 于 2008-12-18 00:38 发表
"还有，ORACLE如此怪异，见我前边的日志和程序，"
照理说UPDATE开销最大，INSERT最小。你确定你的试验是在单用户环境下吗？如果有其他负栽，硬解析是很受影响的，因为里面使用了很多全局的LATCH. 你不妨在自己的PC机上装一个ORACLE作试验。

结果是可重复的，数据稳定，机器负载很轻。在席位生成程序中也发现此事，如果席位已经生成，再次生成就会重码，重码就loss it，这时很慢。有些事情难以解释。
你也可以试试，估计与工具无关，用什么都一样。

那个OCI bind参数还在寻找中。

的确是单引号的问题，insert通过，update调试中。
服了你了，你说的对。insert的结果：
5 ./loadsth:28768 12/18 09:26'17 tabname=tjrb
5 ./loadsth:28768 12/18 09:26'17 ___SQL_OpenDatabase__: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
5 ./loadsth:28768 12/18 09:26'17 DB=TICKET
5 ./loadsth:28768 12/18 09:26'17 loadfile:entry
5 ./loadsth:28768 12/18 09:26'17 mkpk:tjdate|unit|tabname|flg|
5 ./loadsth:28768 12/18 09:26'17 insrec:bind=54,sth=0,stmt=INSERT INTO TICKET.TJRB (tjdate,unit,tabname,flg,dat1,dat2,dat3,dat4,dat5,dat6,dat7,dat8,dat9,dat10,dat11,dat12,dat13,dat14,dat15,dat16,dat17,dat18,dat19,dat20,dat21,dat22,dat23,dat24,dat25,dat26,dat27,dat28,dat29,dat30,dat31,dat32,dat33,dat34,dat35,dat36,dat37,dat38,dat39,dat40,dat41,dat42,dat43,dat44,dat45,dat46,dat47,dat48,dat49,dat50) VALUES(to_date(:1,'YYYY-MM-DD'), :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54 )
2 ./loadsth:28768 12/18 09:26'18 loadfile:rows=3776,upd=0,loss=0
2 ./loadsth:28768 12/18 09:26'18 loadasc:load 3776 rec's time=1,buf=2007.01.31
~
估计不到1秒。

time ./ldasc >/dev/null <../sql/TJ01.txt

real    0m1.178s
user    0m0.416s
sys     0m0.144s

下一代包装器将支持bind，但是，过程和参数会更复杂一些。

5 ./loadsth:28911 12/18 09:46'48 insrec:sqlo_execute=-1,errmsg=ORA-00001: unique constraint (TICKET.SYS_C0011534) violated

2 ./loadsth:28911 12/18 09:46'48 loadfile:rows=0,upd=0,loss=3776
2 ./loadsth:28911 12/18 09:46'48 loadasc:load 0 rec's time=140,buf=2007.01.31

[ 本帖最后由 yulihua49 于 2008-12-18 09:52 编辑 ]

yulihua49

update做好了，结果：
5 ./loadsth:29915 12/18 11:52'33 ___SQL_OpenDatabase__: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
5 ./loadsth:29915 12/18 11:52'33 DB=TICKET
5 ./loadsth:29915 12/18 11:52'33 loadfile:entry
5 ./loadsth:29915 12/18 11:52'33 mkpk:tjdate|unit|tabname|flg|
5 ./loadsth:29915 12/18 11:52'33 insrec:bind=54,sth=0,stmt=INSERT INTO TICKET.TJRB (tjdate,unit,tabname,flg,dat1,dat2,dat3,dat4,dat5,dat6,dat7,dat8,dat9,dat10,dat11,dat12,dat13,dat14,dat15,dat16,dat17,dat18,dat19,dat20,dat21,dat22,dat23,dat24,dat25,dat26,dat27,dat28,dat29,dat30,dat31,dat32,dat33,dat34,dat35,dat36,dat37,dat38,dat39,dat40,dat41,dat42,dat43,dat44,dat45,dat46,dat47,dat48,dat49,dat50) VALUES(to_date(:1,'YYYY-MM-DD'), :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54 )
5 ./loadsth:29915 12/18 11:52'33 updaterec:bind=58,sth=1,stmt=UPDATE TICKET.TJRB SET(tjdate,unit,tabname,flg,dat1,dat2,dat3,dat4,dat5,dat6,dat7,dat8,dat9,dat10,dat11,dat12,dat13,dat14,dat15,dat16,dat17,dat18,dat19,dat20,dat21,dat22,dat23,dat24,dat25,dat26,dat27,dat28,dat29,dat30,dat31,dat32,dat33,dat34,dat35,dat36,dat37,dat38,dat39,dat40,dat41,dat42,dat43,dat44,dat45,dat46,dat47,dat48,dat49,dat50)=(SELECT to_date(:1,'YYYY-MM-DD'), :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54 FROM DUAL) WHERE tjdate=to_date(:55,'YYYY-MM-DD') AND unit=:56 AND tabname=:57 AND flg=:58
2 ./loadsth:29915 12/18 11:52'36 loadfile:rows=0,upd=3776,loss=0
2 ./loadsth:29915 12/18 11:52'36 loadasc:load 0 rec's time=3,buf=0

3秒，有点对劲了，但，loss还是140多秒，为什么？

程序全文发表如下:

1. 
   
2. #include <stdio.h>
   
3. #define SERVER
   
4. #include <DAU_utility.h>
   
5. #include <ctype.h>
   
6. 
   
7. #define trans_begin(SQL_Connect)  ___SQL_Transaction__(SQL_Connect,TRANBEGIN)
   
8. #define trans_rollback(SQL_Connect)  ___SQL_Transaction__(SQL_Connect,TRANROLLBACK)
   
9. #define trans_commit(SQL_Connect)  ___SQL_Transaction__(SQL_Connect,TRANCOMMIT)
   
10. 
    
11. char * mkcondi(char *vp,T_PkgType *typ,int *argc)
    
12. {
    
13.         vp+=sprintf(vp,"%s=",typ->name);
    
14.         switch(typ->type) {
    
15.             case CH_DATE:
    
16.             case CH_JUL:
    
17.             case CH_CJUL:
    
18.             case CH_TIME:
    
19.             case CH_CTIME:
    
20.             case CH_MINUTS:
    
21.             case CH_CMINUTS:
    
22.                 vp += sprintf(vp,"to_date(:%d,'%s')",++*argc,typ->format);
    
23.                 break;
    
24.             default:
    
25.                 vp+=sprintf(vp,":%d",++*argc);
    
26.                 break;
    
27.         }
    
28.         return vp;
    
29. }
    
30. 
    
31. static int mkwhere(SRM *srmp,char *where,int *argc)
    
32. {
    
33. char *pks,*p,*p1;
    
34. char kbuf[31];
    
35. int i,j;
    
36. T_PkgType Char_Type[2];
    
37.         if(!srmp||!where) return 0;
    
38.         if(srmp->pkn<=0 || !srmp->pks) {
    
39.                 return 0;
    
40.         }
    
41.         *where=0;
    
42.         pks=strdup(srmp->pks);
    
43.         p=pks;
    
44.         Char_Type[0].type=CH_CHAR;
    
45.         Char_Type[0].len=-1;
    
46.         Char_Type[0].offset=0;
    
47.         Char_Type[1].type=-1;
    
48.         Char_Type[1].len=0;
    
49.         p1=where;
    
50.         p1+=sprintf(p1,"WHERE ");
    
51.         for(i=0;i<srmp->pkn;i++) {
    
52.                 p+=net_dispack(kbuf,p,Char_Type);
    
53.                 j=pkg_getnum(kbuf,srmp->tp);
    
54.                 if(srmp->tp[j].type<0) continue;
    
55.                 if(i>0) p1+=sprintf(p1," AND ");
    
56.                 p1 = mkcondi(p1,&srmp->tp[j],argc);
    
57.         }
    
58.         free(pks);
    
59. //ShowLog(5,"mkwere:%s",where);
    
60.         return strlen(where);
    
61. }
    
62. 
    
63. static char *mk_val(char *values,void  *data, T_PkgType *tp,int *num)
    
64. {
    
65. T_PkgType *typ;
    
66. char *vp,tmp[4096];
    
67. int i;
    
68.         if(!values || !data || !tp ->type<0) return values;
    
69.         vp=values;
    
70.         *vp=0;
    
71.         if(tp->offset<0) set_offset(tp);
    
72.         for(i=0,typ=tp;typ->type != -1;i++,typ++) {
    
73.             if(typ->type == CH_CLOB || !strcmp(typ->name,"ROWID")) {
    
74. /* can not insert CLOB *p and ROWID into database,skip it*/
    
75.                 continue;
    
76.             }
    
77.             switch(typ->type) {
    
78.             case CH_DATE:
    
79.             case CH_JUL:
    
80.             case CH_CJUL:
    
81.             case CH_TIME:
    
82.             case CH_CTIME:
    
83.             case CH_MINUTS:
    
84.             case CH_CMINUTS:
    
85.                 vp += sprintf(vp,"to_date(:%d,'%s'),",++*num,typ->format);
    
86.                 break;
    
87.             default:
    
88.                 vp+=sprintf(vp," :%d,",++*num);
    
89.                 break;
    
90.             }
    
91.         }
    
92.         if(*(vp-1) == ',') *(--vp)=0;
    
93.         return vp;
    
94. }
    
95. 
    
96. 
    
97. static int updaterec(DAU *DP,char *buf)
    
98. {
    
99. int ret,bindnum,i;
    
100. int argc=0;
     
101. char **argv;
     
102. char *p,tmp[4096];
     
103. static sqlo_stmt_handle_t upd_sth=SQLO_STH_INIT;
     
104. int coln,pkn;
     
105. 
     
106.         if(!buf) { //任务结束，关闭游标
     
107.                 if(upd_sth>=0) ret=___SQL_Close__(DP->SQL_Connect,upd_sth);
     
108.                 upd_sth=SQLO_STH_INIT;
     
109.                 return ret;
     
110.         }
     
111. 
     
112.         coln=abs(DP->srm.Aflg);
     
113.         pkn=DP->srm.pkn;
     
114.         argv=(char **)malloc((coln+pkn+1) * sizeof(char *));
     
115.         if(!argv) {
     
116.                 sprintf(buf,"updaterec:argv #存储分配错误#");
     
117.                 return MEMERR;
     
118.         }
     
119. /* 绑定列值，有多少要绑定的？*/
     
120.         for(bindnum=0;bindnum<coln;bindnum++) {
     
121.             if(DP->srm.tp[bindnum].type == CH_CLOB || !strcmp(DP->srm.tp[bindnum].name,"ROWID")) {
     
122. /* can not insert CLOB *p and ROWID into database,skip it*/
     
123.                 bindnum--;
     
124.                 continue;
     
125.             }
     
126.                 get_one(buf,DP->srm.rec,DP->srm.tp,bindnum,0); //取值，转char类型
     
127.                 argv[bindnum]=strdup(buf); //值加入argv
     
128.         }
     
129.         if(pkn > 0) {  //  绑定键值
     
130.         char *pks,*p2;
     
131.         int n;
     
132.                 pks=strdup(DP->srm.pks);
     
133.                 p2=pks;
     
134.                 for(ret=0;ret<pkn;ret++) {
     
135.                         p2+=net_dispack(tmp,p2,CharType);
     
136.                         n=pkg_getnum(buf,DP->srm.tp);
     
137.                         get_one(tmp,DP->srm.rec,DP->srm.tp,n,0); //取值，转char类型
     
138.                         argv[bindnum++]=strdup(tmp); //值加入argv
     
139.                 }
     
140.                 free(pks);
     
141.         }
     
142.         if(upd_sth==SQLO_STH_INIT) {
     
143.                 p=buf;
     
144.                 p+=sprintf(p,"UPDATE %s.%s SET(%s)=(SELECT ",DP->SQL_Connect->DBOWN, DP->srm.tabname,
     
145.                         mkset(tmp,DP->srm.tp));
     
146.                 p=mk_val(p,DP->srm.rec,DP->srm.tp,&argc);
     
147.                 p+=sprintf(p," FROM DUAL) ");
     
148.                 p+=mkwhere(&DP->srm,p,&argc);
     
149.                 upd_sth=sqlo_open(DP->SQL_Connect->dbh, (CONST char *)buf,bindnum,(CONST char **)argv);
     
150. ShowLog(5,"updaterec:bind=%d,sth=%d,stmt=%s",bindnum,upd_sth,buf);
     
151.                 if(upd_sth<0) ret=upd_sth;
     
152.                 else ret=0;
     
153.         } else {
     
154.                 ret=sqlo_reopen(upd_sth,bindnum,(CONST char **)argv);
     
155.         }
     
156. 
     
157.         if(ret) {
     
158.                 ___SQL_GetError(DP->SQL_Connect);
     
159.                 ShowLog(1,"updaterec:sqlo_open=%d,errmsg=%s",ret,DP->SQL_Connect->ErrMsg);
     
160.                 for(i=0;i<bindnum;i++) free(argv[i]);
     
161.                 free(argv);
     
162.                 return -1;
     
163.         }
     
164.         while (SQLO_STILL_EXECUTING == (ret = sqlo_execute(upd_sth, 1))) {
     
165.                 usleep(1000);
     
166.         }
     
167. 
     
168.         if(ret) {
     
169. char *p;
     
170.                 ___SQL_GetError(DP->SQL_Connect);
     
171. p=buf;
     
172. for(i=0;i<bindnum;i++) p+=sprintf(p,"%s,",argv[i]);
     
173.                 ShowLog(1,"updaterec:sqlo_execute=%d,err=%d,%s,values=%s",ret,
     
174.                         DP->SQL_Connect->Errno,
     
175.                         DP->SQL_Connect->ErrMsg,buf);
     
176.         }
     
177.         for(i=0;i<bindnum;i++) free(argv[i]);
     
178.         free(argv);
     
179.         return ret;
     
180. }
     
181. 
     
182. static int insrec(DAU *DP,char *buf)
     
183. {
     
184. int ret,bindnum;
     
185. int argc=0;
     
186. char **argv;
     
187. char *p,tmp[4096];
     
188. static sqlo_stmt_handle_t ins_sth=SQLO_STH_INIT;
     
189. int coln,i;
     
190. 
     
191.         if(!buf) { //任务结束，关闭游标
     
192.                 if(ins_sth>=0) ret=___SQL_Close__(DP->SQL_Connect,ins_sth);
     
193.                 ins_sth=SQLO_STH_INIT;
     
194.                 return ret;
     
195.         }
     
196.         coln=abs(DP->srm.Aflg);
     
197.         argv=(char **)malloc((coln+1) * sizeof(char *));
     
198.         if(!argv) {
     
199.                 sprintf(buf,"insrec argv #存储分配错误#");
     
200.                 return MEMERR;
     
201.         }
     
202. /* 绑定列值，有多少要绑定的？*/
     
203.         for(bindnum=0;bindnum<coln;bindnum++) {
     
204.             if(DP->srm.tp[bindnum].type == CH_CLOB || !strcmp(DP->srm.tp[bindnum].name,"ROWID")) {
     
205. /* can not insert CLOB *p and ROWID into database,skip it*/
     
206.                 bindnum--;
     
207.                 continue;
     
208.             }
     
209.                 get_one(tmp,DP->srm.rec,DP->srm.tp,bindnum,0); //取值，转char类型
     
210.                 argv[bindnum]=strdup(tmp); //值加入argv
     
211.         }
     
212.         if(ins_sth==SQLO_STH_INIT) {
     
213.                 p=buf;
     
214.                 p+=sprintf(p,"INSERT INTO %s.%s (%s) VALUES(",DP->SQL_Connect->DBOWN, DP->srm.tabname,
     
215.                         mkset(tmp,DP->srm.tp));
     
216.                 p=mk_val(p,DP->srm.rec,DP->srm.tp,&argc);
     
217.                 p+=sprintf(p," )");
     
218.                 ins_sth=sqlo_open(DP->SQL_Connect->dbh, (CONST char *)buf,bindnum,(CONST char **)argv),
     
219. ShowLog(5,"insrec:bind=%d,sth=%d,stmt=%s",bindnum,ins_sth,buf);
     
220.                 if(ins_sth<0) ret=ins_sth;
     
221.                 else {
     
222.                         ret=0;
     
223.                 }
     
224.         } else {
     
225.                 ret=sqlo_reopen(ins_sth,bindnum,(CONST char **)argv);
     
226.         }
     
227.         if(ret) {
     
228.                 ___SQL_GetError(DP->SQL_Connect);
     
229.                 ShowLog(5,"insrec:sqlo_open=%d,errmsg=%s",ret,DP->SQL_Connect->ErrMsg);
     
230.                 for(i=0;i<bindnum;i++) free(argv[i]);
     
231.                 free(argv);
     
232.                 return -1;
     
233.         }
     
234.         while (SQLO_STILL_EXECUTING == (ret = sqlo_execute(ins_sth, 1))) {
     
235.                 usleep(1000);
     
236.         }
     
237. 
     
238.         if(ret) {
     
239.                 ___SQL_GetError(DP->SQL_Connect);
     
240.                 sprintf(buf,"insrec:sqlo_execute=%d,err=%d,%s",ret,
     
241.                         DP->SQL_Connect->Errno,
     
242.                         DP->SQL_Connect->ErrMsg);
     
243.         }
     
244.         for(i=0;i<bindnum;i++) free(argv[i]);
     
245.         free(argv);
     
246.         return ret;
     
247. }
     
248. 
     
249. int loadfile(T_SQL_Connect *SQL_Connect,char *tabname,FILE *ifd,FILE *ofd,int Pflg,char *buf,int buflen)
     
250. {
     
251. char *p,tabn[512];
     
252. DAU _DAU;
     
253. int rows,ret;
     
254. int upd,loss;
     
255. 
     
256.         ShowLog(5,"loadfile:entry");
     
257.         if(tabname) {
     
258.                 ret=DAU_init(&_DAU,SQL_Connect,tabname,0,0);
     
259.                 if(ret) {
     
260.                         ShowLog(1,"loadfile:DAU_init tabname=%s,ret=%d",tabname,ret);
     
261.                         return -1;
     
262.                 }
     
263.         }
     
264.         upd=loss=0;
     
265.         for(rows=0;!ferror(ifd);rows++) {
     
266.                 fgets(buf,buflen,ifd);
     
267.                 if(feof(ifd)) break;
     
268.                 TRIM(buf);
     
269.                 if(!*buf) {
     
270.                         rows--;
     
271.                         continue;
     
272.                 }
     
273.                 if(rows==0 && !tabname) {
     
274.                         p=skipblk(buf);
     
275.                         ret=sscanf(p,"TABNAME=%s",tabn);
     
276.                         if(ret<1) {
     
277.                                 ShowLog(1,"Can Not find TABLE Name!");
     
278.                                 return FORMATERR;
     
279.                         }
     
280.                         ret=DAU_init(&_DAU,SQL_Connect,tabn,0,0);
     
281.                         if(ret) {
     
282.                                 ShowLog(1,"loadfile:DAU_init tabname=%s",tabn);
     
283.                                 return -1;
     
284.                         }
     
285.                         continue;
     
286.                 }
     
287.                 DAU_dispack(&_DAU,buf);
     
288.                 ret=insrec(&_DAU,buf);
     
289.                 if(ret) {
     
290.                         if(SQL_Connect->Errno == DUPKEY) {
     
291.                                 if(!Pflg) {//如果没有-P选项
     
292.                                         ret=updaterec(&_DAU,buf);
     
293.                                         if(ret==0) upd+=1;
     
294.                                         else loss++;
     
295.                                 }
     
296.                                 else loss++;
     
297.                         } else {
     
298.                                 DAU_pack(&_DAU,buf);
     
299.                                 fprintf(ofd,"%s\n",buf);
     
300.                                 ShowLog(1,"loadfile:%s",buf);
     
301.                                 loss++;
     
302.                         }
     
303.                         rows--;
     
304.                         continue;
     
305.                 }
     
306.         }
     
307.         DAU_free(&_DAU);
     
308.         ShowLog(2,"loadfile:rows=%d,upd=%d,loss=%d",rows,upd,loss);
     
309.         return rows;
     
310. }
     
311. 
     
312. static char my_showid[200];
     
313. main(int argc,char *argv[])
     
314. {
     
315. int ret,i;
     
316. T_SQL_Connect SQL_Connect;
     
317. int Pflg=0;
     
318. char *tabname=0;
     
319. FILE *ifd,*ofd;
     
320. INT64 now;
     
321. char buf[4096];//最大数据长度有限
     
322. 
     
323.         tzset();
     
324.         sprintf(my_showid,"%s:%d",
     
325.                 argv[0],getpid());
     
326.         Showid=my_showid;
     
327.         ifd=0;
     
328.         ofd=0;
     
329. 
     
330. /*******************************************************************
     
331. *  get Opt
     
332. *******************************************************************/
     
333.         for(i=1;i<argc;i++) {
     
334.                 if(*argv[i]=='-') {
     
335.                         switch(argv[i][1]) {
     
336.                         case 'f':
     
337.                                 if(argv[i][2]) ret=envcfg(argv[i]+2);
     
338.                                 else {
     
339.                                         i++;
     
340.                                         ret=envcfg(argv[i]);
     
341.                                 }
     
342.                                 continue;
     
343.                         case 'P':  //输出 不能加载的记录
     
344.                                 Pflg=1;
     
345.                                 if(argv[i][2]) {
     
346.                                         ofd=fopen(argv[i]+2,"w");
     
347.                                 } else {
     
348.                                         i++;
     
349.                                         if(i<argc) ofd=fopen(argv[i],"w");
     
350.                                 }
     
351.                                 continue;
     
352. 
     
353.                         default:
     
354.                                 fprintf(stderr,"no know option:%s",argv[i]);
     
355.                                 fprintf(stderr,"Usage:%s -f 配置文件 [-P]  输出文件名 ",
     
356.                                         argv[0]);
     
357.                                 continue;
     
358.                         }
     
359.                 }
     
360.                 tabname=argv[i];
     
361.         }
     
362. 
     
363. ShowLog(5,"tabname=%s",tabname);
     
364.         ret=db_open(&SQL_Connect);
     
365.         if(ret) {
     
366.                 ShowLog(1,"Open Database err=%d.%s",
     
367.                         SQL_Connect.Errno,
     
368.                         SQL_Connect.ErrMsg);
     
369.                 return 1;
     
370.         }
     
371. ShowLog(5,"DB=%s",SQL_Connect.DBOWN);
     
372.         if(!ofd) ofd=stdout;
     
373.         ifd=stdin;
     
374. 
     
375.         now=now_sec();
     
376.         ret=loadfile(&SQL_Connect,tabname,ifd,ofd,Pflg,buf,sizeof(buf));
     
377.         ShowLog(2,"loadasc:load %d rec's time=%d,buf=%s",ret,(int)(now_sec()-now),buf);
     
378.         trans_commit(&SQL_Connect);
     
379. 
     
380.         if(ofd && ofd != stdout) fclose(ofd);
     
381.         ret=___SQL_CloseDatabase__(&SQL_Connect);
     
382.         return 0;
     
383. }
     

复制代码

[ 本帖最后由 yulihua49 于 2008-12-18 12:01 编辑 ]

yulihua49

//新插入的：
tuxticket@jgbticket:~/test> time ./ldasc >/dev/null <../sql/TJ01.txt

real    0m1.203s
user    0m0.452s
sys     0m0.124s
//修改的：
tuxticket@jgbticket:~/test> time ./ldasc >/dev/null <../sql/TJ01.txt

real    0m3.239s
user    0m0.928s
sys     0m0.320s
tuxticket@jgbticket:~/test>

5 ./loadsth:30942 12/18 15:05'10 insrec:bind=54,sth=0,stmt=INSERT INTO TICKET.TJRB (tjdate,unit,tabname,flg,dat1,dat2,dat3,dat4,dat5,dat6,dat7,dat8,dat9,dat10,dat11,dat12,dat13,dat14,dat15,dat16,dat17,dat18,dat19,dat20,dat21,dat22,dat23,dat24,dat25,dat26,dat27,dat28,dat29,dat30,dat31,dat32,dat33,dat34,dat35,dat36,dat37,dat38,dat39,dat40,dat41,dat42,dat43,dat44,dat45,dat46,dat47,dat48,dat49,dat50) VALUES(to_date(:1,'YYYY-MM-DD'), :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54 )
2 ./loadsth:30942 12/18 15:05'11 loadfile:rows=3776,upd=0,loss=0
2 ./loadsth:30942 12/18 15:05'11 loadasc:load 3776 rec's time=1,buf=2007.01.31
5 ./loadsth:30946 12/18 15:05'20 tabname=tjrb5 ./loadsth:30946 12/18 15:05'20 ___SQL_OpenDatabase__: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP and Data Mining options5 ./loadsth:30946 12/18 15:05'20 DB=TICKET5 ./loadsth:30946 12/18 15:05'20 loadfile:entry
5 ./loadsth:30946 12/18 15:05'20 mkpk:tjdate|unit|tabname|flg|
5 ./loadsth:30946 12/18 15:05'20 insrec:bind=54,sth=0,stmt=INSERT INTO TICKET.TJRB (tjdate,unit,tabname,flg,dat1,dat2,dat3,dat4,dat5,dat6,dat7,dat8,dat9,dat10,dat11,dat12,dat13,dat14,dat15,dat16,dat17,dat18,dat19,dat20,dat21,dat22,dat23,dat24,dat25,dat26,dat27,dat28,dat29,dat30,dat31,dat32,dat33,dat34,dat35,dat36,dat37,dat38,dat39,dat40,dat41,dat42,dat43,dat44,dat45,dat46,dat47,dat48,dat49,dat50) VALUES(to_date(:1,'YYYY-MM-DD'), :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54 )
5 ./loadsth:30946 12/18 15:05'20 updaterec:bind=58,sth=1,stmt=UPDATE TICKET.TJRB SET(tjdate,unit,tabname,flg,dat1,dat2,dat3,dat4,dat5,dat6,dat7,dat8,dat9,dat10,dat11,dat12,dat13,dat14,dat15,dat16,dat17,dat18,dat19,dat20,dat21,dat22,dat23,dat24,dat25,dat26,dat27,dat28,dat29,dat30,dat31,dat32,dat33,dat34,dat35,dat36,dat37,dat38,dat39,dat40,dat41,dat42,dat43,dat44,dat45,dat46,dat47,dat48,dat49,dat50)=(SELECT to_date(:1,'YYYY-MM-DD'), :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15, :16, :17, :18, :19, :20, :21, :22, :23, :24, :25, :26, :27, :28, :29, :30, :31, :32, :33, :34, :35, :36, :37, :38, :39, :40, :41, :42, :43, :44, :45, :46, :47, :48, :49, :50, :51, :52, :53, :54 FROM DUAL) WHERE tjdate=to_date(:55,'YYYY-MM-DD') AND unit=:56 AND tabname=:57 AND flg=:58
2 ./loadsth:30946 12/18 15:05'24 loadfile:rows=0,upd=3776,loss=0

不用bind的效果。ldasc是shell，里边换一个程序：
tuxticket@jgbticket:~/test> time ./ldasc >/dev/null <../sql/TJ01.txt

real    0m6.795s
user    0m0.648s
sys     0m0.060s
tuxticket@jgbticket:~/test> time ./ldasc >/dev/null <../sql/TJ01.txt

这个结果比较对劲：

real    0m17.371s
user    0m1.420s
sys     0m0.216s

tuxticket@jgbticket:~/test> time ./ldasc >/dev/null <../sql/TJ01.txt
怎么又快了？可能是buffer的效果？
real    0m4.366s
user    0m1.536s
sys     0m0.164s
tuxticket@jgbticket:~/test> time ./ldasc >/dev/null <../sql/TJ01.txt

real    0m4.277s
user    0m1.396s
sys     0m0.204s

[ 本帖最后由 yulihua49 于 2008-12-18 15:21 编辑 ]

yulihua49

用bind的程序：
tuxticket@jgbticket:~/test> time ./ldasc >/dev/null <../sql/TJ01.txt

real    0m1.239s
user    0m0.440s
sys     0m0.120s
tuxticket@jgbticket:~/test> time ./ldasc >/dev/null <../sql/TJ01.txt

real    0m3.714s
user    0m0.976s
sys     0m0.212s
tuxticket@jgbticket:~/test> time ./ldasc >/dev/null <../sql/TJ01.txt

real    0m3.229s
user    0m0.756s
sys     0m0.372s

第一次是truncate以后的插入
第二次是修改
第三次还是修改，快了一点，可能是buffer的作用

结论：与SQL*loader相比，模板系统开销稍大，这3700个记录共多花费user时间约0.5-1秒，但它带来的程序便利性、可维护性是值得的。尤其写这种通用软件，如果你用PL/SQL写，非要高超技巧不可。
上述程序，loadfile是应用逻辑，insrec()和updaterec()是访问逻辑（DAO）。
单看loadfile，是不是逻辑很清晰易懂？对程序员降低了要求？

[ 本帖最后由 yulihua49 于 2008-12-18 15:47 编辑 ]

ccecmxl

大体看完了楼主和newkid的高论，感觉楼主被“坑”了，楼主的意思是不用存储过程，用自己写的程序包，感觉相当于自己写了一个pl/sql的引擎。于是就变成了

楼主自己开发的引擎好用，还是oracle自己开发的好用，这点好像没什么争论，我更

倾向于oracle方面，毕竟是他们自己做的系统，各种资源的调配更得心应手。newkid

的意思是如果oracle提供给我们的工具，尽量去用，要充分发挥数据库的优势

(oracle有些方面优势很明显)。而不要把oracle的已经实现的功能自己再实现一遍，

这往往会适得其反。这也是tom的观点（tom应该比与楼主公司交流

的oracle技术人员更权威）。然后希望itpub上这种帖子越来越多。感谢二位提供给我们这么好的学习机会。

[ 本帖最后由 ccecmxl 于 2008-12-18 16:22 编辑 ]