SQL.ProC.OCI速度比较

〇〇

准备工作中
1.数据源
SQL> create procedure createtab(colc IN NUMBER,PP IN NUMBER:=15,SS IN NUMBER:=2)
  2  AUTHID CURRENT_USER
  3  is
  4  buf varchar(24000):='create table tcol'||to_char(colc)||'(x1 varchar(10),';
  5  begin
  6  for i in 1..colc-1
  7  loop
  8  buf:=buf||'v'||to_char(i)||' number('||to_char(PP)||','||to_char(SS)||'),';
  9  end loop;
10  buf:=buf||'v'||to_char(colc)||' number('||to_char(PP)||','||to_char(SS)||'))';
11  execute immediate buf;
12  end;
13  /

过程已创建。

SQL> -- inserttab插入一个含有指定数值型列的表，插入的行数可以指定，数值是随机的
SQL> create procedure inserttab(rowc NUMBER,colc NUMBER)
  2  AUTHID CURRENT_USER
  3  is
  4  buf varchar(24000):='insert into tcol'||to_char(colc)||' select level,';
  5  begin
  6  for i in 1..colc-1
  7  loop
  8  buf:=buf||'mod(level*DBMS_RANDOM.RANDOM,1e10),';
  9  end loop;
10  buf:=buf||'mod(level*DBMS_RANDOM.RANDOM,1e10) from dual connect by level<='||to_char(rowc);
11  execute immediate buf;
12  end;
13  /

过程已创建。

SQL> set timi on
SQL> exec createtab(100); --100列

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.04

SQL> exec inserttab(20000,100); -2万行

PL/SQL 过程已成功完成。

已用时间:  00: 00: 17.92
2.测试语句
SQL> col s format 99999999999999.9999999
SQL> select sum(v1+v2) s from tcol100;

                      S
-----------------------
   -44907785471.0000000

已用时间:  00: 00: 00.01

#include <stdio.h>
#include <sqlca.h>
#include <time.h>

void sqlerror();

EXEC SQL BEGIN DECLARE SECTION;
char *connstr = "lt/lt@orcl";
char v_x1[30];
double v_v1=0.0;
double v_v2=0.0;
double v_sum=0.0;
long start=0L;
long finish=0L;
EXEC SQL END DECLARE SECTION;

void main() {
EXEC SQL WHENEVER SQLERROR DO sqlerror();
EXEC SQL WHENEVER SQLWARNING CONTINUE;
EXEC SQL CONNECT :connstr;

EXEC SQL DECLARE lt_cursor CURSOR FOR
        SELECT /*x1, */v1,v2
        FROM   tcol100
        ;

EXEC SQL OPEN lt_cursor;
start = clock();  

EXEC SQL WHENEVER NOT FOUND DO break;
for (;;) {
EXEC SQL FETCH lt_cursor INTO /* :v_x1 , */:v_v1, :v_v2;
v_sum+=v_v1+v_v2;
}
finish = clock();  
  
printf( "%f seconds\n", (double)(finish - start) / CLOCKS_PER_SEC );  

printf("sum(v1+v2)=%lf\n", v_sum);
EXEC SQL CLOSE lt_cursor;
EXEC SQL COMMIT WORK RELEASE;
return;
}

void sqlerror() {
printf("Stop Error:\t%25i\n", sqlca.sqlcode);
EXEC SQL WHENEVER SQLERROR CONTINUE;
printf("%.70s",sqlca.sqlerrm.sqlerrmc);

EXEC SQL ROLLBACK WORK RELEASE;
return;
}
D:\app\pc\instantclient_11_1>..\sum2
0.982000 seconds
sum(v1+v2)=-44907785471.000000
第一次比较proc比sql慢多了

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

〇〇

占楼
下一步准备采取EXEC SQL FOR :array_size FETCH C
                      USING DESCRIPTOR select_dp;
批量获取到数组

由于我的c编译器是vc6,只有32位，而数据库11.1.0.7 for windows x64，所以另外下载了instantclient_11_1，编译一楼的程序没有问题
而本例用到了外部函数
extern SQLDA *sqlald();
extern void   sqlclu();

直接编译报错
D:\app\pc>cl -ID:\app\oracle\product\11.1.0\db_1\oci\include -ID:\app\oracle\product\11.1.0\db_1\precomp\public -I. -ID:\app\pc\vc6\include -D_MT -D_DLL -Zi tom
.c /link D:\app\pc\vc6\lib\oci.lib /libpath:D:\app\oracle\product\11.1.0\db_1\precomp\lib /libpath:D:\app\oracle\product\11.1.0\db_1\precomp\lib\msvc  /LIBPATH:
D:\app\pc\vc6\lib msvcrt.lib /nod:libc
Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8168 for 80x86
Copyright (C) Microsoft Corp 1984-1998. All rights reserved.

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

/out:tom.exe
/debug
D:\app\pc\vc6\lib\oci.lib
/libpath:D:\app\oracle\product\11.1.0\db_1\precomp\lib
/libpath:D:\app\oracle\product\11.1.0\db_1\precomp\lib\msvc
/LIBPATH:D:\app\pc\vc6\lib
msvcrt.lib
/nod:libc
tom.obj
tom.obj : error LNK2001: unresolved external symbol _sqlnul
tom.obj : error LNK2001: unresolved external symbol _sqlclu
tom.obj : error LNK2001: unresolved external symbol _sqlald
tom.exe : fatal error LNK1120: 3 unresolved externals
改用instantclient_11_1sdk和precomp sdk后解决
测试结果：arrarysize的影响还是很明显的，但仍然比SQL慢很多，估计与类型转化有关，数字转字符串，再转双精度浮点数

