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.
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"; |
FWIW, your v10 build is real old, with v10.0.4181 (one of the last ones released), the code does not crash the server...
Please go ahead and edit the question, show what 10.0.4181 returns... thanks!