oracle 替代方案,oracle – PL/SQL或替代方案的数值优化

我们需要做一些计算繁重的工作来连接Oracle数据库.到目前为止,我们已经在PL / sql中进行了数值计算,并且很大程度上缺乏性能.

我用三种语言实现了部分算法：Fortran(90-2008符合gfortran),Excel中的VBA和PL / sql,并围绕它进行了一百万次调用测试循环.即使使用binary_double数据类型和使用PLsql_CODE_TYPE = NATIVE的本机编译(两者都会导致改进),下面的测试代码仍然需要37秒才能运行(Oracle XE 11.2).相比之下,VBA在同一硬件上占用16s和Fortran 1.6s.

虽然要求性能接近Fortran数字可能太过分了(虽然这显然是非常需要的)但我很惊讶即使是不起眼的VBA也会执行PL / sql.

所以我的问题有两个部分：

>我还可以尝试在Oracle方面进一步提高性能吗？

>如果我们考虑放弃PL / sql,我们应该考虑哪些替代方案来连接Oracle数据库？虽然接近Fortran速度的性能提升将使我们考虑在应用程序的交互式部分中引入一些繁重的工作,但是繁重的工作将以类似批处理的方式被称为相对较少.我首选的数值工作语言仍然是Fortran,因为它易于实现带有屏蔽的多维数组.

此外,虽然我不是直接批评我的源代码本身,但如果有人能发现任何我可以合并的明显优化,我将不胜感激.

函数timeebo是测试函数,我用双精度简单的selectebo调用它;在sql Developer中.

create or replace FUNCTION gammln(

x IN binary_double)

RETURN binary_double

IS

--Lanczos' approximation to the Log Gamma function

gammln binary_double;

ser binary_double;

tmp binary_double;

BEGIN

tmp := x + 5.5;

tmp :=(x + 0.5) * Ln(tmp) - tmp;

ser := 1.000000000190015;

ser := ser + 76.18009172947146 /(x + 1.0) ;

ser := ser - 86.50532032941677 /(x + 2.0) ;

ser := ser + 24.01409824083091 /(x + 3.0) ;

ser := ser - 1.231739572450155 /(x + 4.0) ;

ser := ser + 1.208650973866179E-03 /(x + 5.0) ;

ser := ser - 5.395239384953E-06 /(x + 6.0) ;

RETURN tmp + Ln(2.5066282746310005 * ser / x) ;

END;

/

CREATE OR REPLACE FUNCTION PoissonDist(

k IN INTEGER,lambda IN binary_double)

RETURN binary_double

IS

BEGIN

RETURN Exp((k * Ln(lambda)) - lambda - gammln(k + 1)) ;

END;

/

CREATE OR REPLACE FUNCTION EBO(

stock IN pls_integer,pipeline IN binary_double,accuracy IN binary_double DEFAULT 0.0000000001)

RETURN binary_double

IS

i pls_integer;

EBO binary_double;

term binary_double;

temp binary_double;

PoissonVal binary_double;

peaked BOOLEAN; --Flag the Poisson curve as having peaked

BEGIN

EBO := 0.0;

IF(pipeline = 0.0) THEN

RETURN EBO;

END IF;

--Initialise

i := 1;

peaked := false;

PoissonVal := PoissonDist(stock + 1,pipeline) ; --Get p() value

IF(PoissonVal < accuracy AND floor(pipeline) > stock) THEN --If p() is very

-- small...

i := floor(pipeline) - stock; --Revise i to just below peak of Poisson curve

PoissonVal := PoissonDist(stock + i,pipeline) ; --Get p() value close to

-- peak

temp := PoissonVal *(pipeline / CAST(stock + i + 1 AS binary_double)) ; --

-- Store poisson value just above peak

LOOP

term := CAST(i AS binary_double) * PoissonVal;

EBO := EBO + term;

i := i - 1; --Work backwards

PoissonVal := PoissonVal *(CAST(stock + i + 1 AS DOUBLE

PRECISION) / pipeline) ; --Revise Poisson

-- value for next time

EXIT

WHEN(term < accuracy OR i = 0) ;

END LOOP;

i := 1 + floor(pipeline) - stock;

PoissonVal := temp;

peaked := true;

END IF;

LOOP

term := CAST(i AS binary_double) * PoissonVal;

EBO := EBO + term;

i := i + 1;

PoissonVal := PoissonVal *(pipeline / CAST(stock + i AS

binary_double)) ; --Revise Poisson value for next time

IF(CAST(stock + i AS binary_double) > pipeline) THEN

peaked := true;

END IF;

EXIT

WHEN(term < accuracy AND peaked) ;

END LOOP;

IF(EBO < accuracy) THEN

EBO := 0.0;

END IF;

RETURN EBO;

END;

/

CREATE OR REPLACE FUNCTION timeebo

RETURN binary_double

IS

i pls_integer;

EBOVal binary_double;

acc binary_double;

BEGIN

acc := 0.0;

FOR i IN 1..1000000

LOOP

EBOVal := EBO(500,CAST(i AS binary_double) / 1000.0) ;

acc := acc + EBOVal;

END LOOP;

RETURN acc;

END;