用过程语言实现group by cube的代码挑战赛

〇〇 · 发表于 2010-3-25 17:03

几种小改进,没有大影响备查

〇〇 · 发表于 2010-3-26 10:19

利用

typedef union {
int i;
struct{
char c4;
char c3;
char c2;
struct
{
unsigned char c1:4;
unsigned char gid:4;
};
};
} u;
case FIELD_SET://设置列数组每行中的元素
for(j=0;j<MAXCOL;j++)
{
switch(j)
{
case 0:cvalp=(ub1*)&c1[curr_row];clen=sizeof(char);if(*cvalp==15)cflg=OCI_DIRPATH_COL_NULL;break;
case 1:cvalp=(ub1*)&c2[curr_row];if(*cvalp==127)cflg=OCI_DIRPATH_COL_NULL;break;
case 2:cvalp=(ub1*)&c3[curr_row];if(*cvalp==127)cflg=OCI_DIRPATH_COL_NULL;break;
case 3:cvalp=(ub1*)&c4[curr_row];if(*cvalp==127)cflg=OCI_DIRPATH_COL_NULL;break;
case 4:cvalp=(ub1*)&C[curr_row];clen=sizeof(int);break;
case 6:cvalp=(ub1*)&gid[curr_row];clen=sizeof(char);break;
case 5:
cvalp=(ub1*)sv+curr_row*14;
clen=strlen((char*)sv+curr_row*14);
break;
}

复制代码

改为

for(j=0;j<MAXCOL;j++)
{
switch(j)
{
case 0:cvalp=(ub1*)&c1[curr_row];clen=sizeof(char);if(*cvalp==15)cflg=OCI_DIRPATH_COL_NULL;break; //error C2104: '&' on bit field ignored
case 1:cvalp=(ub1*)&((I+curr_row)->c2);if(*cvalp==127)cflg=OCI_DIRPATH_COL_NULL;break;
case 2:cvalp=(ub1*)&((I+curr_row)->c3);if(*cvalp==127)cflg=OCI_DIRPATH_COL_NULL;break;
case 3:cvalp=(ub1*)&((I+curr_row)->c4);if(*cvalp==127)cflg=OCI_DIRPATH_COL_NULL;break;
case 4:cvalp=(ub1*)&C[curr_row];clen=sizeof(int);break;
case 6:cvalp=(ub1*)&gid[curr_row];clen=sizeof(char);break;
case 5:
cvalp=(ub1*)sv+curr_row*14;
clen=strlen((char*)sv+curr_row*14);
break;
}

复制代码

减少了内存的复制,对速度没有提高

〇〇 · 发表于 2010-3-26 10:24

let的主要时间用在
sprintf(sv+i*14,"%I64d",*((__int64*)v2+y));
听说11g的oci增加了64位int结构，就不用这么麻烦了

〇〇 · 发表于 2010-3-26 10:29

记错了，应该是11.2
1.1.2.6 OCI Support for 8-Byte Integer Bind/Define
Oracle Call Interface (OCI) now provides support for 8-byte integer bind/define on 32-bit and 64-bit platforms.

Fusion applications need Pro*C or Pro*COBOL to be able to support 8-byte native data type for bind/define while inserting or fetching data to and from a NUMBER(18) column. Pro*C or Pro*COBOL need this support from OCI to be able to pass it on to application developers.

64-bit Integer Host Datatype
Starting with release 11.2, OCI supports the ability to bind and define integer values greater than 32-bit size (more than 9 digits of precision) from and into a NUMBER column using a 64-bit native host variable and SQLT_INT or SQLT_UIN as the external datatype in an OCI application.

This enables an application to bind and define 8-byte native host variables using SQLT_INT or SQLT_UIN external datatypes in the OCI bind and define function calls on all platforms. The "OCIDefineByPos()", "OCIBindByName()", and "OCIBindByPos()" function calls can specify an 8-byte integer datatype pointer as the valuep parameter. This enables you to insert and fetch large integer values (up to 18 decimal digits of precision) directly into and from native host variables and to perform free arithmetic on them.

OCI Bind and Define for 64-bit Integers
The following snippet will work without errors:

...
/* Variable declarations */
orasb8 sbigval1, sbigval2, sbigval3; // Signed 8-byte variables.
oraub8 ubigval1, ubigval2, ubigval3; // Unsigned 8-byte variables.
...
/* Bind Statements */
OCIBindByPos(..., (void *) &sbigval1, sizeof(sbigval1), ..., SQLT_INT, ...);
OCIBindByPos(..., (void *) &ubigval1, sizeof(ubigval1), ..., SQLT_UIN, ...);
OCIBindByName(...,(void *) &sbigval2, sizeof(sbigval2), ..., SQLT_INT, ...);
OCIBindByName(...,(void *) &ubigval2, sizeof(ubigval2), ..., SQLT_UIN, ...);
...
/* Define Statements */
OCIDefineByPos(..., (void *) &sbigval3, sizeof(sbigval3), ..., SQLT_INT, ...);
OCIDefineByPos(..., (void *) &ubigval3, sizeof(ubigval3), ..., SQLT_UIN, ...);
...

〇〇 · 发表于 2010-3-26 10:33

把sv字符串设为NULL,节省了大部分时间
case 5:
cvalp=(ub1*)sv+curr_row*14;
clen=strlen((char*)sv+curr_row*14);cflg=OCI_DIRPATH_COL_NULL;

Initializing the environment..
Logged on as LT
col0:c1 3    1
col1:c2 3    1
col2:c3 3    1
col3:c4 3    1
col4:cv 3    4
col5:sv 1    14
col6:gid       3    1
time=296ms
col_ary_rows=4096,col_ary_cols=7
1000all time=2340ms
插入结果表1000000行
malloc time=0ms
let time=421ms
col0:c1 3    1
col1:c2 3    1
col2:c3 3    1
col3:c4 3    1
col4:cv 3    4
col5:sv 1    14
col6:gid       3    1
time=421ms
col_ary_rows=4096,col_ary_cols=7
600all time=3759ms
插入结果表1299600行

〇〇 · 发表于 2010-4-7 09:33

11gr2测试

C:\Documents and Settings\Administrator>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 4月 6 16:56:53 2010

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

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

SQL> create user lt identified by LT default tablespace users;

用户已创建。

SQL> grant create session ,dba to lt;

授权成功。

SQL> conn lt/LT
已连接。
SQL> set lines 132 pages 50000
SQL> create table t(c1 number(2,0),c2 number(2,0),c3 number(3,0),c4 number(3,0),v number(12,0))compress;

表已创建。

SQL> set timi on
SQL> insert into t select  mod(level,4)c1,mod(level,5)c2,mod(level,67)c3,
  2  mod(case when mod(level,13)>0 then level end ,113)c4, level v from dual connect by level<=1E6;

已创建1000000行。

已用时间:  00: 00: 15.20
SQL> select sum(bytes)/power(2,20) from user_segments where segment_name='T';

SUM(BYTES)/POWER(2,20)
----------------------
                  22

已用时间:  00: 00: 00.29
SQL> create table tcube (c1 number(2,0),c2 number(2,0),c3 number(3,0),c4 number(3,0),sv number(18,0),cv number(12,0),gid number(2,0))compres
s;

表已创建。

已用时间:  00: 00: 00.04
SQL> insert into tcube select c1,c2,c3,c4,sum(v)sv,count(v)cv,grouping_id(c1,c2,c3,c4)gid from t group by cube(c1,c2,c3,c4);

已创建234600行。

已用时间:  00: 00: 32.11
SQL> commit;

提交完成。

已用时间:  00: 00: 00.01
SQL> create table yourcube (c1 number(2,0),c2 number(2,0),c3 number(3,0),c4 number(3,0),sv number(18,0),cv number(12,0),gid number(2,0))comp
ress;

表已创建。

已用时间:  00: 00: 00.06

D:\app>cl /O2 mapc4chd4.cpp /link oci.lib
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8168 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.

mapc4chd4.cpp
Microsoft (R) Incremental Linker Version 6.00.8168
Copyright (C) Microsoft Corp 1992-1998. All rights reserved.

/out:mapc4chd4.exe
oci.lib
mapc4chd4.obj

D:\app>mapc4chd4 LT/LT@10.6.99.65/orcl query="select * from t" file=NUL
0,1    1,1    2,1    3,1    4,4
   0 rows exported at 2010-04-07 09:26:42
recn=0
  500000 rows exported at 2010-04-07 09:26:45
recn=500000
1000000 rows exported at 2010-04-07 09:26:47
      output file  closed at 1000000 rows.

sum=1
sum(c1+c2+c3+v)=500036999500
sum(c4)=51690810
从数据库把表读入内存4812ms
4,5,67,114,152760
申请map内存0ms
申请一层内存15ms
新建一层hashmap节点563ms,recn=152760,每毫秒读1776行,每毫秒写271节点
申请后面内存0ms,152760
gid=1 found 0 time
新建后面节点[1],recn=225822
新建后面节点[2],recn=226392
新建后面节点[3],recn=226392
新建后面节点[4],recn=234600
新建后面节点[5],recn=234600
新建后面节点[6],recn=234600
新建后面节点203ms,recn=234600,每毫秒读752行key,每毫秒写403节点
malloc time=0ms
let time=172ms

Initializing the environment..
Logged on as LT
col0:c1 3    1
col1:c2 3    1
col2:c3 3    1
col3:c4 3    1
col4:cv 3    4
col5:sv 1    14
col6:gid       3    1
time=266ms
col_ary_rows=4096,col_ary_cols=7
2760all time=1703ms
插入结果表152760行
malloc time=0ms
let time=109ms
col0:c1 3    1
col1:c2 3    1
col2:c3 3    1
col3:c4 3    1
col4:cv 3    4
col5:sv 1    14
col6:gid       3    1
time=125ms
col_ary_rows=4096,col_ary_cols=7
840all time=938ms
插入结果表81840行
释放所有动态内存0ms,234600
释放所有map内存62ms,234600
总时间3515ms,234600
3步（读入+运算+写出）总时间8359ms

Exiting with SUCCESS status 0

SQL> select * from (select * from tcube minus select * from yourcube);

未选定行

已用时间:  00: 00: 03.17

〇〇 · 发表于 2010-4-7 09:45

SQL> truncate table t;

表被截断。

已用时间:  00: 00: 00.14
SQL> truncate table tcube;

表被截断。

已用时间:  00: 00: 00.14
SQL> truncate table yourcube;

表被截断。

已用时间:  00: 00: 00.09
SQL> insert into t select  mod(level,9)c1,mod(level,97)c2,mod(level,119)c3,
  2  mod(case when mod(level,13)>0 then level end ,113)c4, level v from dual connect by level<=1E6;

已创建1000000行。

已用时间:  00: 00: 13.15
SQL> commit;

提交完成。

已用时间:  00: 00: 00.01
SQL> insert into tcube select c1,c2,c3,c4,sum(v)sv,count(v)cv,grouping_id(c1,c2,c3,c4)gid from t group by cube(c1,c2,c3,c4);

已创建2299600行。

已用时间:  00: 01: 58.71
SQL>
SQL> commit;

提交完成。

已用时间:  00: 00: 00.00

D:\app>mapc4chd4 LT/LT@10.6.99.65/orcl query="select * from t" file=NUL
0,1    1,1    2,1    3,1    4,4
   0 rows exported at 2010-04-07 09:44:36
recn=0
  500000 rows exported at 2010-04-07 09:44:37
recn=500000
1000000 rows exported at 2010-04-07 09:44:39
      output file  closed at 1000000 rows.

sum=1
sum(c1+c2+c3+v)=500111497488
sum(c4)=51690810
从数据库把表读入内存4062ms
4,5,67,114,152760
申请map内存0ms
申请一层内存31ms
新建一层hashmap节点1047ms,recn=1000000,每毫秒读955行,每毫秒写955节点
申请后面内存31ms,1000000
realloc2000000
gid=1 found 0 time
新建后面节点[1],recn=2273836
新建后面节点[2],recn=2284894
新建后面节点[3],recn=2284894
新建后面节点[4],recn=2299600
新建后面节点[5],recn=2299600
新建后面节点[6],recn=2299600
新建后面节点3219ms,recn=2299600,每毫秒读310行key,每毫秒写403节点
malloc time=0ms
let time=969ms

Initializing the environment..
Logged on as LT
col0:c1 3    1
col1:c2 3    1
col2:c3 3    1
col3:c4 3    1
col4:cv 3    4
col5:sv 1    14
col6:gid       3    1
time=1157ms
col_ary_rows=4096,col_ary_cols=7
1000all time=10938ms
插入结果表1000000行
malloc time=0ms
let time=1328ms
col0:c1 3    1
col1:c2 3    1
col2:c3 3    1
col3:c4 3    1
col4:cv 3    4
col5:sv 1    14
col6:gid       3    1
time=1359ms
col_ary_rows=4096,col_ary_cols=7
600all time=14546ms
插入结果表1299600行
释放所有动态内存15ms,2299600
释放所有map内存500ms,2299600
总时间30406ms,2299600
3步（读入+运算+写出）总时间34515ms

Exiting with SUCCESS status 0

D:\app>

SQL> select * from (select * from tcube minus select * from yourcube);

未选定行

已用时间:  00: 00: 44.06

〇〇 · 发表于 2010-4-7 09:51

常规路径数组批量加载
D:\app>cl /O2 mapc4chd5.cpp /link oci.lib
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8168 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.

mapc4chd5.cpp
Microsoft (R) Incremental Linker Version 6.00.8168
Copyright (C) Microsoft Corp 1992-1998. All rights reserved.

/out:mapc4chd5.exe
oci.lib
mapc4chd5.obj

D:\app>mapc4chd5 LT/LT@10.6.99.65/orcl query="select * from t" file=NUL
0,1    1,1    2,1    3,1    4,4
   0 rows exported at 2010-04-07 09:51:34
recn=0
  500000 rows exported at 2010-04-07 09:51:36
recn=500000
1000000 rows exported at 2010-04-07 09:51:37
      output file  closed at 1000000 rows.

sum=1
sum(c1+c2+c3+v)=500111497488
sum(c4)=51690810
从数据库把表读入内存3500ms
4,5,67,114,152760
申请map内存0ms
申请一层内存16ms
新建一层hashmap节点1031ms,recn=1000000,每毫秒读969行,每毫秒写969节点
申请后面内存31ms,1000000
realloc2000000
gid=1 found 0 time
新建后面节点[1],recn=2273836
新建后面节点[2],recn=2284894
新建后面节点[3],recn=2284894
新建后面节点[4],recn=2299600
新建后面节点[5],recn=2299600
新建后面节点[6],recn=2299600
新建后面节点3110ms,recn=2299600,每毫秒读321行key,每毫秒写417节点

Total number of rows to be inserted is 1000000
插入结果表time=9390

Total number of rows to be inserted is 1299600
插入结果表time=14032
释放所有动态内存0ms,2299600
释放所有map内存438ms,2299600
总时间28078ms,2299600
3步（读入+运算+写出）总时间31625ms

Exiting with SUCCESS status 0

〇〇 · 发表于 2010-4-7 15:29

利用11gr2新特性
http://download.oracle.com/docs/ ... i03typ.htm#LNOCI039
64-bit Integer Host Datatype
Starting with release 11.2, OCI supports the ability to bind and define integer values greater than 32-bit size (more than 9 digits of precision) from and into a NUMBER column using a 64-bit native host variable and SQLT_INT or SQLT_UIN as the external datatype in an OCI application.

This enables an application to bind and define 8-byte native host variables using SQLT_INT or SQLT_UIN external datatypes in the OCI bind and define function calls on all platforms. The "OCIDefineByPos()", "OCIBindByName()", and "OCIBindByPos()" function calls can specify an 8-byte integer datatype pointer as the valuep parameter. This enables you to insert and fetch large integer values (up to 18 decimal digits of precision) directly into and from native host variables and to perform free arithmetic on them.

OCI Bind and Define for 64-bit Integers
The following snippet will work without errors:

...
/* Variable declarations */
orasb8 sbigval1, sbigval2, sbigval3; // Signed 8-byte variables.
oraub8 ubigval1, ubigval2, ubigval3; // Unsigned 8-byte variables.
...
/* Bind Statements */
OCIBindByPos(..., (void *) &sbigval1, sizeof(sbigval1), ..., SQLT_INT, ...);
OCIBindByPos(..., (void *) &ubigval1, sizeof(ubigval1), ..., SQLT_UIN, ...);
OCIBindByName(...,(void *) &sbigval2, sizeof(sbigval2), ..., SQLT_INT, ...);
OCIBindByName(...,(void *) &ubigval2, sizeof(ubigval2), ..., SQLT_UIN, ...);
...
/* Define Statements */
OCIDefineByPos(..., (void *) &sbigval3, sizeof(sbigval3), ..., SQLT_INT, ...);
OCIDefineByPos(..., (void *) &ubigval3, sizeof(ubigval3), ..., SQLT_UIN, ...);

复制代码

把大整数用__int64表示，加载速度提高了

D:\app>mc LT/LT@10.6.99.65/orcl query="select * from t" file=NUL
0,1    1,1    2,1    3,1    4,4
   0 rows exported at 2010-04-07 15:14:22
recn=0
  500000 rows exported at 2010-04-07 15:14:23
recn=500000
1000000 rows exported at 2010-04-07 15:14:25
      output file  closed at 1000000 rows.

sum=1
sum(c1+c2+c3+v)=500111497488
sum(c4)=51690810
从数据库把表读入内存4828ms
4,5,67,114,152760
申请map内存0ms
申请一层内存32ms
新建一层hashmap节点1062ms,recn=1000000,每毫秒读941行,每毫秒写941节点
申请后面内存31ms,1000000
realloc2000000
gid=1 found 0 time
新建后面节点[1],recn=2273836
新建后面节点[2],recn=2284894
新建后面节点[3],recn=2284894
新建后面节点[4],recn=2299600
新建后面节点[5],recn=2299600
新建后面节点[6],recn=2299600
新建后面节点3203ms,recn=2299600,每毫秒读312行key,每毫秒写405节点
malloc time=0ms
let time=1000ms

Initializing the environment..
Logged on as LT
col0:c1 3    1
col1:c2 3    1
col2:c3 3    1
col3:c4 3    1
col4:cv 3    4
col5:sv 1    14
col6:gid       3    1
time=1141ms
col_ary_rows=4096,col_ary_cols=7
1000all time=9969ms
插入结果表1000000行
malloc time=0ms
let time=1328ms
col0:c1 3    1
col1:c2 3    1
col2:c3 3    1
col3:c4 3    1
col4:cv 3    4
col5:sv 1    14
col6:gid       3    1
time=1344ms
col_ary_rows=4096,col_ary_cols=7
600all time=13047ms
插入结果表1299600行
释放所有动态内存0ms,2299600
释放所有map内存421ms,2299600
总时间27844ms,2299600
3步（读入+运算+写出）总时间32734ms

Exiting with SUCCESS status 0

========〉

D:\app>mc8 LT/LT@10.6.99.65/orcl query="select * from t" file=NUL
0,1    1,1    2,1    3,1    4,4
   0 rows exported at 2010-04-07 15:26:36
recn=0
  500000 rows exported at 2010-04-07 15:26:38
recn=500000
1000000 rows exported at 2010-04-07 15:26:39
      output file  closed at 1000000 rows.

sum=1
sum(c1+c2+c3+v)=500111497488
sum(c4)=51690810
从数据库把表读入内存3578ms
4,5,67,114,152760
申请map内存0ms
申请一层内存16ms
新建一层hashmap节点1031ms,recn=1000000,每毫秒读969行,每毫秒写969节点
申请后面内存16ms,1000000
realloc2000000
gid=1 found 0 time
新建后面节点[1],recn=2273836
新建后面节点[2],recn=2284894
新建后面节点[3],recn=2284894
新建后面节点[4],recn=2299600
新建后面节点[5],recn=2299600
新建后面节点[6],recn=2299600
新建后面节点3187ms,recn=2299600,每毫秒读313行key,每毫秒写407节点
malloc time=0ms
let time=32ms

Initializing the environment..
Logged on as LT
col0:c1 3    1
col1:c2 3    1
col2:c3 3    1
col3:c4 3    1
col4:cv 3    4
col5:sv 3    8
col6:gid       3    1
time=141ms
col_ary_rows=4096,col_ary_cols=7
1000all time=8922ms
插入结果表1000000行
malloc time=0ms
let time=47ms
col0:c1 3    1
col1:c2 3    1
col2:c3 3    1
col3:c4 3    1
col4:cv 3    4
col5:sv 3    8
col6:gid       3    1
time=78ms
col_ary_rows=4096,col_ary_cols=7
600all time=12328ms
插入结果表1299600行
释放所有动态内存16ms,2299600
释放所有map内存421ms,2299600
总时间25968ms,2299600
3步（读入+运算+写出）总时间29593ms

Exiting with SUCCESS status 0

D:\app>

〇〇 · 发表于 2010-4-7 16:07

mc str
mc8 int64 (需要11gr2的lib和dll)

[ 本帖最后由〇〇于 2010-4-7 16:10 编辑 ]

[有奖问答] 用过程语言实现group by cube的代码挑战赛

回复 #133 〇〇的帖子

[有奖问答] 用过程语言实现group by cube的代码挑战赛

回复 #133 〇〇 的帖子

回复 #133 〇〇的帖子