看我做的数据库包装器

〇〇 · 发表于 2009-7-9 08:17

原帖由 newkid 于 2009-7-8 22:41 发表

你觉得是OLAP? 你这理解可够独特！去这里看看，哪里能对上号？
http://en.wikipedia.org/wiki/OLAP

无非就是一个批量事务而已。

我不知道现在的大学教育发生了什么变化，当年我毕业时已经能够写复杂的C程序，但是对SQL几乎一无所知。因此你的比赛条件也是不公平的。

10年前我们的一个系统，要求类似http://www.itpub.net/thread-1177956-1-1.html
用proc汇总和制表，据说当时测试比SQL快。
而到目前我还未看到这种案例，测试了全是SQL快。
http://www.itpub.net/thread-1186573-1-1.html（数据量偏少）

yulihua49_cu · 发表于 2009-7-9 11:55

原帖由〇〇于 2009-7-9 08:17 发表

10年前我们的一个系统，要求类似http://www.itpub.net/thread-1177956-1-1.html
用proc汇总和制表，据说当时测试比SQL快。
而到目前我还未看到这种案例，测试了全是SQL快。
http://www.itpub.net/thread-1186573-1-1.html（数据量偏少）

改一下，用你的过程生成 tcol100  表。

用DAU做的，插入20000条：

tuxticket@jgbticket:~/test> time ./test1 ld.ini

real 0m3.905s
user 0m1.240s
sys    0m0.400s

看到你用PL/SQL的插入时间，没看到你用PRO*C的，也没看到OCI的。
要想快，打开游标，bind变量：

=INSERT INTO TICKET.tcol100 (x1,v1,v2,v3,v4,v5,v6,v7,v8,v9,v10,v11,v12,v13,v14,v15,v16,v17,v18,v19,v20,v21,v22,
v23,v24,v25,v26,v27,v28,v29,v30,v31,v32,v33,v34,v35,v36,v37,v38,v39,v40,v41,v42,v43,v44,v45,v46,v47,v48,v49,v50,
v51,v52,v53,v54,v55,v56,v57,v58,v59,v60,v61,v62,v63,v64,v65,v66,v67,v68,v69,v70,v71,v72,v73,v74,v75,v76,v77,v78,v
79,v80,v81,v82,v83,v84,v85,v86,v87,v88,v89,v90,v91,v92,v93,v94,v95,v96,v97,v98,v99,v100)
VALUES ( :1, :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, :55, :56, :57, :58, :59, :60, :61, :62, :63, :64, :65, :66, :67, :68, :69, :70, :71, :72, :73, :74, :75,
:76, :77, :78, :79, :80, :81, :82, :83, :84, :85, :86, :87, :88, :89, :90, :91, :92, :93, :94, :95, :96, :97, :98, :99, :100, :101)

SQL> select count(*) from tcol100;

  COUNT(*)
----------
   20000

程序：

#include <DAU_utility.h>
main(int argc,char *argv[])
{
DAU _DAU;
int ret,i;
T_SQL_Connect sql;
char buf[1000];
if(argc>1) envcfg(argv[1]);
ret=db_open(&sql);
if(ret) return 1;
ret=DAU_init(&_DAU,&sql,"tcol100",0,0);
if(ret) {
printf("DAU_init fail!\n");
___SQL_CloseDatabase__(&sql);
return 2;
}
for(i=0;i<20000;i++) {
sprintf(buf,"%d",i);
for(j=0;j<=100;j++) DAU_putOne(&_DAU,buf,j);
ret=DAU_insert(&_DAU,buf);
}
___SQL_Transaction__(&sql,TRANCOMMIT);
DAU_free(&_DAU);
___SQL_CloseDatabase__(&sql);
return 0;
}

复制代码

你的是动态列数，100个V。
我前边贴的TJ01表，54列插入7552行1.4秒左右，20000行应该在5秒以内，供参考。我那表还有一个主键，要影响插入速度的。

[ 本帖最后由 yulihua49_cu 于 2009-7-10 14:38 编辑 ]

