COMPUTE column causes query performance behavior change

[Breck_Carter]

This is a followup to SA12 UDF in select list evaluation time based on a simple reproducible sent to me by ODL_Sybase (thank you!)

---

The presence of a COMPUTE constraint containing a SELECT FROM another table seems to cause bad query performance starting after SQL Anywhere version 9: version 10 crashes, versions 11 through 16 run slower possibly because a UDF call is evaluated many times instead of once.

( the "seems to" and "possibly because" are weasel phrases to cover my ignorance of how the query optimizer works... as in "firing a bullet into someone's chest seems to cause death, possibly because of all the damage done" 🙂

Here is the reproducible code, output shown first (code slightly modified to run in all versions)...

Execution time: 0.612 seconds
@@VERSION,with Computed Column,without Computed Column
'9.0.2.3951',1,1

crash dump in V10...
VERSION=10.0.1.3579
FILENAME=C:\\ProgramData\\SQL Anywhere 10\\diagnostics\\SA10_20140228_051543_6952.crash_log

Execution time: 3.215 seconds
@@VERSION,with Computed Column,without Computed Column
'11.0.1.2960',13001,1

Execution time: 2.099 seconds
@@VERSION,with Computed Column,without Computed Column
'12.0.1.3298',13001,1

Execution time: 2.211 seconds
@@VERSION,with Computed Column,without Computed Column
'16.0.0.1691',13001,1

-----

-- important; we use it for setting scrollbars; without it computed column doesn't matter
set option row_counts = 'On';

BEGIN
   drop table "DBA"."testPoints";
   EXCEPTION WHEN OTHERS THEN
END;

-- referenced table
CREATE TABLE "DBA"."testPoints" (
    "point" tinyint NOT NULL,
    "points" numeric(10, 4) NULL,
    PRIMARY KEY ( "point" )
);

insert testPoints values(0, 1);
insert testPoints values(1, 10);
insert testPoints values(2, 100);
insert testPoints values(3, 1000);

BEGIN
   drop table "DBA"."testProducts";
   EXCEPTION WHEN OTHERS THEN
END;

-- main product table
CREATE TABLE "DBA"."testProducts" (
    "id" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
    "Name" CHAR(100) NULL,
    "price" "money" NULL,
    "pointff" "money" NULL COMPUTE( (select points from testPoints where point = (id & 4)) ),
    PRIMARY KEY ( "id" )
);

-- called UDF
BEGIN
   drop FUNCTION "DBA"."testGetPrice"; 
   EXCEPTION WHEN OTHERS THEN
END;

create FUNCTION "DBA"."testGetPrice"( productId integer)
RETURNS money
DETERMINISTIC
BEGIN
    DECLARE res INTEGER;
    set calls = calls + 1;
    select price into res from testProducts where id = productId;
    if productId & 1 = 0 then 
        set res = res + 1
    end if;
    RETURN res;
END
;

-- testing cursor behavior
BEGIN
   drop FUNCTION "DBA"."testCursorForDefinedData"; 
   EXCEPTION WHEN OTHERS THEN
END;

create FUNCTION "DBA"."testCursorForDefinedData"(  )
RETURNS INTEGER
DETERMINISTIC
BEGIN
    DECLARE res INTEGER;
    set res = -1;
    set calls = 0;
    for myC as testC DYNAMIC SCROLL CURSOR FOR 
        select name, testGetPrice(id) from testProducts order by name DO 
        if calls > 1 THEN 
            return calls
        ELSE 
            return 0;
        end if
    end for;
    RETURN res;
END;

-- UDF call counter
BEGIN
    create variable calls integer;
   EXCEPTION WHEN OTHERS THEN
END;

-- result store
BEGIN
    create variable "with Computed Column" integer;
   EXCEPTION WHEN OTHERS THEN
END;

BEGIN
    create variable "without Computed Column" integer;
   EXCEPTION WHEN OTHERS THEN
END;

-- fill product table; maxRec - # records to insert
BEGIN
   drop FUNCTION "DBA"."testCursorForVariousData"; 
   EXCEPTION WHEN OTHERS THEN
END;

create PROCEDURE "DBA"."testCursorForVariousData"( in maxRec integer)
BEGIN
    declare i integer;
    truncate table testProducts;
        set i = 0;
        while i < maxRec loop
            insert testProducts(id, name, price) values(default, 'p' || i, i);
            set i = i + 1
        end loop;
        commit;
        if testCursorForDefinedData() > 0 then
            return calls
        end if;
    return 0;
END;

// let's test bad behavior
call testCursorForVariousData( 13000);

set "with Computed Column" = calls;

-- drop computed column
set calls = 0;
ALTER TABLE "DBA"."testProducts" DROP "pointff";

-- test proper behavior
call testCursorForVariousData( 13000);

set "without Computed Column" = calls;

select @@VERSION, "with Computed Column", "without Computed Column";

[VolkerBarth]

FWIW, your v10 build is real old, with v10.0.4181 (one of the last ones released), the code does not crash the server...

[Breck_Carter]

Please go ahead and edit the question, show what 10.0.4181 returns... thanks!