D:\app\pc\instantclient_11_1>sdk\demo\tom2 userid=lt/lt@orcl sqlstmt="select v1,v2 from tcol100 "  arraysize=100

Connected to ORACLE as user: lt/lt@orcl

Unloading 'select v1,v2 from tcol100 '
Array size = 100
V1,V2
20000 rows extracted
0.078000 seconds
sum(v1+v2)=-44907785471.000000

D:\app\pc\instantclient_11_1>sdk\demo\tom2 userid=lt/lt@orcl sqlstmt="select v1,v2 from tcol100 "  arraysize=10

Connected to ORACLE as user: lt/lt@orcl

Unloading 'select v1,v2 from tcol100 '
Array size = 10
V1,V2
20000 rows extracted
0.234000 seconds
sum(v1+v2)=-44907785471.000000

D:\app\pc\instantclient_11_1>sdk\demo\tom2 userid=lt/lt@orcl sqlstmt="select v1,v2 from tcol100 "  arraysize=1000

Connected to ORACLE as user: lt/lt@orcl

Unloading 'select v1,v2 from tcol100 '
Array size = 1000
V1,V2
20000 rows extracted
0.062000 seconds
sum(v1+v2)=-44907785471.000000

D:\app\pc\instantclient_11_1>sdk\demo\tom2 userid=lt/lt@orcl sqlstmt="select v1,v2 from tcol100 "  arraysize=10000

Connected to ORACLE as user: lt/lt@orcl

Unloading 'select v1,v2 from tcol100 '
Array size = 10000
V1,V2
20000 rows extracted
0.047000 seconds
sum(v1+v2)=-44907785471.000000

D:\app\pc\instantclient_11_1>sdk\demo\tom2 userid=lt/lt@orcl sqlstmt="select v1,v2 from tcol100 "  arraysize=20000

Connected to ORACLE as user: lt/lt@orcl

Unloading 'select v1,v2 from tcol100 '
Array size = 20000
V1,V2
20000 rows extracted
0.031000 seconds
sum(v1+v2)=-44907785471.000000

D:\app\pc\instantclient_11_1>

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

〇〇

占楼
用了dbca版主的优化导出工具作为测试范本,array增大后影响不大是因为
      else if (STRNCASECMP("array=",argv[ i],6)==0)
      {
          memset(tempbuf,0,512);
          memcpy(tempbuf,argv[ i]+6,MIN(strlen(argv[ i]) - 6,254));
          DEFAULT_ARRAY_SIZE = atoi(tempbuf);
          if (DEFAULT_ARRAY_SIZE < 5) DEFAULT_ARRAY_SIZE = 5;
          if (DEFAULT_ARRAY_SIZE > 2000) DEFAULT_ARRAY_SIZE = 2000;
      }

D:\app\pc\instantclient_11_1>sdk\demo\ociuldr2 lt/lt@orcl query="select v1,v2 from tcol100 "  file=nul array=10

       0 rows exported at 2009-07-08 15:57:18
   20000 rows exported at 2009-07-08 15:57:19
         output file nul closed at 20000 rows.

0.249000 seconds
sum(v1+v2)=-44907785471.000000

D:\app\pc\instantclient_11_1>sdk\demo\ociuldr2 lt/lt@orcl query="select v1,v2 from tcol100 "  file=nul array=100

       0 rows exported at 2009-07-08 15:57:29
   20000 rows exported at 2009-07-08 15:57:29
         output file nul closed at 20000 rows.

0.078000 seconds
sum(v1+v2)=-44907785471.000000

D:\app\pc\instantclient_11_1>sdk\demo\ociuldr2 lt/lt@orcl query="select v1,v2 from tcol100 "  file=nul array=1000

       0 rows exported at 2009-07-08 15:57:36
   20000 rows exported at 2009-07-08 15:57:36
         output file nul closed at 20000 rows.

0.062000 seconds
sum(v1+v2)=-44907785471.000000

D:\app\pc\instantclient_11_1>sdk\demo\ociuldr2 lt/lt@orcl query="select v1,v2 from tcol100 "  file=nul array=10000

       0 rows exported at 2009-07-08 15:57:40
   20000 rows exported at 2009-07-08 15:57:41
         output file nul closed at 20000 rows.

0.063000 seconds
sum(v1+v2)=-44907785471.000000

D:\app\pc\instantclient_11_1>sdk\demo\ociuldr2 lt/lt@orcl query="select v1,v2 from tcol100 "  file=nul array=20000

       0 rows exported at 2009-07-08 15:57:46
   20000 rows exported at 2009-07-08 15:57:46
         output file nul closed at 20000 rows.

0.063000 seconds
sum(v1+v2)=-44907785471.000000

[ 本帖最后由 〇〇 于 2009-7-8 16:11 编辑 ]

〇〇

占楼

〇〇

占楼

〇〇

占楼

〇〇

占楼

〇〇

占楼

〇〇

占楼

〇〇

占楼