〇〇 · 发表于 2009-7-9 14:23

sqlldr 2列和100列的测试结果
D:\app\pc\instantclient_11_1>sdk\demo\ociuldr2 lt/lt@orcl query="select v1,v2 from tcol100 " file=2w.txt array=1200 table=t2w

   0 rows exported at 2009-07-09 13:38:34
20000 rows exported at 2009-07-09 13:38:34
      output file 2w.txt closed at 20000 rows.

0.063000 seconds
sum(v1+v2)=936840569026.000000

D:\app\pc\instantclient_11_1>sqlplus lt/lt@orcl

SQL*Plus: Release 11.1.0.7.0 - Production on 星期四 7月 9 13:42:29 2009

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

连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table t2w as select v1,v2 from tcol100 where 1=2;

表已创建。

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

D:\app\pc\instantclient_11_1>timer sqlldr lt/lt@orcl control=t2w_sqlldr.ctl direct=y

Timer 3.01  Copyright (c) 2002-2003 Igor Pavlov  2003-07-10

SQL*Loader: Release 11.1.0.7.0 - Production on 星期四 7月 9 14:11:23 2009

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

加载完成 - 逻辑记录计数 20000。

Kernel Time  =    0.046 = 00:00:00.046 = 4%
User Time =    0.046 = 00:00:00.046 = 4%
Process Time =    0.093 = 00:00:00.093 = 8%
Global Time  =    1.060 = 00:00:01.060 = 100%

D:\app\pc\instantclient_11_1>type t2w_sqlldr.ctl
--
-- Generated by OCIULDR
--
OPTIONS(BINDSIZE=16777216,READSIZE=16777216,ERRORS=-1,ROWS=50000)
LOAD DATA
INFILE '2w.txt' "STR X'0a'"
INSERT INTO TABLE t2w
FIELDS TERMINATED BY X'2c' TRAILING NULLCOLS
(
  V1 CHAR(38),
  V2 CHAR(38)
)
SQL> create table t2w1 as select * from tcol100 where 1=2;

表已创建。

SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开

D:\app\pc\instantclient_11_1>sdk\demo\ociuldr2 lt/lt@orcl query="select * from tcol100 " file=t2w1.txt array=2000 table=t2w1

   0 rows exported at 2009-07-09 14:19:13
20000 rows exported at 2009-07-09 14:19:16
      output file t2w1.txt closed at 20000 rows.

2.901000 seconds
sum(v1+v2)=13614774474919.000000

D:\app\pc\instantclient_11_1>timer sqlldr lt/lt@orcl control=t2w1_sqlldr.ctl direct=y

Timer 3.01  Copyright (c) 2002-2003 Igor Pavlov  2003-07-10

SQL*Loader: Release 11.1.0.7.0 - Production on 星期四 7月 9 14:19:25 2009

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

加载完成 - 逻辑记录计数 20000。

Kernel Time  =    0.109 = 00:00:00.109 = 2%
User Time =    0.639 = 00:00:00.639 =  11%
Process Time =    0.748 = 00:00:00.748 =  13%
Global Time  =    5.445 = 00:00:05.445 = 100%

