SAP HANA ARRAY与CUSOR的性能比较

      在某些应用场景下，针对表的一些复杂操作无法用标准SQL的函数完成。这时我们可以使用游标获取表中的每一行数据，进而实现需要的操作。但是，游标所带来的性能损耗使得数据库开发人员对其避而远之。SAP HANA ARRAY为操作表的每一行数据提供了另一种方式。本文首先介绍SAP HANA ARRAY的使用方法，然后通过一个示例展示ARRAY和CURSOR的性能差异。

本节用两个简单示例介绍如何在SAP HANA SQL Script中使用数组。

CREATE COLUMN TABLE TSTTAB(

     "ID" INTEGER,

     "VALUE" DECIMAL

);

INSERT INTO SYSTEM.TSTTAB VALUES(1,10);

INSERT INTO SYSTEM.TSTTAB VALUES(2,20);

INSERT INTO SYSTEM.TSTTAB VALUES(3,30);

CREATE PROCEDURE TEST_ARRAY_1()

AS

ARRAY_VALUE DECIMAL ARRAY;

V_INDEX INTEGER;

SUM_VALUE DECIMAL:=0;

BEGIN

     V_TSTTAB = SELECT "VALUE" FROM SYSTEM.TSTTAB;

     ARRAY_VALUE := ARRAY_AGG(:V_TSTTAB.VALUE);

     FOR V_INDEX IN 1 .. CARDINALITY(:ARRAY_VALUE) DO

           SUM_VALUE :=:SUM_VALUE + :ARRAY_VALUE[:V_INDEX];

     END FOR;

     SELECT :SUM_VALUE AS "SUM" FROM DUMMY;

END;

CALL TEST_ARRAY_1;

结果如下：

CREATE PROCEDURE TEST_ARRAY_2()

AS

ARRAY_ID INTEGER ARRAY;

ARRAY_VALUE DECIMAL ARRAY;

V_INDEX INTEGER;

BEGIN

     FOR V_INDEX IN 1 .. 5 DO

           ARRAY_ID[:V_INDEX]:=:V_INDEX;

           ARRAY_VALUE[:V_INDEX]:=:V_INDEX*10;

     END FOR;

     RS = UNNEST(:ARRAY_ID,:ARRAY_VALUE) AS ("ID","VALUE");     SELECT *FROM :RS;

END;

CALL TEST_ARRAY_2;

结果如下：

1. 数组变量的定义：<array_name> <type> ARRAY [:= <array_constructor>]

2.   ARRAY_AGG函数从表变量取数据到数组，语法是: ARRAY_AGG”(“:<table_variable>.<column_name> [<order_by_clause>]”)”

     注意：ARRAY_AGG的参数只支持表变量，不支持物理表。

3. CARDINALITY函数返回数组的长度。注意，CARDINALITY返回的是数组下标的最大值，不一定每个下标对于的元素都有值。

    例如：ARRAY_ID[1]:=1;ARRAY_ID[100]:=2; 那么CARDINALITY返回的是100，而不是2。

4. UNNEST函数将一个或多个数组合并成一个表变量，语法是：

     UNNEST(:<array_variable> [ {, array_variable} ...] )

    [WITH ORDINALITY] [AS <return_table_specification>)]

本小节分别使用ARRAY和CURSOR实现一个简单的功能。表结构如下：

CREATE COLUMN TABLE TSTTAB_FOR_ARRAY(

     "ID" INTEGER,

     "VALUE" DECIMAL

);

要实现的功能就是将TSTTAB_FOR_ARRAY表每条记录的VALUE值都加上它前一条记录(由ID列决定)的VALUE值。

CREATE PROCEDURE DO_WITH_CURSOR()

LANGUAGE SQLSCRIPT

AS

ID_ARRAY INTEGER ARRAY;

VALUE_ARRAY DECIMAL ARRAY;

CURSOR CUR_TSTTAB FOR SELECT * FROM SYSTEM.TSTTAB_FOR_ARRAY ORDERBY ID;

V_INDEX INTEGER := 1;

PRE_VALUE DECIMAL :=0;

BEGIN

     FOR CUR AS CUR_TSTTAB DO

           ID_ARRAY[:V_INDEX]:=CUR.ID;

           VALUE_ARRAY[:V_INDEX]:=CUR.VALUE + RE_VALUE;

           PRE_VALUE :=CUR.VALUE;

           V_INDEX :=:V_INDEX+1;

     END FOR;

     RS = UNNEST(:ID_ARRAY,:VALUE_ARRAY) AS ("ID","VALUE");

     SELECT * FROM :RS;

END;

CALL DO_WITH_CURSOR;

CREATE PROCEDURE DO_WITH_ARRAY()

LANGUAGE SQLSCRIPT

AS

ID_ARRAY INTEGER ARRAY;

VALUE_ARRAY DECIMAL ARRAY;

V_INDEX INTEGER := 1;

PRE_VALUE DECIMAL :=0;

TMP DECIMAL :=0;

BEGIN

     TSTTAB = SELECT * FROM SYSTEM.TSTTAB_FOR_ARRAY ORDER

    BY ID;

     ID_ARRAY := ARRAY_AGG(:TSTTAB.ID);

     VALUE_ARRAY:=ARRAY_AGG(:TSTTAB.VALUE);

     FOR V_INDEX IN 1 .. CARDINALITY(:ID_ARRAY) DO

           TMP:=:VALUE_ARRAY[:V_INDEX];

           VALUE_ARRAY[:V_INDEX]:=:VALUE_ARRAY[:V_INDEX]

+ RE_VALUE;

           PRE_VALUE:=:TMP;

     END FOR;

     RS = UNNEST(:ID_ARRAY,:VALUE_ARRAY) AS ("ID","VALUE");

     SELECT * FROM :RS;

END;

CALL DO_WITH_ARRAY;

     首先，对比两个procedure的运行结果。将上述两个procedure中RS的insert到两张不同的表中，然后用以下SQL语句验证：

SELECT SUM(ABS(A.VALUE-B.VALUE))

FROM SYSTEM.TSTTAB_FOR_ARRAY_RESULT A INNER JOIN SYSTEM. TSTTAB_FOR_ARRAY_RESULT_2 B ON A.ID=B.ID;

结果如下：

可见，结果一致。

然后，二者的性能比较，TSTTAB_FOR_ARRAY表含10,000,000行记录：

两个procedure耗时如下：

可见， ARRAY实现的性能较CURSOR提高了5倍多。

      本文简单介绍了如何使用SAP HANA数组，并比较了ARRAY和CURSOR的性能。