D:\app\pc\instantclient_11_1>type t2w1_sqlldr.ctl|more
--
-- Generated by OCIULDR
--
OPTIONS(BINDSIZE=16777216,READSIZE=16777216,ERRORS=-1,ROWS=50000)
LOAD DATA
INFILE 't2w1.txt' "STR X'0a'"
INSERT INTO TABLE t2w1
FIELDS TERMINATED BY X'2c' TRAILING NULLCOLS
(
  X1 CHAR(10),
  V1 CHAR(38),
  V2 CHAR(38),
  V3 CHAR(38),
  V4 CHAR(38),
  V5 CHAR(38),
  V6 CHAR(38),
  V7 CHAR(38),
  V8 CHAR(38),
  V9 CHAR(38),
  V10 CHAR(38),
  V11 CHAR(38),
  V12 CHAR(38),
  V13 CHAR(38),
  V14 CHAR(38),
  V15 CHAR(38),

[ 本帖最后由〇〇于 2009-7-9 14:24 编辑 ]

〇〇 · 发表于 2009-7-9 14:48

D:\app\pc\instantclient_11_1>sqlldr lt/lt@orcl control=t2w1_sqlldr.ctl log=t2w1_sqlldr.sql external_table=GENERATE_ONLY

SQL*Loader: Release 11.1.0.7.0 - Production on 星期四 7月 9 14:40:10 2009

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

SQL*Loader-800:  ROWS 已指定并忽略

D:\app\pc\instantclient_11_1>sqlplus lt/lt@orcl

SQL*Plus: Release 11.1.0.7.0 - Production on 星期四 7月 9 14:41:35 2009

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

连接到:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000 AS 'D:\app\pc\instantclient_11_1';

目录已创建。

SQL> CREATE TABLE "SYS_SQLLDR_X_EXT_T2W1"
  2  (
  3 "X1" VARCHAR2(10),
  4 "V1" NUMBER(38,4),
:,
103 "V100" NUMBER(38,4)
104  )
105  ORGANIZATION external
106  (
107 TYPE oracle_loader
108 DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000
109 ACCESS PARAMETERS
110 (
111    RECORDS DELIMITED BY 0x'0A' CHARACTERSET ZHS16GBK
112    BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'t2w1.bad'
113    LOGFILE 't2w1_sqlldr.sql_xt'
114    READSIZE 16777216
115    FIELDS TERMINATED BY 0x'2C' LDRTRIM
116    MISSING FIELD VALUES ARE NULL
117    REJECT ROWS WITH ALL NULL FIELDS
118    (
119       "X1" CHAR(10)
120       TERMINATED BY 0x'2C',
121       "V1" CHAR(38)
122       TERMINATED BY 0x'2C',
123       "V2" CHAR(38)
124       TERMINATED BY 0x'2C',
:
319       "V100" CHAR(38)
320       TERMINATED BY 0x'2C'
321    )
322 )
323 location
324 (
325    't2w1.txt'
326 )
327  )REJECT LIMIT UNLIMITED
328  ;

表已创建。

SQL> set timi on
SQL> select count(*) from SYS_SQLLDR_X_EXT_T2W1;

  COUNT(*)
----------
   20000

已用时间:  00: 00: 01.09
SQL> truncate table t2w1;

表被截断。

已用时间:  00: 00: 00.04
SQL> INSERT /*+ append */ INTO T2W1
  2  (
  3 X1,
  4 V1,
  5 V2,
:
103 V100
104  )
105  SELECT
106 "X1",
107 "V1",
:
206 "V100"
207  FROM "SYS_SQLLDR_X_EXT_T2W1"
208  ;

已创建20000行。

已用时间:  00: 00: 05.13
SQL>

fuxiaoyang13 · 发表于 2009-7-9 15:51

学习！！！！

yulihua49_cu · 发表于 2009-7-10 10:23

原帖由〇〇于 2009-7-9 14:23 发表

学习。
那个数组加载的确比较快。
我可以考虑在DAU中增加数组的处理，技术上没问题。就是，如果数组中一部分数据有问题时如何表达和处理？
再show一个DAU处理聚合函数的例子，就是所谓技巧。

T_PkgType sum_tpl[]={
{CH_DOUBLE,sizeof(double),"sum(v1+v2) s","%.2lf",-1},
{CH_DOUBLE,sizeof(double),"0 beg_v","%.2lf"}, //这两个字段用于bind，并不需要取值。
{CH_DOUBLE,sizeof(double),"0 end_v","%.2lf"},
{-1,0,"tcol100",0}
};
typedef struct {
double sum;
double beg_v;
double end_v;
} sum_stu;
main(int argc,char *argv[])
{
DAU _DAU,sum_DAU;
int ret,i,j;
T_SQL_Connect sql;
char buf[1000];
sum_stu sum;
struct timeval begtime,endtime;
if(argc>1) envcfg(argv[1]);
ret=db_open(&sql);
if(ret) return 1;
DAU_init(&sum_DAU,&sql,0,&sum,sum_tpl);
sum.beg_v=1000;
sum.end_v=2000;
strcpy(buf,"WHERE v1>=:beg_v and v1 <:end_v"); //需要两个成员用来bind。
gettimeofday(&begtime,0);
ret=DAU_select(&sum_DAU,buf,0);
if(ret >0) DAU_next(&sum_DAU);
gettimeofday(&endtime,0);
printf("ret=%d,sum=%lf, TIMEVAL=%ld\n",ret,sum.sum,interval(&begtime,&endtime));
//生成的语句：
SELECT sum(v1+v2) s,0 beg_v,0 end_v FROM TICKET.tcol100 WHERE v1>=:1 and v1 <:2
这两个东西：0 beg_v,0 end_v，与 where 1=1 有异曲同工之妙，全是废话。

复制代码

运行结果;
./test1 ld.ini
ret=1,sum=2999000.000000, TIMEVAL=12344
12.3ms 以后再运行都是11.66ms,就是软解析结果，可以看出，在OLAP中软解析不是很重要。

[ 本帖最后由 yulihua49_cu 于 2009-7-10 11:11 编辑 ]

〇〇 · 发表于 2009-7-10 14:33

我更希望c能产生从基层表产生汇总表的1个或多个sql语句（优化后的），然后sql丢给oracle执行，
比如：
create table lt_679
as
select
substr(a.m4,1,2)dzm ,substr(a.m22,1,2)hy ,
sum (case when substr(b.m22,1,2)='63' then 1 end) cnt_63,
sum (case when substr(b.m22,1,2)='63' then b.m40 end) c2,
sum (case when substr(b.m22,1,2)='63' then c.m11 end) c3,
sum (case when substr(b.m22,1,2)='63' then c.m13 end) c4,
sum (case when substr(b.m22,1,2)='63' then c.m17 end) c5,
sum (case when substr(b.m22,1,2)='63' then c.m18 end) c6,
sum (case when substr(b.m22,1,2)='63' then c.m15 end) c7,
sum (case when substr(b.m22,1,2)='63' then c.m4 end) c8,
sum (case when substr(b.m22,1,2)='65' then 1 end) cnt_65,
sum (case when substr(b.m22,1,2)='65' then b.m40 end) c10,
sum (case when substr(b.m22,1,2)='66' then 1 end) cnt_66,
sum (case when substr(b.m22,1,2)='66' then b.m40 end) c18,
sum (case when substr(b.m22,1,2)='67' then 1 end) cnt_67,
sum (case when substr(b.m22,1,2)='67' then b.m40 end) c29
from
pjpdata.v_8600000002008032207000015 a,
pjpdata.v_8600000002008032207000016 b,
pjpdata.v_8600000002008032207000017 c
where b.m43=a.m1 and substr(b.m22,1,2)in('63','65','66','67') and b.m1=c.m1(+)
group by substr(a.m4,1,2) ,substr(a.m22,1,2);

create or replace function dl2ml(hy in varchar)
return varchar
as
x varchar(2);
begin
select
case
when hy >='01' and hy<= '05' then 1
when hy >='06' and hy<= '11' then 2
when hy >='13' and hy<= '43' then 3
when hy >='44' and hy<= '46' then 4
when hy >='47' and hy<= '50' then 5
when hy >='51' and hy<= '59' then 6
when hy >='60' and hy<= '62' then 7
when hy ='63' or hy = '65' then 8
when hy >='66' and hy<= '67' then 9
when hy >='68' and hy<= '71' then 10
when hy >='72' and hy<= '72' then 11
when hy >='73' and hy<= '74' then 12
when hy >='75' and hy<= '78' then 13
when hy >='79' and hy<= '81' then 14
when hy >='82' and hy<= '83' then 15
when hy >='84' and hy<= '84' then 16
when hy >='85' and hy<= '87' then 17
when hy >='88' and hy<= '92' then 18
when hy >='93' and hy<= '98' then 19
else null
end
into x from dual;
return x;
end;
/

select dl2ml(hy)ml,,sum(cnt_63),sum(cnt_65),sum(cnt_66),sum(cnt_67) from lt_679 group by dl2ml(hy)order by 1;

老大：请把411楼加中间换行，我的ie被撑不下了

yulihua49_cu · 发表于 2009-7-10 14:42

原帖由〇〇于 2009-7-10 14:33 发表
我更希望c能产生从基层表产生汇总表的1个或多个sql语句（优化后的），然后sql丢给oracle执行，
比如：

这是C的拿手戏，君不见几乎所有的编译器都是C写的？
你需要设计一个语法去自动生成语句。我在DAU里不就干的这事？
我们过去的万能统计软件也是，根据条件生成语句。

形如如下的使用方法：

mksql 脚本 | sqlplus xx/xx@xx

我们也用shell程序：

sqlplus xx/x/@xx <<!
SQL语句；
语句；

!
show一个我们过去的统计脚本，没打算让你看懂。看个意思吧。这个脚本在ORACLE和SYBASE上都能执行。是比DAU更高层次的包装。就是前边我跟newkid加载的那个TJ01.txt表。
A1.zdsc：

#newtj3:A1 中铁快运经营收入统计日报表(按收入科目分类)
TABDESC=../libct/dict/tjrb.desc
#表需要转置，分配50X20＝1000个单元：
LABN=1201 #定义变量1201个
0..1200L=0.0 #1201个变量清0
#$GSID是环境变量，总公司的ID
#共20列:
#2列：当日实际完成
51..78L=.,$GSID,A1,102,1&& # WHERE tjdate=today AND unit='00' AND tabname='A1' AND FLAG=102; dat1 .. dat28 放到51 ..78号变量
#4列：月计划来自月计划表：
151..178L=.1,$GSID,A1,2,1&& #收入月计划
#1列：当日日均计划,由月计划算出来
1..28L=151&&L*10.0/0D #每句话计算了28个变量
#3列，完成计划
101..128L=51&&L/1&&L*100.0
#5列，月实际完成,来自月累计
201..228L=.,$GSID,A1,103,1&& #月累计
#6列，完成计划％
251..278L=201&&L/151&&L*10.0
#7列,日均完成
301..328L=201&&L/.,2D
#8列,比日均完成计划
(1..28L!=0.0)?351&&L=301&&L-1&&L*10.0:351&&L=0.0
#9列，比日历进度
501..528L=151&&L*.,2D/31,2D #借用一下
501..528L!=0.0?401&&L=201&&L-501&&L*100.0:401&&L=0.0
#10列，完成日历进度
451..478L=201&&L/501&&L*10.0
#11列，同比完成
1001..1028L=\-1..,$GSID,A1,103,1&& #去年同日月累计
1001..1028L!=0.0?501&&L=201&&L-1001&&L:501&&L=0.0
#12列，同比完成%
551..578L=501&&L/1001&&L*10.0 #?
#13列,年计划?
601..628L=1.1,$GSID,A1,1,1&& #收入年计划
#14列，年实际完成
651..678L=1,$GSID,A1,104,1&& #月初年累计完成数
651..678L=651&&L+201&&L #加上本月累计完成数=当日年累计完成数
#15列，年计划完成%
701..728L=651&&L/601&&L*10.0
#16列，剩余计划
601..628L!=0.0?751&&L=601&&L*10.0-651&&L/10.0:751&&L=0.0
#17列，比日历进度+-
2.31,2D==28.?1199L=365.0:1199L=366.0
1051..1078L=601&&L*100.0*.,3D/1199L #年计划日历进度
(1051..1078L!=0.0)?801&&L=(651&&L-1051&&L)/10.0:801&&L=0.0
#18列，完成日历进度%
(1051..1078L!=0.0)?851&&L=651&&L/1051&&L*1000.0:851&&L=0.0
#19列，同比
1051..1078L=1001&&L+\-1..1,$GSID,A1,104,1&& #去年同日月累计+去年同月年累计=去年同日年累计
1051..1078L!=0.0?901&&L=651&&L-1051&&L:901&&L=0.0
#20列，同比%
951..978L=651&&L/1051&&L*100.0
#全表计算完成，下面根据表格需要进行转置输出
1,6D;Y
6D;Y
for <! @2,@3,
01,51,
02,52,
03,53,
04,54,
05,55,
06,56,
07,57,
08,58,
09,59,
10,60,
11,61,
12,62,
13,63,
14,64,
15,65,
16,66,
17,67,
18,68,
19,69,
20,70,
21,71,
22,72,
23,73,
24,74,
25,75,
26,76,
27,77,
28,78,
!
for <! @1,@4,@5,
,1,2,
1,1,,
2,2,1,
3,2,2,
4,2,1,
5,2,1,
6,,2,
7,1,1,
8,1,1,
9,1,1,
!
@1$@2L;@4
@1$@3L;@5
endfor
endfor
gettab.sh：
export GSID=00
zdsc $ZDDIR/$1.zdsc |getdb $DAY |ctab -html $ZDDIR/$1.ctb >$1.html
^^^^^^^^^^ 就是脚本
usage：gettab.sh A1
生成了A1.html,一个网页表格。

复制代码

http://www.itpub.net/viewthread. ... ;extra=&page=11
105楼，有个tool.txt，你拿去看，看完了，上边的脚本就懂了。

如果说DAU只是部分的隐藏了SQL语句，统计脚本早就把SQL扔到爪哇国去了，但他的内核的确是SYBASE或ORACLE。
脚本完全由统计人员写成，他们不懂SQL，也不清楚数据结构。他们知道的就是表名（可不是数据库表名），项号，统计口径。。。。。
当初是INFORMIX数据库，后来是ORACLE，后来有了SYBASE版本，什么都啃一口，什么都没消化，还搞得一帮统计人员，就会写脚本，不懂数据库。

[ 本帖最后由 yulihua49_cu 于 2009-7-10 16:16 编辑 ]

newkid · 发表于 2009-7-11 04:58

原帖由 yulihua49_cu 于 2009-7-10 10:23 发表

学习。
那个数组加载的确比较快。
我可以考虑在DAU中增加数组的处理，技术上没问题。就是，如果数组中一部分数据有问题时如何表达和处理？
再show一个DAU处理聚合函数的例子，就是所谓技巧。

嗯？我去年就给了你批量绑定的例子，怎么不见你学习？
http://www.itpub.net/viewthread.php?tid=1102036
PL/SQL里面有SAVE EXCEPTIONS可以跳过错误纪录，C里面肯定有类似的。

yulihua49 · 发表于 2009-7-12 09:36

原帖由 newkid 于 2009-7-11 04:58 发表

嗯？我去年就给了你批量绑定的例子，怎么不见你学习？
http://www.itpub.net/viewthread.php?tid=1102036
PL/SQL里面有SAVE EXCEPTIONS可以跳过错误纪录，C里面肯定有类似的。

你的观点我一直在学习。你给的OTN那个帖子：
http://forums.oracle.com/forums/ ... 165968&#2165968
其中：
long emp_no[MAX_RECORD_INSERT];
char emp_name[MAX_RECORD_INSERT][256];

大概就是存储错误信息的，没发现在哪使用。关键的：
if (p_sl_record_count != MAX_RECORD_INSERT)
{
Error
}
没说如何使用这两个变量。
不过问题不大，我可以再找找其它资料。这问题有点太OLAP了，如果DAU要支持的话要添加大量参数，
使大量OPTP调用复杂化，它们即使不使用这些参数，也必须赋值。这点C++会好些。

[ 本帖最后由 yulihua49 于 2009-7-12 09:42 编辑 ]

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

sqlldr 创建外部表的